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-01 07:23:38Z

6

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(...))
    {

    }
}

edited Jan 1, 2014 at 7:23

answered Dec 31, 2013 at 18:33

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

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

4 Comments

moe Over a year ago

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(); .

Mike Perrenoud Over a year ago

@moe, are you getting a specific error with the SqlCommandBuilder?

moe Over a year ago

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();

Mike Perrenoud Over a year ago

@moe, move the builder lines below the Fill and tell me what happens.

Steve · Accepted Answer · 2013-12-31 19:17:31Z

2

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

edited Dec 31, 2013 at 19:17

answered Dec 31, 2013 at 18:33

Steve

217k22 gold badges242 silver badges296 bronze badges

Comments

Irfan TahirKheli · Accepted Answer · 2013-12-31 18:37:55Z

0

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

answered Dec 31, 2013 at 18:37

Irfan TahirKheli

3,6621 gold badge26 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to Use an Update Statement in SQLDataAdapter

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related