How to get the Identity primary key value?

H

hugobooze

Active member
Joined
Jul 22, 2003
Messages
25
Location
Sweden
I have a table (tblArticleGroup) with a automatic increasing primary key (ArticleGroupID). Im using a datatable to insert a new row in the table. Then I want to know the generated ArticleGroupID, but I havent found a solution.

Im using SQL Server 7.0

I checked on google and found a good example in C##. I translated it to vb.net (see below), but it doesnt work, because ArticleGroupID = 0 all the time. Can anyone see whats wrong with the code:


Dim conn As New SqlConnection(myConnectionString)
Dim da As New SqlClient.SqlDataAdapter("Select * from tblArticleGroup", conn)

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim builder As New SqlClient.SqlCommandBuilder(da)
Dim Command As SqlCommand
Command = builder.GetInsertCommand()
Command.CommandText += " ; select @@IDENTITY as ArticleGroupID"
Dim dt As New DataTable()
da.FillSchema(dt, SchemaType.Source)

conn.Open()
dt.Clear()

Dim row As DataRow
row = dt.NewRow()
row("ChargeCodeSearchStr") = ""
row("Name") = "Testgroup"
row("IsTemporary") = True
dt.Rows.Add(row)

da.Update(dt)
dt.AcceptChanges()
MsgBox(row("ArticleGroupID")) This returns "0" all the time!!!
conn.Close()
 
This is what I use to do:

Code: 
strSQL = "INSERT INTO Table(field1,field2) VALUES(@field1,@field2); SET @ID_Field = (SELECT @@Identity);"

Dim cmd as New SQLCommand(strSQL,connection)
cmd.Parameter.Add("@Field1",value1)
cmd.Parameter.Add("@Field2",value2)
cmd.Parameter.Add("@ID_Field").Direction = ParameterDirection.Output

connection.Open
Dim dr as SQLDataReader = cmd.ExecuteReader
ID = dr.Items("@ID_Field")

I dont have .NET in hands now, so you may find some syntax error.
 
Thank you!

Thank you! It worked after some justifications (se below)

Code: 
Dim strSQL As String
Dim connection As New SqlConnection(connectionString)
strSQL = "INSERT INTO tblArticleGroup(Name, IsTemporary, ChargeCodeSearchStr) VALUES(@Name, @IsTemporary, @ChargeCodeSearchStr); Select @@Identity as ArticleGroupID ;"

Dim cmd As New SqlCommand(strSQL, connection)
cmd.Parameters.Add("@Name", "Test2Group")
cmd.Parameters.Add("@IsTemporary", "0")
cmd.Parameters.Add("@ChargeCodeSearchStr", "")
Dim param As New SqlParameter()
param.Direction = ParameterDirection.Output
param.ParameterName = "@ArticleGroupID"
param.SqlDbType = SqlDbType.Int
cmd.Parameters.Add(param)

connection.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
dr.Read()
MsgBox(dr.Item("ArticleGroupID"))
 
How to solve it by using a Datatable?

Well. Thats a way to retrieve the identity primary key, but the problem is: I have to use a datatable (and probably a dataadapter).

Is there any way to solve the problem by using a Datatable?




If you want to know why I want to use a datatable:

I have one Table-class with one UpdateDataTable-method and one UpdateDatabase-method. Then I have one class for each table in my database that Inherits this Table-class. The UpdateDatabase-method looks like this:
Code: 
Public Sub updateDatabase()
   SqlDataAdapter = New SqlClient.SqlDataAdapter(strSql_DbUpdate, connectionString)
   SqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

   sqlCommBuild = New SqlClient.SqlCommandBuilder(SqlDataAdapter)

   SqlDataAdapter.Update(DataTable)
   DataTable.AcceptChanges()
End Sub
The only thing that differs between the tables is the protected String variable "strSql_DbUpdate". This method can be used ALWAYS, FOR ALL TABLES: When rows is deleted, changed or inserted. The only time it doesnt work is when an identity primary key must be returned, as mentioned...
 
Well, it seems like it was a bad idea to re-formulate my question in the same thread. Ill create a new thread instead. This thread can be closed
 
Back
Top