[dba-SQLServer] Return new id number

jeffrey.demulling at usbank.com jeffrey.demulling at usbank.com
Thu Mar 9 13:29:03 CST 2006


Basics: FE = A2K
      BE = SQL2K

I am trying to use the code below with the accompanying stored procedure
when inserting a new record into the employee table.  Once the employee is
entered into the table I want to know the record number for this employee.

The access code for inserting the employee and returning the new id number
is:

Public Function lngAddEmployee(strEmployeeName As String, strEmployeeEmail
As String, varEmployeePhone As Variant, lngEmployeeManager As Long) As Long
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cmd.CommandText = "sproc_APPEND_New_Employee"
cmd.CommandType = adCmdStoredProc

con = setSQLServerConnectionApplication(2, 2)
con.Open

cmd.ActiveConnection = con

cmd.Parameters.Refresh
cmd(1) = strEmployeeName
cmd(2) = strEmployeeEmail
cmd(3) = varEmployeePhone
cmd(4) = lngEmployeeManager

Set rs = cmd.Execute

lngAddEmployee = rs.Fields(0).Value

con.Close

Set rs = Nothing
Set con = Nothing
Set cmd = Nothing
End Function

I have been trying to test it using:
Sub Test()
MsgBox lngAddEmployee("Test", "TestEmail", "TestPhone", 7)
MsgBox "Finished"
End Sub

In the function it hangs on the line
lngAddEmployee = rs.Fields(0).Value

The stored procedure is the following and if I use Query Analyzer it works
just fine and returns the id of the new record.
CREATE PROCEDURE dbo.sproc_APPEND_New_Employee
-----------------------------------------------------------------------------------------
-- Procedure:           APPEND_New_Employee
-- Author:              Jeffrey F. Demulling
-- Create Date:         2006-03-09
-- Database Name:       DebtMaster
-- Table Names:         tblEmployee
-- Purpose:             Upload a new Employee
--
--
-- Maintenance log- Most recent changes first
-- Updated By            Updated Date                         Description
of Change
------------------------------------------------------------------------------------------
--
--
------------------------------------------------------------------------------------------
@strEmployeeName nvarchar(255),
@strEmployeeEmail nvarchar(255),
@strEmployeePhone nvarchar(50),
@intEmployeeManager int

AS

INSERT INTO
tblEmployee
( EmployeeName,
EmployeeEmail,
EmployeePhone,
EmployeeManager)

VALUES
( @strEmployeeName,
@strEmployeeEmail,
@strEmployeePhone,
@intEmployeeManager)

SELECT SCOPE_IDENTITY() AS 'InternalEmployeeNumber'
GO

Can anyone see what I am doing wrong?

TIA

Jeffrey F. Demulling
Project Manager
U.S. Bank Corporate Trust Services
60 Livingston Avenue
EP-MN-WS3C
St. Paul, MN  55107-2292
Ph: 651-495-3925
Fax: 651-495-8103
Pager: 888-732-3909
Text Messaging: 8887323909 at my2way.com
email: jeffrey.demulling at usbank.com


------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================




More information about the dba-SQLServer mailing list