Solved

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

Posted on 2004-09-27
5
945 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

707 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