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.
==============================================================================