DataAdapter changes insert-command at Update

[Patrick]

Hello

I try to do the following thing:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM test",new
SqlConnection(this.ConnectionString));


adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);

SqlCommand insertCommand= cmdBuilder.GetInsertCommand(true);

cmdBuilder.DataAdapter.InsertCommand = insertCommand;

insertCommand.CommandText =
insertCommand.CommandText.Replace("@mydbinteger", "33");

so far all works. I have a dataadpter with the new update

now I call
adapter.Update(new DataRow[] { dataRow });

so what happens is that the InsertCommand is changed to the original one. I
see everything is ok untill this line comes, then I tried to attach to the
RowUpdating event and saw, on that moment all has been changed to the
original insertcommand

has someone an idea what I'm doing wrong



Thanks

Patrick

 

[W.G. Ryan [MVP]]

Hello

I try to do the following thing:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM test",new
SqlConnection(this.ConnectionString));


adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);

SqlCommand insertCommand= cmdBuilder.GetInsertCommand(true);

cmdBuilder.DataAdapter.InsertCommand = insertCommand;

insertCommand.CommandText =
insertCommand.CommandText.Replace("@mydbinteger", "33");

so far all works. I have a dataadpter with the new update

now I call
adapter.Update(new DataRow[] { dataRow });

so what happens is that the InsertCommand is changed to the original one.
I see everything is ok untill this line comes, then I tried to attach to
the RowUpdating event and saw, on that moment all has been changed to the
original insertcommand

has someone an idea what I'm doing wrong

Everything ;-)

Just kidding man. You've pegged the commandbuilder to the adpater so this
approach isn't going to work. I'd just can the adapter personally (check
out www.betav.com and look for Bill's article on weaning developers from the
CommandBuilder article)

 

[Patrick]

Hi Ryan

Just kidding man. You've pegged the commandbuilder to the adpater so this
approach isn't going to work. I'd just can the adapter personally (check
out www.betav.com and look for Bill's article on weaning developers from
the CommandBuilder article)

sorry, I can't find a real answer on my question. The only thing he sais is
to avoid the CommandBuilder as it is used by the wizards, but my question
is, why does the dataadapter change the insert-query by it'self.

Thanks

 

[W.G. Ryan [MVP]]

Look at the declaration and instantiation of the CommandBuilder and Data
Adapter. They are being coupled there. At that point, the adapter uses the
CB whenever Update is called irrespective of what the commandtext changes to