5

I'm trying out the batch inserting/updating of SqlDataAdapter. When I set UpdateBatchSize = 1, it works, but setting it to 2 gives the exception "Specified parameter name 'Id' is not valid.".

using (var sqlDataAdapter = new SqlDataAdapter
{
    UpdateBatchSize = 2
})
using (var connection = new SqlConnection("Data Source=server;Initial Catalog=DB;Integrated Security=True"))
using (var command = new SqlCommand("INSERT INTO Test (Id) VALUES (@Id)", connection)
{
    UpdatedRowSource = UpdateRowSource.None
})
{
    command.Parameters.Add("Id", SqlDbType.Int).SourceColumn = "Id";
    sqlDataAdapter.InsertCommand = command;

    var table = new DataTable("Test");
    table.Columns.Add("Id");
    table.Rows.Add(1);
    table.Rows.Add(2);
    sqlDataAdapter.Update(table);
}
Kelly Selden
  • 1,238
  • 11
  • 21

2 Answers2

3

After decompiling the SqlDataAdapter and using the stack trace, I came to this line.

if (!SqlCommandSet.SqlIdentifierParser.IsMatch(sqlParameter.ParameterName))
    throw ADP.BadParameterName(sqlParameter.ParameterName);

So it turns out, normally you can omit the @ from a SqlParameter name, but when doing this batch, it requires it. Very strange. The correct line becomes

command.Parameters.Add("@Id", SqlDbType.Int).SourceColumn = "Id";
Kelly Selden
  • 1,238
  • 11
  • 21
0
 {
   sqlDataAdapter.Update(table);

 }

this parameter is not valid to sqlDataAdapter , you need to enter the DataSet name followed by the table name , I think it should be like that : 

   {

   sqlDataAdapter.Update(youDataSet,"YourTable");
    }