Returning Identity from Stored Procedure VB6 3

[AlanGlynSmith]

Hi, Been trying to achieve this over the last 5 years given up, and continued finding other ways around the problem. I have just revisited the problem and am determined to find a solution. I want to add a new record to a table using a stored procedure and check the returned ID it works perfectly in "SQL Query Analyzer". But trying to call the procedure from VB6 I simply cannot find a way to get at the Returned Identity of the record just added

this is the sproc...

CREATE PROCEDURE [spInsertAdminSection]
@SectionName_2 varchar (50)
AS INSERT INTO [test].[dbo].[tblAdminSections]
(SectionName)
VALUES
(@SectionName_2)
Select Scope_Identity() As Id

As I said this works perfectly using the syntax
spInsertAdminSection "Test" using "SQL Query Analyzer" and it returns the correct next ID.

How the heck do you get the equivalent code to work thru VB6 so far have tried

set rstemp = cn1.spInsertAdminsection Var1
(gets error 3001)

where rstemp is an Adodb.recordset

then tried setting the recordset to capture the returned value as in...

cn1.spinsertadminsection Var1, rstemp
(gets error 3001)

How do you get at the returned ID in VB code is it thru the Connection?

a feeble plea for help???

 

[gmmastros]

I suggest you modify your stored procedure to include the SET NOCOUNT ON statment, like this...

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [spInsertAdminSection]
    @SectionName_2   [COLOR=blue]varchar[/color] (50)
[COLOR=blue]AS[/color] 
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] [test].[dbo].[tblAdminSections] 
     (SectionName)
[COLOR=blue]VALUES[/color] 
     (@SectionName_2)

[COLOR=blue]Select[/color] Scope_Identity() [COLOR=blue]As[/color] Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Why does that help, George?

 

[Sheco]

After the update SQL Server returns text: (1 row(s) affected) So this will come out before the identity.

 

[strongm]

something like

SET @Result=(SELECT Scope_Identity() AS Id)
RETURN @Result

 

[gmmastros]

Well, Bob, I'm glad you asked.

When you have a stored procedure (or query) that has multiple steps, some of those steps will produce a message giving you the number of records affected. If there is only 1 such message, the ADO recordset is exactly as you suspect. However, if there are multiple messages, something funky happens to the recordset. Truth is... I'm not really sure what happens within ADO, but I know this solution works. To see what I mean, open Query Analyzer and copy/paste this code.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Integer[/color] [COLOR=blue]Identity[/color](1,1), Data [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color](1)

[COLOR=blue]Select[/color] Scope_Identity() [COLOR=blue]As[/color] Id

When you run this query, you get exactly what you expect. However, click on the messages tab (at the bottom of the window) and you will see this...

[tt][blue]

(1 row(s) affected)


(1 row(s) affected)

[/blue][/tt]

Now run this...

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Integer[/color] [COLOR=blue]Identity[/color](1,1), Data [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Data) [COLOR=blue]Values[/color](1)

[COLOR=blue]Select[/color] Scope_Identity() [COLOR=blue]As[/color] Id

Now, with the SET NOCOUNT ON, when you click the messages tab, it is completely empty.

Obviously, this data is coming from the SQL Server Database Engine. Rarely is this information useful. In my opinion, every stored procedure should have the SET NOCOUNT ON option. This way, less information is transferred across the network. Normally, this doesn't make much difference, but under heavy loads, it might.

Does this make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Yeah it does, but why does that solve the OP's problem? (Or does it? Obviously your advice is good in a general sense.) I'm thinking the OP's problem is that he hasn't returned a value, as strongm suggests, and doesn't have any code to evaluate that returned variable either.

While we're having this weighty discussion, AGSmith, you may want to read up on the Parameters collection in ADO, because that's how you get arguments to be passed back and forth between VB6 and SQL stored procs.

Bob

 

[Sheco]

[tt]
strSQL = "spInsertAdminSection 'TestValue'"
Set rs = cn.Exectue(strSQL)

msgbox rs("Id")
[/tt]

 

[Sheco]

Oh, and best not to name SQL Server stored procedures starting with "sp" so you can keep yours easily separate from the system procs.

 

[Sheco]

Also, another way to do it is add an output param to the stored procedure and then call the proc with an ADO Command Object after adding both input and output values to the Command object's Parameters collection property.

 

[gmmastros]

Bob,

Like I said, when there are multiple messages, something funky happens to the ADO recordset object. Just for fun, try this...

    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SQL As String
    
    Set DB = CreateObject("ADODB.Connection")
    DB.ConnectionString = "[!]Connection String here[/!]"
    Call DB.Open
    
    SQL = "Declare @Temp Table(RowId Integer Identity(1,1), Data Int) "
    SQL = SQL & "Insert Into @Temp(Data) Values(1) "
    SQL = SQL & "Select Scope_Identity() As Id "
    
    Set RS = CreateObject("ADODB.Recordset")
    Call RS.Open(SQL, DB)
    
    MsgBox RS("Id")

You'll get the error message: Item cannot be found in the collection corresponding to the requested name or ordinal.

Now, add the SET NOCOUNT ON.

    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SQL As String
    
    Set DB = CreateObject("ADODB.Connection")
    DB.ConnectionString = "[!]Connection String here[/!]"
    Call DB.Open
    
    SQL = "SET NOCOUNT ON Declare @Temp Table(RowId Integer Identity(1,1), Data Int) "
    SQL = SQL & "Insert Into @Temp(Data) Values(1) "
    SQL = SQL & "Select Scope_Identity() As Id "
    
    Set RS = CreateObject("ADODB.Recordset")
    Call RS.Open(SQL, DB)
    
    MsgBox RS("Id")

Now, because SET NOCOUNT ON is used, you get the value you expect.

The way I see it, there are 3 ways to return data from a stored procedure. You can set the return value (usually reserved for success of failure, not necessarily data), you could define output parameters (forcing you to use the command object), or you could return a recordset.

With the RETURN method, you are limited to returning an integer.

With the output parameter method, you can return any data type you'd like, and you can even return multiple values by having multiple output parameters.

With the recordset method, you can return, well... a recordset of values. So, while this particular example can be solved by using RETURN or Output Parameters, there can also be many situations where only a recordset will do.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

<Also, another way to do it is add an output param to the stored procedure

In other words, read up on the ADO Parameters collection...

 

[Sheco]

Oh sorry Bob I guess I didn't it well the first time.

 

[Sheco]

didn't READ it well

 

[JoeAtWork]

I think there's a lot of unneeded confusion in this thread because of trying to use a recordset to insert a record. Why use a recordset? This type of work is better suited for a command object. Here's an example (sorry for the length, it was the first example I found):

    'Set up a stored procedure for adding any new records
    strProcName = gstrSPPrefix & "AddTechTraining"
    Set spAddRec = New ADODB.Command
    spAddRec.CommandText = strProcName
    spAddRec.CommandType = adCmdStoredProc
    spAddRec.ActiveConnection = gobjDBConn
    [COLOR=red]
    'Return value (i.e. the new primary key)
    Set adoParam = spAddRec.CreateParameter("PrimaryKey", _
     adInteger, adParamReturnValue, , -1)
    spAddRec.Parameters.Append adoParam
    [/color]
    'ContactNameID parameter
    Set adoParam = spAddRec.CreateParameter("ContactNameID", _
     adInteger, adParamInput, , CLng(mstrContactID))
    spAddRec.Parameters.Append adoParam
    'CourseID parameter
    Set adoParam = spAddRec.CreateParameter("CourseID", adInteger, _
     adParamInput, , 0)
    spAddRec.Parameters.Append adoParam
    'DateTaken parameter
    Set adoParam = spAddRec.CreateParameter("DateTaken", adDate, _
     adParamInput, , Now)
    spAddRec.Parameters.Append adoParam
    'LocationID parameter
    Set adoParam = spAddRec.CreateParameter("LocationID", adInteger, _
     adParamInput, , 0)
    spAddRec.Parameters.Append adoParam
    'Comments parameter
    Set adoParam = spAddRec.CreateParameter("Comments", adLongVarChar, _
     adParamInput, ADO_TEXT_SIZE, Null)
    spAddRec.Parameters.Append adoParam

        'Is this a new record that needs to be saved?
        If objTraining.IsNew Then
            'Use stored procedure to add record so we can
            'retrieve the new primary key
            spAddRec.Parameters("PrimaryKey") = 0
            spAddRec.Parameters("CourseID") = objTraining.CourseID
            spAddRec.Parameters("DateTaken") = objTraining.DateTaken
            spAddRec.Parameters("LocationID") = objTraining.LocationID
            spAddRec.Parameters("Comments") = Ez(objTraining.Comments)
            
            'Execute the stored procedure
            spAddRec.Execute , , adExecuteNoRecords
            
            [COLOR=red]
            'Let the object know it's newly generated ID
            objTraining.ID = spAddRec.Parameters("PrimaryKey")
            [/color]

            objTraining.SetAsSaved  'Reset IsDirty/IsNew flags
        End If

StrongM's post shows what needs to be done on the stored procedure side.

 

[BobRodes]

Well, that was mighty kind of JoeAtWork. I was gonna make Alan work harder, so he'd be sure to learn the process. So Alan, I suggest you spend some time pulling unnecessary parameter objects out of Joe's code as a means of getting familiar with the technique it embodies.

On the other hand, Joe, to answer your question: sometimes if you are going to have a value or especially a group of values, it can be simpler to pull it into a recordset with a single record. George's code is simpler than yours, after all. One can also point out that ADO.Net has a special recordset type that has only one record in it, and pulls faster than a regular cursor. This implies official support for the technique that George outlines.

HTH

Bob

 

[BobRodes]

>Just for fun, try this...

Well, I just did, and not at all to my surprise, it works as George explains. Outstanding, George, and a star from me.

Bob

 

[BobRodes]

>there can also be many situations where only a recordset will do.

George to dispel the "unneeded confusion" in this thread, can you give us a scenario where this would be the case?

 

[gmmastros]

Sure.

My app involves maps. One tiny area of functionality is the ability to add new roads. When doing so, you usually intersect with an existing road. To do this properly (and to make sure you can continue to route along the roads), you need to split the intersected road in to 2 smaller roads. This can happen at both ends of the new road. So, in my app, the insert road stored procedure can return 0 records (no roads were split), 2 records (1 road was split) or 4 records (2 roads were split).

Another example... You insert a new stop on to a bus route. The stored procedure determines the student(s) that are assigned to the bus. So, there could be any number of students that got assigned.

In both cases, I am not returning the identity value of the newly inserted record, but I do want to return a recordset of other information that was affected by the insert.

In my opinion, it is best to minimize the round trips to the database. To do this, you usually end up doing more inside the stored procedure. Also, database servers are usually better computers than end user workstations, so often times it makes sense to push a lot of the heavy processing to the server. Generally, this makes the application run faster and usually causes it to be more stable.

Of course, I should also mention that this problem isn't limited to 'inserting a record'. Even with stored procedures that are selects, this nasty problem can happen.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Thanks George, that's very interesting. I might make two points here: first, I agree that minimizing round trips to the database is a good idea. On the other hand, the point that database servers are better computers than end user workstations is balanced by the point that they are doing a heck of a lot more work, so I always seriously consider the offloading of processing work to the client machine in the form of disconnected recordsets and batch updates. It's another very interesting discussion that is probably outside of the scope of this thread to fully take up, but it seems to me that most scenarios fairly clearly suggest one or the other direction.

Bob