Solved

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

Posted on 2004-09-27
5
936 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:MajikTara
  • 3
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
FDzjuba earned 500 total points
ID: 12162483
1st metod, but not the best:

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

AS
SET NOCOUNT ON
EXEC('INSERT INTO ' +  @TableName + ' ([Date])  VALUES(GetDate())')
   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))
                    conn.Open()
                    SQLCommand.Execute

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

that should work, maybe you need to work a little more on the vb code, cos i am not sure that its syntax correct
0
 

Author Comment

by:MajikTara
ID: 12164540
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
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12165364
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
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12165372
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
0
 

Author Comment

by:MajikTara
ID: 12171447
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
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question