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

Open in new window

kaufmed

Did you try moving lines 50 and 51 outside of your loop (before line 53)?

nkhelashvili

Have you tested value of iCount?

Check it first before loop

MessageBox.Show(iCount.ToString());

nkhelashvili

You have to change code:

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

to this:

cmd.Parameters["@CBTimeZone"].Value = strTimeZone.ToString();
cmd.Parameters["@CBSequenceNumber"].Value = dsMonitor.Tables[0].Rows[i].ItemArray[0];
cmd.Parameters["@CBAttempt"].Value = dsMonitor.Tables[0].Rows[i].ItemArray[2];

cmd.ExecuteNonQuery();

belpepsi

ASKER

kaufmed - When I do that, no records are updated.

nkhelashvili - yes, iCount will show the 30000 records that are in the database

belpepsi

ASKER

nkhelashvili: - same result, only the first row is affected

nkhelashvili

try to debug your program at the lines I told to change it. Check if the values are different...

belpepsi

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)

ASKER CERTIFIED SOLUTION

nkhelashvili

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

belpepsi

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.VarChar, 2);
SqlParameter paramSN = new SqlParameter("@CBSequenceNumber", System.Data.SqlDbType.BigInt, 8);
SqlParameter paramA = new SqlParameter("@CBAttempt", System.Data.SqlDbType.Int, 4);