Randall R Anthony
RRANTHON at sentara.com
Thu Mar 9 14:07:26 CST 2006
Jeff,
Not sure on where the value would be returned in the access calling
procedure, but I'm using VB with SQL2K on BE. In the SP, use the
following:
@RetValue Int Output --after your last declaration
----return Artificial key -- in place of your select scope_identity
statement
Select @RetValue = @@Identity
>From my VB module, what returns the value to be used further on in my
code is:
cmd.Parameters("@RetValue").Value
Hope this helps a little bit...
>>> jeffrey.demulling at usbank.com 03/09/06 2:29 PM >>>
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.
==============================================================================
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com