Help inserting specific value into an identity column

S

Sean Finkel

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 )------------------
 
N

nil

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 )------------------
Click to expand...

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

Sean Finkel

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?
Click to expand...

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
 
J

Jim Rand

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 )------------------
Click to expand...
 
S

Sean Finkel

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.
Click to expand...

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
 
S

Sean Finkel

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!
Click to expand...

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
 
W

William Vaughn \(MVP\)

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 )------------------
Click to expand...
 
S

Sean Finkel

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
Click to expand...

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

Cheers!

-Sean
 

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

How do i insert explicit ID on an identity Column? 4
Inserting identity columns with CommandBuilder generated commands? 9
Getting the value of an identity column after an insert 3
Inserting into an identity column fails 1
Error inserting into an identity column 1
getting Identity value back 5
SqlCommandBuilder - not setting my identity column 1
Identity Column in SQL Server View 3

Top