Actually, ado.net 2.0 help needed - batch update failing

A

andy6

I am using the following code and SQL Profiler shows no activity when I step
through the .update line. Do you see a syntax error?
Thanks!

using (SqlConnection cn = new SqlConnection(cnStr))
{
SqlCommand updCmd = new SqlCommand
("sp_web_edit_manager_update", cn);

SqlDataAdapter adpt = new SqlDataAdapter(updCmd);

updCmd.UpdatedRowSource = UpdateRowSource.None;
updCmd.Parameters.Add("@entity", SqlDbType.VarChar,10,
dtOnlyChanges.Columns[0].ColumnName);
updCmd.Parameters.Add("@edit_id", SqlDbType.Int, 4,
dtOnlyChanges.Columns[1].ColumnName);
updCmd.Parameters.Add("@edit_override", SqlDbType.VarChar, 1,
dtOnlyChanges.Columns[2].ToString());
updCmd.Parameters.Add("@biller_cd", SqlDbType.VarChar, 20,
dtOnlyChanges.Columns[3].ToString());
updCmd.Parameters.Add("@include_in_stats", SqlDbType.VarChar,
1, dtOnlyChanges.Columns[4].ToString());

adpt.UpdateCommand = updCmd;

// Set the Batch Size. 0 means all; default is 1.
adpt.UpdateBatchSize = 10;

// Send the new rows and the changed rows to the database
adpt.Update(dtOnlyChanges);

}
 
B

bruce barker \(sqlwork.com\)

probably no changed (rowstate == modified) rows in the dataset to update.

-- bruce (sqlwork.com)
 
A

andy6 via DotNetMonster.com

Good catch! Thank you.

I ended up with this code.

using (SqlConnection cn = new SqlConnection(DBConnectionString))
{
using (SqlCommand updCmd = new SqlCommand
("sp_web_edit_manager_update", cn))
{
SqlDataAdapter adpt = new SqlDataAdapter(updCmd);
updCmd.CommandType = CommandType.StoredProcedure;
updCmd.CommandTimeout = Convert.ToInt32
(ConfigurationSettings.AppSettings["dbCommandTimeout"]);

updCmd.UpdatedRowSource = UpdateRowSource.None;
updCmd.Parameters.Add("@entity", SqlDbType.VarChar, 10,
dtOnlyChanges.Columns[0].ColumnName);
updCmd.Parameters.Add("@edit_id", SqlDbType.Int, 4,
dtOnlyChanges.Columns[1].ColumnName);
updCmd.Parameters.Add("@edit_override", SqlDbType.VarChar,
1, dtOnlyChanges.Columns[2].ToString());
updCmd.Parameters.Add("@biller_cd", SqlDbType.VarChar, 20,
dtOnlyChanges.Columns[3].ToString());
updCmd.Parameters.Add("@include_in_stats", SqlDbType.
VarChar, 1, dtOnlyChanges.Columns[4].ToString());

adpt.UpdateCommand = updCmd;

// Set the Batch Size. 0 means all; default is 1.
adpt.UpdateBatchSize = 0;

foreach(DataRow b in dtOnlyChanges.Rows)
{
b.SetModified();
}

cn.Open();

// send the changed rows("modified") to the database
adpt.Update(dtOnlyChanges);
}
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top