How to use insert and update command using c#

Question

I have GridView and I am trying to check Task_ID from a table, if it is found then I want to update the record but if Task_ID is not found in the table then I want to insert it into my table. My code now does the insert part but it does not do the update part of the code. I was wondering how you can do this within the same code. Please help. thanks here is my code:

int index = 0;
foreach (GridViewRow row in myGV.Rows)
{
    if (row.RowType == DataControlRowType.DataRow)
    {

        Label ID = row.FindControl("lbl_ID") as Label;

        string UID = Request.Form[row.FindControl("hfUId").UniqueID];

        DateTime strDate = DateTime.Now;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myCon"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into myTable(TID, USR_ID, UPDT_DT) values(@ID, @USR_ID, @UPDT_DT) ";

        cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
        cmd.Parameters.Add("@USR_ID", SqlDbType.VarChar).Value = UID.ToString();
        cmd.Parameters.Add("@UPDT_DT", SqlDbType.VarChar).Value = strDate.Date;
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        con.Dispose();
        cmd.Dispose();
    }

    index++;
}

Milad.H · Accepted Answer · 2014-02-13 20:40:11Z

3

There is a MERGE command in SQL for this purpose. Here is an example that might work for you:

Merge myTable As T
    Using (Select Task_ID From myTable) As Src (Src_ID)
    ON (T.Task_ID = Src.Src_ID)
    WHEN MATCHED THEN
        UPDATE SET T.UPDT_DT = 'your new value'
    WHEN NOT MATCHED THEN   
        INSERT (TASK_ID, USR_ID, UPDT_DT)
        VALUES (@TASK_ID, @USR_ID, @UPDT_DT);

change 'your new value' to the correct update statement

answered Feb 13, 2014 at 20:40

Milad.H

1164 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HOKBONG · Accepted Answer · 2014-02-13 20:40:29Z

1

Replace the command text as below:

if not exists (select task_id from mytable where task_id = @task_id)  
insert into myTable(TASK_ID, USR_ID, UPDT_DT) values(@TASK_ID, @USR_ID, @UPDT_DT)
else
update mytable set USR_ID = @USR_ID, UPDT_DT = @UPDT_DT where task_id = @TASK_ID

answered Feb 13, 2014 at 20:40

HOKBONG

7957 silver badges17 bronze badges

Comments

Jon · Accepted Answer · 2014-02-13 20:39:26Z

0

I would first query the database to see if the task_id existed... declare cmd, define it with a sql command like "Select count(*) from myTable where Task_ID = '" & Task_ID.text & "'" and assign that to some variable x. Then follow that with "if x <> 0 then --> define your cmd as an insert statement here, ELSE --> define cmd as "Update myTable set blah blah = values WHERE task_ID = " & task_ID.text.

answered Feb 13, 2014 at 20:39

Jon

3011 gold badge5 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to use insert and update command using c#

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related