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...
|
Similar Threads
