ADO: how to execute SQL Server stored procedure and get return val

[jrsmoots]

I have a stored procedure that looks like this:
--=======
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE procAddNewPatient
-- parameters for the stored procedure here
@PatientIDOLD nvarchar(50),
@SiteID tinyint,
@PatientID int Output
AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblPatients (PatientIDOLD, SiteID)
VALUES(@PatientIDOLD, @SiteID)

SELECT @PatientID = Scope_Identity()
END
GO
--======

I then call it with this ADO code, which is in an Access 2002 'mdb'

'===
Dim cmd As New adoDB.Command
Dim param1 As New adoDB.Parameter
Dim param2 As New adoDB.Parameter
'Dim param3 As New adoDB.Parameter

'These are here, from the stored procedure, for reference
'@PatientIDOLD nvarchar(50),
'@SiteID tinyint,
'@PatientID int Output


With cmd
.CommandText = "procAddNewPatient"
.CommandType = adCmdStoredProc
.ActiveConnection = CurrentProject.Connection

'the value here is hard coded for tesing purposes
Set param1 = .CreateParameter("@PatientIDOLD", adVarWChar,
adParamInput, 50, "9999999999-ZZ")
.Parameters.Append param1

'the value here is hard coded for tesing purposes
Set param2 = .CreateParameter("@SiteID", adTinyInt,
adParamInput, , 1)
.Parameters.Append param2

Set param3 = .CreateParameter("@PatientID", adInteger,
adParamOutput)
.Parameters.Append param3

.Execute Options:=adExecuteNoRecords

Set param1 = Nothing
Set param2 = Nothing
End With

'Two different ways to display the output parameter
MsgBox param3.Value
MsgBox cmd.Parameters("@PatientID")

Set param3 = Nothing
Set cmd = Nothing

'===

Every time I run the code, it dies when it hits the .Execute command, saying
there's an overflow.

I can't figure this one out, though I'm guessing I've got a setting wrong in
the createparameter statements...

 

[Stefan Hoffmann]

hi,

Every time I run the code, it dies when it hits the .Execute command, saying
there's an overflow.

This indicates a data type mismatch.

I can't figure this one out, though I'm guessing I've got a setting wrong in
the createparameter statements...

Check the parameter data types. Especially the adTinyInt parameter.


mfG
--> stefan <--