DataAdapter changes insert-command at Update

11 Dec 2006 4:14 PM

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

12 Dec 2006 12:57 AM

W.G. Ryan [MVP]

Show quote

"Patrick" <patrick3***@gmail.com> wrote in message
news:uUZZC9THHHA.4636@TK2MSFTNGP06.phx.gbl...
> 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)
Show quote

>
>
>
> Thanks
>
> Patrick
>
>

12 Dec 2006 6:59 AM

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

12 Dec 2006 6:42 PM

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
Show quote

"Patrick" <patrick3***@gmail.com> wrote in message
news:%23cjkesbHHHA.1008@TK2MSFTNGP06.phx.gbl...
> 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
>