Help inserting specific value into an identity column

Sean Finkel · Aug 12, 2008

Hello all,
I am currently tasked with developing a home-grown replication system for our product.
I have it completed and am working out the various bugs. One of the issues we are having deals with inserting data into tables that have identity columns.
Currently, I am reseeding the autonumber on each insert. As you can imagine, this results in a ton of context switches over to the database.

I have tried to modify the code to take the insert command that SqlCommandBuilder generates and modify it to include the identity column in both the column list and the values list.
I then also add a new parameter and set the name/source column.
I also set identity_insert to on for the table. No matter what I do, I always get the error from SQL Server that I must explicitly provide the value for the identity column.

Am I missing a line of code to associate the new parameter with the insert command?
The parameter is defined, and the insert command's commandText is correct.

Any help would be much appreciated!

Here is a sample app I was using, the test table has two columns -
insertID - int - identity - primary key - not null
insertText - varchar(50)

-------------------( snip )-----------------
Dim lconTest As New SqlConnection
Try
lconTest.ConnectionString = "<removed>"
lconTest.Open()

' now attempt to insert a new record
Dim lcmdInsert As New SqlCommand
lcmdInsert.CommandText = "set identity_insert insertTest on"
lcmdInsert.Connection = lconTest
lcmdInsert.ExecuteScalar()

Dim ldtTest As New DataTable("insertTest")
Dim ldaTest As New SqlDataAdapter("select insertID, insertText from insertTest", lconTest)
ldaTest.Fill(ldtTest)

Dim ldrNewRow As DataRow = ldtTest.NewRow
ldrNewRow("insertID") = 1
ldrNewRow("insertText") = "test"
ldtTest.Rows.Add(ldrNewRow)

Dim lcbdTest As New SqlCommandBuilder(ldaTest)
ldaTest.InsertCommand = lcbdTest.GetInsertCommand(True)

' manually modify the insert command to include the identity column
Dim lparmIdentity As New SqlParameter
lparmIdentity.SourceColumn = "insertID"
lparmIdentity.ParameterName = "@insertID"
lparmIdentity.SqlDbType = SqlDbType.Int
ldaTest.InsertCommand.CommandText = ldaTest.InsertCommand.CommandText.Replace("[insertTest] (", "[insertTest] ([insertID], ").Replace("VALUES (", "VALUES (@insertID, ")
ldaTest.InsertCommand.Parameters.Add(lparmIdentity)

ldaTest.Update(ldtTest)

lcmdInsert.CommandText = "set identity_insert insertTest off"
lcmdInsert.ExecuteScalar()

TextBox1.Text = "Success"

Catch ex As Exception
TextBox1.Text = "=========================" & vbNewLine & ex.Message & vbNewLine & "=========================" & vbNewLine & ex.StackTrace
End Try
-------------------( /snip )------------------

nil · Aug 12, 2008

Hello all,
I am currently tasked with developing a home-grown replication system forour product.
I have it completed and am working out the various bugs. One of the issues we are having deals with inserting data into tables that have identity columns.
Currently, I am reseeding the autonumber on each insert. As you can imagine, this results in a ton of context switches over to the database.

I have tried to modify the code to take the insert command that SqlCommandBuilder generates and modify it to include the identity column in both thecolumn list and the values list.
I then also add a new parameter and set the name/source column.
I also set identity_insert to on for the table. No matter what I do, I always get the error from SQL Server that I must explicitly provide the valuefor the identity column.

Am I missing a line of code to associate the new parameter with the insert command?
The parameter is defined, and the insert command's commandText is correct..

Any help would be much appreciated!

Here is a sample app I was using, the test table has two columns -
insertID - int - identity - primary key - not null
insertText - varchar(50)

-------------------( snip )-----------------
Dim lconTest As New SqlConnection
Try
lconTest.ConnectionString = "<removed>"
lconTest.Open()

' now attempt to insert a new record
Dim lcmdInsert As New SqlCommand
lcmdInsert.CommandText = "set identity_insert insertTest on"
lcmdInsert.Connection = lconTest
lcmdInsert.ExecuteScalar()

Dim ldtTest As New DataTable("insertTest")
Dim ldaTest As New SqlDataAdapter("select insertID, insertText from insertTest", lconTest)
ldaTest.Fill(ldtTest)

Dim ldrNewRow As DataRow = ldtTest.NewRow
ldrNewRow("insertID") = 1
ldrNewRow("insertText") = "test"
ldtTest.Rows.Add(ldrNewRow)

Dim lcbdTest As New SqlCommandBuilder(ldaTest)
ldaTest.InsertCommand = lcbdTest.GetInsertCommand(True)

' manually modify the insert command to include the identity column
Dim lparmIdentity As New SqlParameter
lparmIdentity.SourceColumn = "insertID"
lparmIdentity.ParameterName = "@insertID"
lparmIdentity.SqlDbType = SqlDbType.Int
ldaTest.InsertCommand.CommandText = ldaTest.InsertCommand.CommandText.Replace("[insertTest] (", "[insertTest] ([insertID],").Replace("VALUES (", "VALUES (@insertID, ")
ldaTest.InsertCommand.Parameters.Add(lparmIdentity)

ldaTest.Update(ldtTest)

lcmdInsert.CommandText = "set identity_insert insertTest off"
lcmdInsert.ExecuteScalar()

TextBox1.Text = "Success"

Catch ex As Exception
TextBox1.Text = "=========================" & vbNewLine & ex.Message & vbNewLine & "=========================" & vbNewLine & ex.StackTrace
End Try
-------------------( /snip )------------------

I think there is no need to insert insertid explicitly.. it will be
taken care by sqlserver it self. as you set it to IDENTITY.
Just check that out..have you tried it?

Sean Finkel · Aug 12, 2008

nil said:
I think there is no need to insert insertid explicitly.. it will be
taken care by sqlserver it self. as you set it to IDENTITY.
Just check that out..have you tried it?

Nil,
this is for a replicating type system. The identity columns must match exactly on all instances of the data. Thus,
I have a real need for inserting the *exact* value on the replicated systems. There are occurances where there are
breaks in the numbering, ie:
1
2
3
5
10
12
23

I see many people say they are able to do this by simply turning identity_insert on, but it just won't work for me via .Net

Jim Rand · Aug 12, 2008

It is probably too late now, but you might consider another database product
that handles replication gracefully using global autoincrement keys - Sybase
SqlAnywhere.

Sean Finkel said:
Hello all,
I am currently tasked with developing a home-grown replication system for
our product.
I have it completed and am working out the various bugs. One of the issues
we are having deals with inserting data into tables that have identity
columns.
Currently, I am reseeding the autonumber on each insert. As you can
imagine, this results in a ton of context switches over to the database.

I have tried to modify the code to take the insert command that
SqlCommandBuilder generates and modify it to include the identity column
in both the column list and the values list.
I then also add a new parameter and set the name/source column.
I also set identity_insert to on for the table. No matter what I do, I
always get the error from SQL Server that I must explicitly provide the
value for the identity column.

Am I missing a line of code to associate the new parameter with the insert
command?
The parameter is defined, and the insert command's commandText is correct.

Any help would be much appreciated!

Here is a sample app I was using, the test table has two columns -
insertID - int - identity - primary key - not null
insertText - varchar(50)

-------------------( snip )-----------------
Dim lconTest As New SqlConnection
Try
lconTest.ConnectionString = "<removed>"
lconTest.Open()

' now attempt to insert a new record
Dim lcmdInsert As New SqlCommand
lcmdInsert.CommandText = "set identity_insert insertTest on"
lcmdInsert.Connection = lconTest
lcmdInsert.ExecuteScalar()

Dim ldtTest As New DataTable("insertTest")
Dim ldaTest As New SqlDataAdapter("select insertID, insertText
from insertTest", lconTest)
ldaTest.Fill(ldtTest)

Dim ldrNewRow As DataRow = ldtTest.NewRow
ldrNewRow("insertID") = 1
ldrNewRow("insertText") = "test"
ldtTest.Rows.Add(ldrNewRow)

Dim lcbdTest As New SqlCommandBuilder(ldaTest)
ldaTest.InsertCommand = lcbdTest.GetInsertCommand(True)

' manually modify the insert command to include the identity
column
Dim lparmIdentity As New SqlParameter
lparmIdentity.SourceColumn = "insertID"
lparmIdentity.ParameterName = "@insertID"
lparmIdentity.SqlDbType = SqlDbType.Int
ldaTest.InsertCommand.CommandText =
ldaTest.InsertCommand.CommandText.Replace("[insertTest] (", "[insertTest]
([insertID], ").Replace("VALUES (", "VALUES (@insertID, ")
ldaTest.InsertCommand.Parameters.Add(lparmIdentity)

ldaTest.Update(ldtTest)

lcmdInsert.CommandText = "set identity_insert insertTest off"
lcmdInsert.ExecuteScalar()

TextBox1.Text = "Success"

Catch ex As Exception
TextBox1.Text = "=========================" & vbNewLine &
ex.Message & vbNewLine & "=========================" & vbNewLine &
ex.StackTrace
End Try
-------------------( /snip )------------------

Sean Finkel · Aug 12, 2008

Jim said:
It is probably too late now, but you might consider another database product
that handles replication gracefully using global autoincrement keys - Sybase
SqlAnywhere.

Yes indeed it is too late. This product is mature and actually uses a
combination of SQL Server and SQL Server Express. We have pretty much
committed to MS technologies. As well, it's not a true replication
system in the way most people think. We have a master database, with the
"replicated" systems only having a select subset. The big difference is
that these replicated systems are read/write. I suppose it's more of a
"synchronization" system.

Basically, the "client" computers get a copy of the master database
(subset) and then work on that local copy for the day/week then
synchronize back up to the master database at an arbitrary point -
usually the end of the work day or the next morning.

Anyhow, to keep relations proper, we have to make sure identity columns
are synced down to the client computers with the exact data. We use GUID
(uniqueidentifier) values for PKs for those tables that the client
computers can modify.

I simply don't understand why, even though the InsertCommand.Commandtext
property contains the identity column, after I manually modify it, and
add the proper parameter, it STILL won't pass the darn thing over to the
database.

-Sean

Sean Finkel · Aug 12, 2008

Sean said:
Hello all,
I am currently tasked with developing a home-grown replication system
for our product.
I have it completed and am working out the various bugs. One of the
issues we are having deals with inserting data into tables that have
identity columns.
Currently, I am reseeding the autonumber on each insert. As you can
imagine, this results in a ton of context switches over to the database.

I have tried to modify the code to take the insert command that
SqlCommandBuilder generates and modify it to include the identity column
in both the column list and the values list.
I then also add a new parameter and set the name/source column.
I also set identity_insert to on for the table. No matter what I do, I
always get the error from SQL Server that I must explicitly provide the
value for the identity column.

Am I missing a line of code to associate the new parameter with the
insert command?
The parameter is defined, and the insert command's commandText is correct.

Any help would be much appreciated!

Ok, so I added a event handler for the OnRowUpdating event so I could
see what command is actually being passed to the database. Even though I
modified the command and added a parameter, it's not using either (??).
The insert command being passed to the event shows the original column
and parameter list. What a pain!

Well, it looks like I am just going to have to manually make insert
commands for these tables. More code, and it would be nice for a more
elegant solution, but I suppose I really don't have much of a choice, eh?

thanks to those that responded - and if anyone else knows a better way,
please let me know

r,
-Sean

William Vaughn \(MVP\) · Aug 12, 2008

Sean, you're reinventing the wheel. Visual Studio 2005 includes an
implementation of ADO.NET Synchronization Services that does just what you
want to do and more--with almost no code. See
http://channel9.msdn.com/posts/funk...onnected-Client-Support-in-Visual-Studio-SP1/
for an idea about what I'm talking about. The new Local Data Cache class is
easy to work with an deals with a litany of issues including Identity
values. I doubt if it would take much work to incorporate this new
technology into your application.

Of course, for a distributed database, I would recommend a composite key
that includes the owner and an Identity value or a GUID. You don't need
Sybase for that.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Sean Finkel said:
Hello all,
I am currently tasked with developing a home-grown replication system for
our product.
I have it completed and am working out the various bugs. One of the issues
we are having deals with inserting data into tables that have identity
columns.
Currently, I am reseeding the autonumber on each insert. As you can
imagine, this results in a ton of context switches over to the database.

I have tried to modify the code to take the insert command that
SqlCommandBuilder generates and modify it to include the identity column
in both the column list and the values list.
I then also add a new parameter and set the name/source column.
I also set identity_insert to on for the table. No matter what I do, I
always get the error from SQL Server that I must explicitly provide the
value for the identity column.

Am I missing a line of code to associate the new parameter with the insert
command?
The parameter is defined, and the insert command's commandText is correct.

Any help would be much appreciated!

Here is a sample app I was using, the test table has two columns -
insertID - int - identity - primary key - not null
insertText - varchar(50)

-------------------( snip )-----------------
Dim lconTest As New SqlConnection
Try
lconTest.ConnectionString = "<removed>"
lconTest.Open()

' now attempt to insert a new record
Dim lcmdInsert As New SqlCommand
lcmdInsert.CommandText = "set identity_insert insertTest on"
lcmdInsert.Connection = lconTest
lcmdInsert.ExecuteScalar()

Dim ldtTest As New DataTable("insertTest")
Dim ldaTest As New SqlDataAdapter("select insertID, insertText
from insertTest", lconTest)
ldaTest.Fill(ldtTest)

Dim ldrNewRow As DataRow = ldtTest.NewRow
ldrNewRow("insertID") = 1
ldrNewRow("insertText") = "test"
ldtTest.Rows.Add(ldrNewRow)

Dim lcbdTest As New SqlCommandBuilder(ldaTest)
ldaTest.InsertCommand = lcbdTest.GetInsertCommand(True)

' manually modify the insert command to include the identity
column
Dim lparmIdentity As New SqlParameter
lparmIdentity.SourceColumn = "insertID"
lparmIdentity.ParameterName = "@insertID"
lparmIdentity.SqlDbType = SqlDbType.Int
ldaTest.InsertCommand.CommandText =
ldaTest.InsertCommand.CommandText.Replace("[insertTest] (", "[insertTest]
([insertID], ").Replace("VALUES (", "VALUES (@insertID, ")
ldaTest.InsertCommand.Parameters.Add(lparmIdentity)

ldaTest.Update(ldtTest)

lcmdInsert.CommandText = "set identity_insert insertTest off"
lcmdInsert.ExecuteScalar()

TextBox1.Text = "Success"

Catch ex As Exception
TextBox1.Text = "=========================" & vbNewLine &
ex.Message & vbNewLine & "=========================" & vbNewLine &
ex.StackTrace
End Try
-------------------( /snip )------------------

Sean Finkel · Aug 12, 2008

William said:
Sean, you're reinventing the wheel. Visual Studio 2005 includes an
implementation of ADO.NET Synchronization Services that does just what
you want to do and more--with almost no code. See
http://channel9.msdn.com/posts/funk...onnected-Client-Support-in-Visual-Studio-SP1/
for an idea about what I'm talking about. The new Local Data Cache class
is easy to work with an deals with a litany of issues including Identity
values. I doubt if it would take much work to incorporate this new
technology into your application.

Of course, for a distributed database, I would recommend a composite key
that includes the owner and an Identity value or a GUID. You don't need
Sybase for that.

hth

William, thanks so much for the tip. I will look into this and see if it
will satisfy our requirements.

Cheers!

-Sean

Help inserting specific value into an identity column

Sean Finkel

nil

Sean Finkel

Jim Rand

Sean Finkel

Sean Finkel

William Vaughn \(MVP\)

Sean Finkel