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???

 

[AlanGlynSmith]

I hope you could hear the sound of pennies dropping!! Had to give a star to both george and joeatwork. Excellent well thats 5 years of head scratching finally at an end. brilliant... both methods worked well. cheers chaps!

 

[JoeAtWork]

In my opinion, every stored procedure should have the SET NOCOUNT ON option.

Well, I learned something from George today. I will have to remember that for the future.

But I will stick with my opinion that for the OP's original requirement, to insert a record and receive back the new identity value, that a command object is the most efficient way to go.

I often see programmers opening up recordsets to insert, update, or delete records. This is almost always a less efficient choice than using a command object that calls a stored procedure.

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.

Are you referring to the ExecuteScalar method? If so, I don't think it can be thought of as a "recordset", since the method returns a single value, not a DataRow object. If you meant something else, can you elaborate?

 

[BobRodes]

Well, yes, fair enough, perhaps it isn't a recordset. But the point is that a forwardonly recordset with only one value has been in rather general use, which is reflected in the architecture in .Net. Also, it and may have efficiencies built into it that we're not aware of.

However, I don't use it personally, preferring the command object as you do. That's why I asked George for the scenarios, which I found helpful.

Bob