How to Use an Update Statement in SQLDataAdapter

Question

I am trying to run an update statement after i built my sqldataadapter. I have column called INIT_PHASE in my table and if the INIT_PHASE is null or there is no data then i would like to set it to 1. I have tried but i can't seem to get it right the update statement. Pls. help. here is my code:

 string ID = ddlPractice.SelectedValue;
    string TYPE = DDL_TYPE.SelectedValue;
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
    SqlDataAdapter da = new SqlDataAdapter(@"select SET_SK, UNIT_NM, TYPE, INIT_PHASE FROM myTable WHERE UNIT_NM =@ID AND TYPE = @TYPE", con);
    DataTable dtSETS = new DataTable();
    da.SelectCommand.Parameters.AddWithValue("@ID", (ID));
    da.SelectCommand.Parameters.AddWithValue("@TYPE", (TYPE));
    da.Fill(dtSETS);

    if (dtSETS.Rows.Count > 0)
    {
        DataRow dtSETS_row = dtSETS.Rows[0];

        long SET_SK = dtSETS_row.Field<long>("SET_SK");

        if (dtSETS_row.Field<string>("INIT_PHASE") == null)
        { 

           //run update command here

             update myTable set INIT_PHASE = 1;

        }
    }

Mike Perrenoud · Accepted Answer · 2014-01-01T07:23:38.503

One approach here would be to use the SqlCommandBuilder to build the UPDATE statement:

string ID = ddlPractice.SelectedValue;
string TYPE = DDL_TYPE.SelectedValue;
SqlConnection con = new SqlConnection(
    ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter(
    @"select SET_SK, UNIT_NM, TYPE, INIT_PHASE FROM myTable WHERE UNIT_NM =@ID AND TYPE = @TYPE",
    con);

DataTable dtSETS = new DataTable();
da.SelectCommand.Parameters.AddWithValue("@ID", (ID));
da.SelectCommand.Parameters.AddWithValue("@TYPE", (TYPE));
da.Fill(dtSETS);

SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

if (dtSETS.Rows.Count > 0)
{
    DataRow dtSETS_row = dtSETS.Rows[0];

    long SET_SK = dtSETS_row.Field<long>("SET_SK");

    if (dtSETS_row.Field<string>("INIT_PHASE") == null)
    {
        dtSETS_row["INIT_PHASE"] = 1;
    }
}

da.Update(dtSETS);

Take note to the following lines of code. Here we are building the update command:

SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

here we are literally modifying the DataRow so that it's RowState is changed to Modified:

dtSETS_row["INIT_PHASE"] = 1;

and then finally, here we are sending updates to the database with the Update method on the SqlDataAdapter:

da.Update(dtSETS);

What this is going to do is only send updates for the rows with a RowState of Modified.

NOTE: each of the ADO.NET objects should be wrapped in a using. Refactor your code to match this type of template:

using (SqlConnection con = new SqlConnection(...))
{
    using (SqlDataAdapter da = new SqlDataAdapter(...))
    {

    }
}

thanks, i am getting this error: Must declare the scalar variable "@ID" and it seems the code is failing at this line: da.UpdateCommand = builder.GetUpdateCommand(); . — moe, Dec 31 '13 at 18:47
@moe, are you getting a specific error with the `SqlCommandBuilder`? — Mike Perrenoud, Dec 31 '13 at 20:00
yes, i am getting this error: Must declare the scalar variable "@ID" and it seems the code is failing at this line: da.UpdateCommand = builder.GetUpdateCommand(); — moe, Dec 31 '13 at 20:14
@moe, move the `builder` lines below the `Fill` and tell me what happens. — Mike Perrenoud, Dec 31 '13 at 20:28

Steve · Answer 2 · 2013-12-31T19:17:31.597

If I understand correctly, you could execute directly a command to update just this field

if (dtSETS_row.Field<string>("INIT_PHASE") == null)
{
    SqlCommand cmd = new SqlCommand(@"UPDATE myTable set INIT_PHASE = 1 " + 
                         "WHERE UNIT_NM =@ID AND TYPE = @TYPE", con);
    cmd.Parameters.AddWithValue("@ID", (ID));
    cmd.Parameters.AddWithValue("@TYPE", (TYPE));
    cmd.ExecuteNonQuery();
}

You need to open the connection though both for the SqlDataAdapter and for the following command

score 0 · Answer 3 · answered Dec 31 '13 at 18:37

0

You will have to use SqlCommandBuilder class for updating data in disconnected mode. ie) DataSet and Data Adapters.

answered Dec 31 '13 at 18:37

Irfan TahirKheli

3,652
1
22
36

How to Use an Update Statement in SQLDataAdapter

3 Answers3

Linked