belpepsi
asked on
ExecuteNonQuery only updates one row in a dataset
I have code that fills a dataset and then I want it to iterate through the rows of the dataset so I can fill in a field that was just added to the table. When I iterate through the dataset, all of the values change as they are supposed to but in the end, it only updates the very first row in the dataset. Any help would be appreciated. Thanks
//
//
// Fill Dataset ad
//
//
cmd.CommandText = @"SELECT CBSequenceNumber, CBNumber, CBAttempt, CBTimeZone FROM CallbacksVirtualQueueHistory WHERE CBNumber <> 'NULL' ORDER BY CBSequenceNumber";
ad.SelectCommand = cmd;
con.ConnectionString = str;
cmd.Connection = con;
con.Open();
ad.Fill(dsMonitor);
MonitorList.DataContext = dsMonitor.Tables[0].DefaultView;
MonitorTxt.Text = dsMonitor.Tables[0].Rows.Count.ToString();
//
//
// Set up the Update Command and Parameters
//
//
cmd.CommandText = "UPDATE CallbacksVirtualQueueHistory SET CBTimeZone = @CBTimeZone WHERE (CBSequenceNumber = @CBSequenceNumber) AND (CBAttempt = @CBAttempt)";
con.CreateCommand();
SqlParameter paramTZ = new SqlParameter("@CBTimeZone", System.Data.SqlDbType.VarChar, 2);
SqlParameter paramSN = new SqlParameter("@CBSequenceNumber", System.Data.SqlDbType.VarChar, 8);
SqlParameter paramA = new SqlParameter("@CBAttempt", System.Data.SqlDbType.VarChar, 4);
cmd.Parameters.Add(paramTZ);
cmd.Parameters.Add(paramSN);
cmd.Parameters.Add(paramA);
int iCount = dsMonitor.Tables[0].Rows.Count;
int i;
//
//
// Iterate through dataset and Update records
//
//
for (i = 0; i < iCount; i++)
{
string strTimeZone;
string strNumber = dsMonitor.Tables[0].Rows[i].ItemArray[1].ToString();
strNumber = strNumber.Substring(0, 3);
strTimeZone = getTimeZone(strNumber);
dsMonitor.Tables[0].Rows[i].ItemArray[3] = strTimeZone.ToString();
paramTZ.Value = strTimeZone.ToString();
paramSN.Value = dsMonitor.Tables[0].Rows[i].ItemArray[0];
paramA.Value = dsMonitor.Tables[0].Rows[i].ItemArray[2];
ad.UpdateCommand = cmd;
ad.UpdateCommand.ExecuteNonQuery();
}
con.Close();
}
Did you try moving lines 50 and 51 outside of your loop (before line 53)?
Have you tested value of iCount?
Check it first before loop
MessageBox.Show(iCount.ToS tring());
Check it first before loop
MessageBox.Show(iCount.ToS
You have to change code:
paramTZ.Value = strTimeZone.ToString();
paramSN.Value = dsMonitor.Tables[0].Rows[i ].ItemArra y[0];
paramA.Value = dsMonitor.Tables[0].Rows[i ].ItemArra y[2];
ad.UpdateCommand = cmd;
ad.UpdateCommand.ExecuteNo nQuery();
to this:
cmd.Parameters["@CBTimeZon e"].Value = strTimeZone.ToString();
cmd.Parameters["@CBSequenc eNumber"]. Value = dsMonitor.Tables[0].Rows[i ].ItemArra y[0];
cmd.Parameters["@CBAttempt "].Value = dsMonitor.Tables[0].Rows[i ].ItemArra y[2];
cmd.ExecuteNonQuery();
paramTZ.Value = strTimeZone.ToString();
paramSN.Value = dsMonitor.Tables[0].Rows[i
paramA.Value = dsMonitor.Tables[0].Rows[i
ad.UpdateCommand = cmd;
ad.UpdateCommand.ExecuteNo
to this:
cmd.Parameters["@CBTimeZon
cmd.Parameters["@CBSequenc
cmd.Parameters["@CBAttempt
cmd.ExecuteNonQuery();
ASKER
kaufmed - When I do that, no records are updated.
nkhelashvili - yes, iCount will show the 30000 records that are in the database
nkhelashvili - yes, iCount will show the 30000 records that are in the database
ASKER
nkhelashvili: - same result, only the first row is affected
try to debug your program at the lines I told to change it. Check if the values are different...
ASKER
nkhelashvili: - the values change during each iteration of the for loop. From that I see that I am iterating row by row through the dataset.
CBSequenceNumber and CBAttempt are the primary key into this table. (forgot to add that earlier)
CBSequenceNumber and CBAttempt are the primary key into this table. (forgot to add that earlier)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. When I used the profiler, I noticed that my declarations for my parameters were of the wrong type. Should have been like this:
SqlParameter paramTZ = new SqlParameter("@CBTimeZone" , System.Data.SqlDbType.VarC har, 2);
SqlParameter paramSN = new SqlParameter("@CBSequenceN umber", System.Data.SqlDbType.BigI nt, 8);
SqlParameter paramA = new SqlParameter("@CBAttempt", System.Data.SqlDbType.Int, 4);
SqlParameter paramTZ = new SqlParameter("@CBTimeZone"
SqlParameter paramSN = new SqlParameter("@CBSequenceN
SqlParameter paramA = new SqlParameter("@CBAttempt",