[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Returning Scope_Identity from stored procedure and assigning it to a variable in VB6

Posted on 2005-05-08
3
Medium Priority
?
611 Views
Last Modified: 2013-12-25
Well for days I have been trying to assign the scope_identity from a stored procedure to a variable in VB6 with NO LUCK.  I have tried so many different ways that I no longer can remember what is what.
I am hoping someone will help with an actual answer and not some links to read.
My stored procedures is

CREATE PROCEDURE [dbo].[sp_People_Earl_Insert_Contacts]
      @CompanyID int,
      @LastName varchar(50),
      @FirstName varchar(50),
      @Suffix char(10),
      @AddressCodeID int,
      @LanguageTypeID int,
      @JobTitleID int,
      @Remarks varchar(1000),
      @EmailAddr varchar(50),
      @WorkPhone char(12),      
      @MobilePhone char(12),
      @FAXPhone char(12),
      @PeopleStatusID int,
      @DepartmentID int,
      @PhoneExt char(15),
      @LocationID int



AS


INSERT INTO [dbo].[People_Earl] (
      CompanyID,
      LastName,
      FirstName,
      Suffix,
      AddressCodeID,
      LanguageTypeID,
      JobTitleID,
      Remarks,
      EmailAddr,
      WorkPhone,
      MobilePhone,
      FAXPhone,
      PeopleStatusID,
      DepartmentID,
      PhoneExt,
      LocationID)

VALUES (
      @CompanyID,
      @LastName,
      @FirstName,
      @Suffix,
      @AddressCodeID,
      @LanguageTypeID,
      @JobTitleID,
      @Remarks,
      @EmailAddr,
      @WorkPhone,      
      @MobilePhone,
      @FAXPhone,
      @PeopleStatusID,
      @DepartmentID,      
      @PhoneExt,
      @LocationID)

SELECT Scope_Identity() As PeopleID

GO

I am inserting the values through the parameters
AddParam just formats the info for me.

                  AddParam "@CompanyID", adInteger, adParamInput, , mlngCompanyID
                  AddParam "@LastName", adVarChar, adParamInput, 50, Str2Field(mstrLastName)
                  AddParam "@FirstName", adVarChar, adParamInput, 50, Str2Field(mstrFirstName)
                  AddParam "@Suffix", adVarChar, adParamInput, 10, Str2Field(mstrSuffix)
                  AddParam "@AddressCodeID", adInteger, adParamInput, , mlngAddressCodeID
                  AddParam "@LanguageTypeID", adInteger, adParamInput, , mlngLanguageTypeID
                  AddParam "@JobTitleID", adInteger, adParamInput, , mlngJobTitleID
                  AddParam "@Remarks", adVarChar, adParamInput, 1000, Str2Field(mstrRemarks)
                  AddParam "@EmailAddr", adVarChar, adParamInput, 50, Str2Field(mstrEmailAddr)
                  AddParam "@WorkPhone", adVarChar, adParamInput, 12, Str2Field(mstrWorkPhone)
                  AddParam "@MobilePhone", adVarChar, adParamInput, 12, Str2Field(mstrMobilePhone)
                  AddParam "@FAXPhone", adVarChar, adParamInput, 12, Str2Field(mstrFaxPhone)
                  AddParam "@PeopleStatusID", adInteger, adParamInput, , mlngPeopleStatusID
                  AddParam "@DepartmentID", adInteger, adParamInput, , mlngDepartmentID
                  AddParam "@PhoneExt", adVarChar, adParamInput, 15, Str2Field(mstrPhoneExt)
                  AddParam "@LocationID", adInteger, adParamInput, , mlngLocationID

I then

            Set moCmd.ActiveConnection = moDataConn.Connection
            moCmd.CommandText = "sp_People_Earl_Insert_Contacts"
            moCmd.CommandType = adCmdStoredProc

            Set moRS = moCmd.Execute
           
Now I have tried so many different variations that this is my latest
            moCmd.Parameters.Refresh
'            moRS.Open moCmd
            mlngPeopleID = mors(peopleid)

I've either received an error back or null

PLEASE..... put in an actual example
0
Comment
Question by:Sheritlw
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
PePi earned 2000 total points
ID: 13961608
if this is SQL Server then add another parameter to your stored proc:

    @RecID int OUTPUT

then after the insert command add:

    SELECT @RecID = @@IDENTITY

in your vb code add another parameter:

AddParam "@RecID", adInteger, adParamOutput

you do not need a recordset here, just go:

     moCmd.Execute
     mlngPeopleID = moCmd.Parameter("@RecID").Value


HTH!!!


0
 
LVL 6

Expert Comment

by:PePi
ID: 13961627
By the way, @@IDENTITY and Scope_Identity() are the same. so either one will work
0
 

Author Comment

by:Sheritlw
ID: 13962358

YOU ARE THE BEST!!!  IT WORKS GREAT!!

Since there have so many posts on this subject, I thought I would put an example of the stored procedure and my VB code for others.


Stored Procedure SQL Server 2000

CREATE PROCEDURE [dbo].[sp_People_Earl_Insert_Contacts]
      @CompanyID int,
      @LastName varchar(50),
      @FirstName varchar(50),
      @Suffix char(10),
      @AddressCodeID int,
      @LanguageTypeID int,
      @JobTitleID int,
      @Remarks varchar(1000),
      @EmailAddr varchar(50),
      @WorkPhone char(12),      
      @MobilePhone char(12),
      @FAXPhone char(12),
      @PeopleStatusID int,
      @DepartmentID int,
      @PhoneExt char(15),
      @LocationID int,
      @RecID int OUTPUT



AS


INSERT INTO [dbo].[People_Earl] (
      CompanyID,
      LastName,
      FirstName,
      Suffix,
      AddressCodeID,
      LanguageTypeID,
      JobTitleID,
      Remarks,
      EmailAddr,
      WorkPhone,
      MobilePhone,
      FAXPhone,
      PeopleStatusID,
      DepartmentID,
      PhoneExt,
      LocationID)

VALUES (
      @CompanyID,
      @LastName,
      @FirstName,
      @Suffix,
      @AddressCodeID,
      @LanguageTypeID,
      @JobTitleID,
      @Remarks,
      @EmailAddr,
      @WorkPhone,      
      @MobilePhone,
      @FAXPhone,
      @PeopleStatusID,
      @DepartmentID,      
      @PhoneExt,
      @LocationID)

SELECT @RecID = Scope_Identity()

GO


VB6 Code

A routine to set my parameters

Private Sub AddParam(ByVal PName As String, _
                     ByVal PType As ADODB.DataTypeEnum, _
                     ByVal PDir As ADODB.ParameterDirectionEnum, _
                     Optional ByVal PSize As Long, _
                     Optional ByVal PValue As Variant, _
                     Optional ByVal ZeroAsNull As Boolean = False)
   
With moCmd

   .Parameters.Append .CreateParameter(PName, PType, PDir, PSize, PValue)

End With

End Sub

A Procedure to Add a new record while returning the Scope_Identity

Public Function AddNew() As Boolean

AddNew = False

My connection and recordset have already been set in another routine

Set moCmd = New ADODB.Command

                  AddParam "@CompanyID", adInteger, adParamInput, , mlngCompanyID
                  AddParam "@LastName", adVarChar, adParamInput, 50, Str2Field(mstrLastName)
                  AddParam "@FirstName", adVarChar, adParamInput, 50, Str2Field(mstrFirstName)
                  AddParam "@Suffix", adVarChar, adParamInput, 10, Str2Field(mstrSuffix)
                  AddParam "@AddressCodeID", adInteger, adParamInput, , mlngAddressCodeID
                  AddParam "@LanguageTypeID", adInteger, adParamInput, , mlngLanguageTypeID
                  AddParam "@JobTitleID", adInteger, adParamInput, , mlngJobTitleID
                  AddParam "@Remarks", adVarChar, adParamInput, 1000, Str2Field(mstrRemarks)
                  AddParam "@EmailAddr", adVarChar, adParamInput, 50, Str2Field(mstrEmailAddr)
                  AddParam "@WorkPhone", adVarChar, adParamInput, 12, Str2Field(mstrWorkPhone)
                  AddParam "@MobilePhone", adVarChar, adParamInput, 12, Str2Field(mstrMobilePhone)
                  AddParam "@FAXPhone", adVarChar, adParamInput, 12, Str2Field(mstrFaxPhone)
                  AddParam "@PeopleStatusID", adInteger, adParamInput, , mlngPeopleStatusID
                  AddParam "@DepartmentID", adInteger, adParamInput, , mlngDepartmentID
                  AddParam "@PhoneExt", adVarChar, adParamInput, 15, Str2Field(mstrPhoneExt)
                  AddParam "@LocationID", adInteger, adParamInput, , mlngLocationID
                  AddParam "@RecID", adInteger, adParamReturnValue

Set moCmd.ActiveConnection = moDataConn.Connection
           
moCmd.CommandText = "sp_People_Earl_Insert_Contacts"
moCmd.CommandType = adCmdStoredProc
moCmd.Execute
           
mlngPeopleID = moCmd.Parameters("@RecID").Value

End Function
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

872 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