Hi all.

I know (I hope) the process how CommandBuilder creates the action commands
derived form the select and how register itself as a listern for catching
the rowupdating event of the DataAdapter. But reading msdn docs, I also know
that if I explicitly assign a command object to the InsertCommand property
of the DataAdapter, the DataAdapter itself would have to use this command
without replace it with the autogenerated one.
If I right, why this piece of code don't run properly?

[...]
Dim daCust As New SqlDataAdapter("SELECT * FROM Customers", cn)
Dim cbCust As New SqlCommandBuilder(daCust)
Dim cmd As SqlCommand = cbCust.GetInsertCommand
cmd.CommandText &= ";SELECT @@IDENTITY AS ID"
daCust.InsertCommand = cmd
daCust.Update(dtCust)

If I run this code, the command used for inserting rows is always the
originale created by CommandBuilder and not the modified. But if I create a
new SqlCommand instead of use the one returned by CommandBuilder, this new
command is rightly used for inserting. Why?

Thanks.

How are you wiring this up? Just calling Update() on the Adapter? Not that it
really matters, but the above may clue you in on why it is not working, if
you really want to tackle that problem.

Here are my suggestions:

1. Get rid of the CommandBuilder. It was designed for tool designers and not
for Enterprise applications. While it works in many instances, it does not
take much to wire the app properly and avoid the CommandBuilder.

2. Write a stored procedure that has your command and returns SCOPE_IDENTITY
(instead of attempting @@IDENTITY. @@IDENTITY can return incorrect values on
heavily used systems. If you must, use a tool like LLBLGen to create your
sprocs and alter them to add the IDENTITY return.

3. Create stored procedures for the rest of your CRUD. Sure, it takes a
little extra time, but it adds security to you app by forcing the
parameterization of queries (harder (or impossible) to SQL inject).

4. Wire the sprocs manually. This way you can be sure of what the app is
doing (or not doing) and better control your data access. Once again, code
gens can help tremendously.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


"Pashkuale" wrote:

> Hi all.
>
> I know (I hope) the process how CommandBuilder creates the action commands
> derived form the select and how register itself as a listern for catching
> the rowupdating event of the DataAdapter. But reading msdn docs, I also know
> that if I explicitly assign a command object to the InsertCommand property
> of the DataAdapter, the DataAdapter itself would have to use this command
> without replace it with the autogenerated one.
> If I right, why this piece of code don't run properly?
>
> [...]
> Dim daCust As New SqlDataAdapter("SELECT * FROM Customers", cn)
> Dim cbCust As New SqlCommandBuilder(daCust)
> Dim cmd As SqlCommand = cbCust.GetInsertCommand
> cmd.CommandText &= ";SELECT @@IDENTITY AS ID"
> daCust.InsertCommand = cmd
> daCust.Update(dtCust)
>
> If I run this code, the command used for inserting rows is always the
> originale created by CommandBuilder and not the modified. But if I create a
> new SqlCommand instead of use the one returned by CommandBuilder, this new
> command is rightly used for inserting. Why?
>
> Thanks.
>
>
>

"Cowboy (Gregory A. Beamer) - MVP" <(E-Mail Removed)>
wrote:
>How are you wiring this up? Just calling Update() on the Adapter? Not that
>it really matters, but the above may clue you in on why it is not working,
>if you really want to tackle that problem. Here are my suggestions: 1. Get
>rid of the CommandBuilder.
[...]
>2. Write a stored procedure that has your command and returns
>SCOPE_IDENTITY
[...]
>3. Create stored procedures for the rest of your CRUD.
[...]
>4. Wire the sprocs manually.

Hi Cowboy, I'm "manually" replay to you through my message becouse I cant't
find your reply in my news server.

Thanks for your tips but I know good point and lacks of the CommandBuilder
approach when updating data. My question was only for accademic purpose,
even if I consider CommandBuilder a good and fast solution for certain
scenarios like single user solutions or small competitive systems.

I'd like to know the reason of that behavior only for curiosity, let's say
for knowing what happens behind the scene.

Thanks.

The SqlCommandBuilder tracks the three commands it generates for the
insert/update/delete commands.
When you set the adapter.InsertCommand = bldr.GetInsertCommand, the builder
still sees it as its command.
You can work around this by 'adapter.InsertCommand =
bldr.GetInsertCommand().Clone()'

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Pashkuale" <(E-Mail Removed)> wrote in message
news:434a41b3$0$13346$(E-Mail Removed)...
> Hi all.
>
> I know (I hope) the process how CommandBuilder creates the action commands
> derived form the select and how register itself as a listern for catching
> the rowupdating event of the DataAdapter. But reading msdn docs, I also
> know that if I explicitly assign a command object to the InsertCommand
> property of the DataAdapter, the DataAdapter itself would have to use this
> command without replace it with the autogenerated one.
> If I right, why this piece of code don't run properly?
>
> [...]
> Dim daCust As New SqlDataAdapter("SELECT * FROM Customers", cn)
> Dim cbCust As New SqlCommandBuilder(daCust)
> Dim cmd As SqlCommand = cbCust.GetInsertCommand
> cmd.CommandText &= ";SELECT @@IDENTITY AS ID"
> daCust.InsertCommand = cmd
> daCust.Update(dtCust)
>
> If I run this code, the command used for inserting rows is always the
> originale created by CommandBuilder and not the modified. But if I create
> a new SqlCommand instead of use the one returned by CommandBuilder, this
> new command is rightly used for inserting. Why?
>
> Thanks.
>

"Mark Ashton" <(E-Mail Removed)> ha scritto nel messaggio
news:ul1c$(E-Mail Removed)...

> When you set the adapter.InsertCommand = bldr.GetInsertCommand, the
> builder still sees it as its command.
> You can work around this by 'adapter.InsertCommand =
> bldr.GetInsertCommand().Clone()'

Unfortunately, GetInsertCommand().Clone() works only with version 2.0 of the
framework. I still use the 1.1 version.
In this case, I will implement this method manually, cloning the command and
all parameters too.

Thanks for reply.

P.S. I ask myself, why check only for reference and don't check if
commandtext (and the other parameters) is the same before decide if it's the
autogenerated command?