Inserting identity columns with CommandBuilder generated commands?

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
 
C

Carl Mercier

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
 
M

Miha Markic

Hi Carl,

Try creating command at design time using wizard and see what code it
generates.
 
M

Miha Markic

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?
 
D

David Sceppa

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.
 
C

Carl Mercier

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
 
D

David Sceppa

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.
 
C

Carl Mercier

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
 
D

David Sceppa

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Commands and CommandBuilder 4
Help inserting specific value into an identity column 7
Urgent ! Can't update the database by using DataAdapter 1
Concurrency violation with Access database 4
Using @@Identity with access 6
No rows returned with OleDbParameter.Value = DBNull.Value 0
Open transaction after TransactionScope.SetComplete 3
Saving data from mult tables 1

Top