Update value in SQL table using C#

Question

I need to change my field QB_STATUS from value R to value C. I am doing this in a loop because i cannot "requery" the table as data may have changed.

I have built a list of entries to update. The code does not error and iterates through 5 times (correct based on my idInvoices list) but the field does not get updated.

for (int i = 0; i < idInvoices.Count; i++)
{
    // following command will update one row as ID_Invoice is primary key.  
    // ID_Invoice taken from list previously built in ReadDataToNAVArray
    SqlCommand cmd = new SqlCommand("UPDATE tblINVOICES SET QB_STATUS=@Status WHERE ID_INVOICE = @IDInvoice", myConnection);
    cmd.Parameters.Add("@Status", "C");
    cmd.Parameters.Add("@IDInvoice", idInvoices[i]);

    cmd.Dispose();
}

instead of connection to database for every update,you can try updating all at once.Try reading passing datatable to stored procedure — TheGameiswar
– TheGameiswar, Commented Jun 10, 2016 at 11:54

Dmitrii Bychenko · Accepted Answer · 2016-06-10 12:08:27Z

3

First, you have to execute your query: ExecuteNonQuery; second - do not create command, parameters etc within the loop, just assign values and execute:

 // Make SQL readable
 String sql =
   @"UPDATE tblINVOICES 
        SET QB_STATUS = @Status 
      WHERE ID_INVOICE = @IDInvoice";

 // wrap IDisposable into "using"
 // do not recreate command in the loop - create it once
 using (SqlCommand cmd = new SqlCommand(sql, myConnection)) {
   cmd.Parameters.Add("@Status", SqlDbType.VarChar); //TODO: check types, please
   cmd.Parameters.Add("@IDInvoice", SqlDbType.Decimal); //TODO: check types, please

   // Assign parameters with their values and execute
   for (int i = 0; i < idInvoices.Count; i++) {
     cmd.Parameters["@Status"].Value = "C";
     cmd.Parameters["@IDInvoice"].Value = idInvoices[i];

     cmd.ExecuteNonQuery();
   }    
 }

edited Jun 10, 2016 at 12:08

answered Jun 10, 2016 at 11:53

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

Comments

Balaji · Accepted Answer · 2016-06-10 11:46:20Z

1

You are missing the ExecuteNonQuery in your command.

for (int i = 0; i < idInvoices.Count; i++)
{
    SqlCommand cmd = new SqlCommand("UPDATE tblINVOICES SET QB_STATUS=@Status WHERE ID_INVOICE = @IDInvoice", myConnection);
    cmd.Parameters.Add("@Status", "C");
    cmd.Parameters.Add("@IDInvoice", idInvoices[i]);
    cmd.ExecuteNonQuery();
    cmd.Dispose();
}

answered Jun 10, 2016 at 11:46

Balaji

1,5151 gold badge20 silver badges32 bronze badges

Comments

rcarthy · Accepted Answer · 2016-06-10 11:54:52Z

0

I think you're missing cmd.ExecuteNonQuery();.

An example for a different way of using sql commands:

SqlConnection addConn = new SqlConnection();
            addConn.ConnectionString = Properties.Settings.Default.yourDataBaseConnection;
            addConn.Open();

            SqlCommand addComm = new SqlCommand();
            addComm.Connection = addConn;
            addComm.CommandText = "sql command";
            addComm.ExecuteNonQuery();

answered Jun 10, 2016 at 11:54

rcarthy

12 bronze badges

Collectives™ on Stack Overflow

Update value in SQL table using C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related