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

Discussion in 'ASP .Net' started by andy6, Jun 15, 2006.

  1. andy6

    andy6 Guest

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

    }

    --
    Message posted via http://www.dotnetmonster.com
    andy6, Jun 15, 2006
    #1
    1. Advertising

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

    -- bruce (sqlwork.com)

    "andy6" <u9025@uwe> wrote in message news:61cf9e8450e76@uwe...
    >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);
    >
    > }
    >
    > --
    > Message posted via http://www.dotnetmonster.com
    bruce barker \(sqlwork.com\), Jun 15, 2006
    #2
    1. Advertising

  3. 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);
    }
    }

    --
    Message posted via http://www.dotnetmonster.com
    andy6 via DotNetMonster.com, Jun 15, 2006
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    845
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    438
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,292
  4. Navin
    Replies:
    1
    Views:
    660
    Ken Schaefer
    Sep 9, 2003
  5. bcap

    Batch Update Statement Help

    bcap, Aug 28, 2009, in forum: ASP General
    Replies:
    6
    Views:
    757
    Bob Barrows
    Aug 28, 2009
Loading...

Share This Page