[dba-SQLServer] Return new id number

You need to modify your stored procedure to RETURN the new identity value to
Access.

Doris Manning
mikedorism at verizon.net

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
jeffrey.demulling at usbank.com
Sent: Thursday, March 09, 2006 2:29 PM
To: accessd at databaseadvisors.com; dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Return new id number

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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com