Solved

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

Posted on 2004-09-27
5
933 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
t-sql complement 8 30
Getting certain data from a string 1 23
INSERT INTO SELECT JOIN THING 2 24
Complex SQL 10 33
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now