asked on

Retrieving record id from identity - Stored Procedure and VB.Net

I have looked through the examples and found what I thought was the answer. The problem is how to get the @@identity from the stored procedure into my VB.Net application. Any help would be greatly appreciated as I will be using this in several stored procedures/vb code.

Stored Procedure:
CREATE PROCEDURE [dbo].[sp_jrn_InsertNewRecordToCounter] (@TableName VarChar(25))
AS
SET NOCOUNT ON
EXEC('INSERT INTO ' + @TableName + ' ([Date]) VALUES(GetDate())')

SELECT @@identity
RETURN @@identity
SET NOCOUNT OFF
GO

VB.Net code:
'insert a new record into the project table
'declare a new sqlcommand
Dim SQLCommand As SqlClient.SqlCommand

'declare a sql transaction name
Dim myTrans As SqlClient.SqlTransaction

'insert a new record in to the appropriate table (strTable)
'define the sql command to be run
SQLCommand = New SqlClient.SqlCommand("sp_jrn_InsertNewRecordToCounter", conn)
SQLCommand.CommandType = CommandType.StoredProcedure
SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@TableName", SqlDbType.VarChar, 25))
SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@@identity", SqlDbType.Int))
SQLCommand.Parameters("@TableName").Value = CStr(strTable)

'make sure connection to sql server is open
conn.Open()

'set the sql command's connection to conn
SQLCommand.Connection = conn

' Begin the transaction.
myTrans = conn.BeginTransaction

' Assign transaction object for a pending local transaction
SQLCommand.Transaction = myTrans

'run the update query
SQLCommand.ExecuteNonQuery()

'commit the trans
myTrans.Commit()

'close the connection to the sql server
conn.Close()
Dim intID As Integer = SQLCommand.Parameters.Item("@@identity").Value()
MsgBox(intID)
Exit Sub

Thanks in advance, MT

ASKER CERTIFIED SOLUTION

FDzjuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MajikTara

ASKER

Hi FDzjuba,
When I try as you indicated I receive the following error:
Procedure 'sp_jrn_InsertNewRecordToCounter' expects parameter '@IdentityID', which was not supplied.

I then added the following to the vb.net code after "SQLCommand.Parameters.Add(New SQLClient.SQLParameter("...... :
SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@IdentityID", SQLDbType.Int, ParameterDirection.OutPut)

I received the same message. It appears that I have some problem with my code extracting the results. Any other items I may have missed?

(Note SQLCommand.Execute does not exist, it's SQLCommand.ExecuteNonQuery and "TableName" needs to be "@TableName")

MT

FDzjuba

i don't have VB to my disposal, so kind of shot in the dark, try this

' Build Command
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "[sp_jrn_InsertNewRecordToCounter]"
End With

' Build Parameters
Dim par As ADODB.Parameter
With cmd
Set par = .CreateParameter("IdentityID", adInt, adParamOutput)
.Parameters.Append par
Set par = .CreateParameter("TableName", adVarChar, adParamInput)
.Parameters.Append par
End With

' Execute Query
With cmd
.Parameters("TableName").Value = "mytable"
.Execute
MsgBox .Parameters("IdentityID").Value
End With

FDzjuba

aha, just realized you VB.NET that is why you've used class SqlClient, just to let you know its kind a messy, the above VB6 code should work with VB.NET anyway

MajikTara

ASKER

FDzjuba,

Your first version of the code was correct, but one thing was missing. Please note the lines preceeded with *. I don't understand why it requires the line for parameter direction rather than the overloaded property. Thanks for the help and I have awarded the points to you.

CREATE PROCEDURE [dbo].[sp_jrn_InsertNewRecordToCounter]
@TableName VarChar(25)
@IdentityID int OUTPUT

AS
SET NOCOUNT ON
EXEC('INSERT INTO ' + @TableName + ' ([Date]) VALUES(GetDate())') ; <--- the semicolon moved here from next line
SET @IdentityID = @@identity
SET NOCOUNT OFF
GO

'declare a new sqlcommand
Dim SQLCommand As SqlClient.SqlCommand

'declare a sql transaction name
Dim myTrans As SqlClient.SqlTransaction

'insert a new record in to the appropriate table (strTable)
'define the sql command to be run
SQLCommand = New SqlClient.SqlCommand("sp_jrn_InsertNewRecordToCounter", conn)
SQLCommand.CommandType = CommandType.StoredProcedure
SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@TableName", SqlDbType.VarChar, 25))
***** SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@IdentityID", SqlDbType.Int))
***** SQLCommand.Parameters("@ID").Direction = ParameterDirection.Output
conn.Open()
***** SQLCommand.ExecuteNonQuery

Dim nIdentity as integer
nIdentity = SQLCommand.Parameters("IdentityID").Value

Thanks, MT