How to get the Identity primary key value?

[hugobooze]

I have a table (tblArticleGroup) with a automatic increasing primary key (ArticleGroupID). I'm using a datatable to insert a new row in the table. Then I want to know the generated ArticleGroupID, but I haven't found a solution.

 

I'm 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 doesn't work, because ArticleGroupID = 0 all the time. Can anyone see what's 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()

[Cassio]

This is what I use to do:

 

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 don't have .NET in hands now, so you may find some syntax error.

[hugobooze]

Thank you!

 

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

 

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

[hugobooze]

How to solve it by using a Datatable?

 

Well. That's 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:

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 doesn't work is when an identity primary key must be returned, as mentioned...

[hugobooze]

Well, it seems like it was a bad idea to re-formulate my question in the same thread. I'll create a new thread instead. This thread can be closed