Inserting identity columns with CommandBuilder generated commands?

  • Thread starter Thread starter Carl Mercier
  • Start date Start date
C

Carl Mercier

Hi,

I need to commit the changes to the database of a dataset containing
many (MANY) datatables. A few of them have an auto-increment column,
but I need to insert a specific value in the database (which is MS-SQL,
by the way).

My code is fine and I send a "SET IDENTITY_INSERT ON" to the database
when needed.

Since all the operations I need to perform are very basic and that I
have many different tables, I am using the CommandBuilder object. The
problem is that the Insert command does NOT include the auto-increment
field.

Is there a way to make the extra parameter is added to my InsertCommand?
MSDN and the books I have were not of any help.

Thank you very much!

Carl

PS: Here's the code...


Public Sub CommitTable(ByVal table As DataTable, ByVal transaction
As SqlTransaction, Optional ByVal allowIdentityInsert As Boolean = False)

Dim da As New SqlDataAdapter("SELECT * FROM " +
table.TableName, transaction.Connection)
da.SelectCommand.Transaction = transaction

Dim cmdBuilder As New SqlCommandBuilder(da)
Dim cmd As SqlCommand

cmdBuilder.QuotePrefix = "["
cmdBuilder.QuoteSuffix = "]"

da.InsertCommand = cmdBuilder.GetInsertCommand
da.UpdateCommand = cmdBuilder.GetUpdateCommand
da.DeleteCommand = cmdBuilder.GetDeleteCommand

cmd = New SqlCommand("DELETE FROM " + table.TableName,
transaction.Connection, transaction)
cmd.ExecuteNonQuery()

If allowIdentityInsert Then
cmd.CommandText = "SET IDENTITY_INSERT " + table.TableName
+ " ON"
cmd.ExecuteNonQuery()
End If

da.Update(table)
table.AcceptChanges()

If allowIdentityInsert Then
cmd.CommandText = "SET IDENTITY_INSERT " + table.TableName
+ " OFF"
cmd.ExecuteNonQuery()
End If


End Sub
 
I tried something different now... but I get -another- error... It is:
Line 1: Incorrect syntax near 'sp_ins_tblCorpOrders'.

I started MS-SQL Profiler and what the dataadapter sends to the database is:
exec sp_executesql N'sp_ins_tblCorpOrders', N'@CorpOrderID
uniqueidentifier,@CorpSeasonID uniqueidentifier,@InvoiceNumber
int,@ClientID uniqueidentifier,@InvoiceDate datetime,@MoneyCampDays
decimal(28,4),@MoneyItems decimal(28,0),@MoneyCampDaysRebates
decimal(28,0),@MoneyItemRebates decimal(28,0),@MoneyTax1
decimal(28,4),@MoneyTax2 decimal(28,4),@MoneyGrandTotal
decimal(28,4),@MoneyPayments decimal(28,4),@MoneyBalance
decimal(28,4),@Invoiced bit,@ContractServiceDesc
nvarchar(4000),@ContractAgreementTerms
nvarchar(4000),@ContractPaymentTerms nvarchar(4000),@AddedBy
nvarchar(4000),@AddedOn datetime,@LastModBy nvarchar(4000),@LastModOn
datetime', @CorpOrderID = '00000000-0000-0000-0000-000000001000',
@CorpSeasonID = '00000000-0000-0000-0000-000000000001', @InvoiceNumber =
1000, @ClientID = '00000000-0000-0000-0000-000000000001', @InvoiceDate =
'May 15 2003 12:00:00:000AM', @MoneyCampDays = 1075.0000, @MoneyItems =
0, @MoneyCampDaysRebates = 0, @MoneyItemRebates = 0, @MoneyTax1 =
75.2500, @MoneyTax2 = 86.2700, @MoneyGrandTotal = 1236.5200,
@MoneyPayments = 1288.5200, @MoneyBalance = -52.0000, @Invoiced = 0,
@ContractServiceDesc = N'reeer', @ContractAgreementTerms = N'rerere',
@ContractPaymentTerms = N'ereer', @AddedBy = N'admin', @AddedOn = 'May
15 2003 1:47:25:087PM', @LastModBy = N'admin', @LastModOn = 'Jul 9
2003 3:15:45:547PM'

All the parameters are in my SPROC. Why is the dataadapter sending
non-working commands to the database? Anyone has an idea?

Thanks!

Carl




Here's the code:

Private Sub CommitIdentityTable(ByVal table As DataTable, ByVal
transaction As SqlTransaction)
Dim da As New SqlDataAdapter("SELECT * FROM " +
table.TableName, transaction.Connection)
da.SelectCommand.Transaction = transaction

Dim cb As New SqlCommandBuilder(da) : da.DeleteCommand =
cb.GetDeleteCommand

Dim cmd As New SqlCommand("sp_ins_" + table.TableName,
transaction.Connection, transaction)
For Each dc As DataColumn In table.Columns
Dim param As New SqlParameter
param.ParameterName = "@" + dc.ColumnName
param.SourceColumn = dc.ColumnName
cmd.Parameters.Add(param)
da.InsertCommand = cmd
Next

cmd = New SqlCommand("sp_upd_" + table.TableName,
transaction.Connection, transaction)
For Each dc As DataColumn In table.Columns
Dim param As New SqlParameter
param.ParameterName = "@" + dc.ColumnName
param.SourceColumn = dc.ColumnName
cmd.Parameters.Add(param)
da.InsertCommand = cmd
Next

da.Update(table)

End Sub
 
Hi Carl,

Try creating command at design time using wizard and see what code it
generates.
 
Carl Mercier said:
It will simply ignore my auto-increment field again...
Click to expand...

That's true. Does it work now - no errors?
You mean it don't pass the autoincfield to SP?
Can you paste the code generated for command?
 
Carl,

The CommandBuilder is unaware of the IDENTITY_INSERT setting
because it does not affect the metadata that SQL Server returns
about the auto-increment column. You'd need to supply your own
updating logic to handle this scenario.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
David,

It's nice to hear from the guy who taught me ADO.NET :) I read your
ADO.NET Core Reference book some time ago and I have to say I loved it.
It is the best ADO.NET book I have seen so far!

I have tried to supply my own updating logic but I get another error...
maybe you can help me out on this one.

Here's my code:
Private Sub CommitIdentityTable(ByVal table As DataTable, ByVal
transaction As SqlTransaction)
Dim da As New SqlDataAdapter("SELECT * FROM " +
table.TableName, transaction.Connection)
da.SelectCommand.Transaction = transaction

Dim cb As New SqlCommandBuilder(da) : da.DeleteCommand =
cb.GetDeleteCommand

Dim cmd As New SqlCommand("sp_ins_" + table.TableName,
transaction.Connection, transaction)
For Each dc As DataColumn In table.Columns
Dim param As New SqlParameter
param.ParameterName = "@" + dc.ColumnName
param.SourceColumn = dc.ColumnName
cmd.Parameters.Add(param)
da.InsertCommand = cmd
Next

cmd = New SqlCommand("sp_upd_" + table.TableName,
transaction.Connection, transaction)
For Each dc As DataColumn In table.Columns
Dim param As New SqlParameter
param.ParameterName = "@" + dc.ColumnName
param.SourceColumn = dc.ColumnName
cmd.Parameters.Add(param)
da.InsertCommand = cmd
Next

da.Update(table)

End Sub


and here's the exception:

Line 1: Incorrect syntax near 'sp_ins_tblCorpOrders'.



In MS-SQL Profiler, this is sent to the database:
exec sp_executesql N'sp_ins_tblCorpOrders', N'@CorpOrderID
uniqueidentifier,@CorpSeasonID uniqueidentifier,@InvoiceNumber
int,@ClientID uniqueidentifier,@InvoiceDate datetime,@MoneyCampDays
decimal(28,4),@MoneyItems decimal(28,0),@MoneyCampDaysRebates
decimal(28,0),@MoneyItemRebates decimal(28,0),@MoneyTax1
decimal(28,4),@MoneyTax2 decimal(28,4),@MoneyGrandTotal
decimal(28,4),@MoneyPayments decimal(28,4),@MoneyBalance
decimal(28,4),@Invoiced bit,@ContractServiceDesc
nvarchar(4000),@ContractAgreementTerms
nvarchar(4000),@ContractPaymentTerms nvarchar(4000),@AddedBy
nvarchar(4000),@AddedOn datetime,@LastModBy nvarchar(4000),@LastModOn
datetime', @CorpOrderID = '00000000-0000-0000-0000-000000001000',
@CorpSeasonID = '00000000-0000-0000-0000-000000000001', @InvoiceNumber =
1000, @ClientID = '00000000-0000-0000-0000-000000000001', @InvoiceDate =
'May 15 2003 12:00:00:000AM', @MoneyCampDays = 1075.0000, @MoneyItems =
0, @MoneyCampDaysRebates = 0, @MoneyItemRebates = 0, @MoneyTax1 =
75.2500, @MoneyTax2 = 86.2700, @MoneyGrandTotal = 1236.5200,
@MoneyPayments = 1288.5200, @MoneyBalance = -52.0000, @Invoiced = 0,
@ContractServiceDesc = N'reeer', @ContractAgreementTerms = N'rerere',
@ContractPaymentTerms = N'ereer', @AddedBy = N'admin', @AddedOn = 'May
15 2003 1:47:25:087PM', @LastModBy = N'admin', @LastModOn = 'Jul 9
2003 3:15:45:547PM'


I have no idea why but my command object sends an invalid query to the
database!

Thanks in advance!

Carl
 
Carl,

Thank you for the kind words.

You need to set Command.CommandType to
CommandType.StoredProcedure.

You could move the calls to set the
InsertCommand/UpdateCommand out of the for loops. Also, you're
setting both the inserting command and updating command to the
InsertCommand property.

The logic you've supplied for setting up the parameters
makes sense. However, you may want to double-check on the logic
for adding parameters for the UpdateCommand. I'm assuming you're
doing some concurrency checking in the UPDATE query inside the
stored procedure, which means you may have some columns where you
have to pass both the current and original values.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
David,

I figured all that out this afternoon. After spending (wasting) so much
time with Commandbuilder... I kind of lost focus for the new code :)

Everything is working great right now...

Again, I liked your book very much. I read the whole thing (minus 1
chapter I think) in a weekend last summer. Since then, I am very
confortable with ADO.NET (even if my last code snipped was pretty bad!)

Take it easy!

Carl
 
Carl,

Thanks again for the kind words, and I'm glad to hear that
your updating logic is working as expected.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top