I am calling a SQL Server Stored Procedure from Visual Basic 6, using ADO. The current (and working) code is as follows:
**************************
**********
**********
**********
**********
**********
**********
**********
**********
**********
**********
' Set up a command object for the stored procedure.
With ADOcmd
.ActiveConnection = conProdDB
.CommandText = "pxPatientInsert"
.CommandType = adCmdStoredProc
' Set up a return parameter.
.Parameters.Append .CreateParameter("@ReturnV
alue", adInteger, adParamReturnValue)
' Set up input parameters. We have found that Must have all parameters listed in order even if we don't have an input for it.
.Parameters.Append .CreateParameter("@Partici
pationId",
adInteger, adParamInput, , 40027)
.Parameters.Append .CreateParameter("@LastNam
e", adVarChar, adParamInput, 50, Null)
.Parameters.Append .CreateParameter("@FirstNa
me", adVarChar, adParamInput, 50, Null)
.Parameters.Append .CreateParameter("@ClientS
iteIdentif
ier", adVarChar, adParamInput, 25, Null)
.Parameters.Append .CreateParameter("@LabPati
entCode", adVarChar, adParamInput, 50, "9")
.Parameters.Append .CreateParameter("@LabPati
entCode2",
adVarChar, adParamInput, 50, "Next test")
.Parameters.Append .CreateParameter("@BirthDa
te", adDate, adParamInput, , "10/7/1953")
.Parameters.Append .CreateParameter("@Gender"
, adVarChar, adParamInput, 1, "M")
.Parameters.Append .CreateParameter("@Age", adInteger, adParamInput, , 53)
.Parameters.Append .CreateParameter("@AgeMont
hs", adInteger, adParamInput, , Null)
.Parameters.Append .CreateParameter("@IHMASer
ialNumber"
, adInteger, adParamInput, , Null)
.Parameters.Append .CreateParameter("@BatchId
entifier",
adVarChar, adParamInput, 50, "9999src")
.Parameters.Append .CreateParameter("@Diagnos
isId", adInteger, adParamInput, , Null)
.Parameters.Append .CreateParameter("@ClientD
iagnosisCo
de", adVarChar, adParamInput, 75, Null)
' Execute command
.Execute
PatID = .Parameters("@ReturnValue"
)
End With
Set ADOcmd = Nothing
**************************
**********
**********
**********
**********
**********
**********
**********
**********
**********
**********
The Stored Procedure code is as follows (just to be complete):
**************************
**********
**********
**********
**********
**********
**********
**********
**********
**********
**********
CREATE PROCEDURE [dbo].[pxPatientInsert]
(
@ParticipationId int ,
@LastName varchar(50)= null,
@FirstName varchar(50)= null,
@ClientSiteIdentifier varchar(25)= null,
@LabPatientCode varchar(50),
@LabPatientCode2 varchar(50)= null,
@BirthDate datetime = null,
@Gender char(1)= null,
@Age int = 0,
@AgeMonths int = 0,
@IHMASerialNumber int = null,
@BatchIdentifier varchar(50) = null,
@DiagnosisId int = null,
@ClientDiagnosisCode varchar(75)= null
)
AS
INSERT INTO dbo.tbl_Patient
(ParticipationId, LastName, FirstName, ClientSiteIdentifier, LabPatientCode, LabPatientCode2, BirthDate, Gender, Age, AgeMonths, IHMASerialNumber, BatchIdentifier, DiagnosisId, ClientDiagnosisCode)
VALUES ( @ParticipationId, @LastName, @FirstName, @ClientSiteIdentifier, @LabPatientCode, @LabPatientCode2, @BirthDate, @Gender, @Age, @AgeMonths, @IHMASerialNumber, @BatchIdentifier, @DiagnosisId, @ClientDiagnosisCode);
Return SCOPE_IDENTITY()
GO
**************************
**********
**********
**********
**********
**********
**********
**********
**********
**********
**********
My question: I would like to call the procedure and send just the parameters that I am interested in, instead of all. In Query Analyzer, I would do this by:
EXEC pxPatientInsert @ParticipationId=12345, @LabPatientCode='XYZ123', @BatchIdentifier='Junk'
If I try just to include the parameters I need with the current code (appending only 3 input parameters), the fields get wrong information stored (i.e. LastName gets a value of 'XYZ123' and FirstName gets 'Junk'). How is this done?
Start Free Trial