How to use the Return Value from Stored Procedure with VB 6.0??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pmud - 26 Jun 2006 23:22 GMT

Hi,

I am returning value from a stored procedure to vb 6.0 function. But I
think something is wrong in the SP or in the function. Its not returnig the
value proprly:
Below is my SP:
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUploaded]
@strStudy_Date varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@strPatientFName varchar(50),
@strPatientLName varchar(50),
@NoOfMatchedRecords integer output

AS

Select @NoOfMatchedRecords= count( *) from studies s inner join
patient p on p.id=s.patient_idnum

WHERE study_date=@strStudy_Date + ' ' +@strStudyTime and
p.patient_dob = @dteBirthDate
AND
( ( p.patient_first = @strPatientFName AND
p.patient_last = @strPatientLName)
OR
(p.patient_last =@strPatientFName and
p.patient_first=@strPatientLName ) )
RETURN @NoOfMatchedRecords
GO

Code:
cn.ConnectionString = strConnectionString
cn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_ImgAlreadyUploaded"

cmd.Parameters.Append cmd.CreateParameter("NoOfMatchedRecords",
adInteger, adParamReturnValue, 6)

cmd.Parameters.Append cmd.CreateParameter("@strStudy_Date", adVarChar,
adParamInput, 20, strStudyDate)
cmd.Parameters.Append cmd.CreateParameter("@strStudyTime", adVarChar,
adParamInput, 20, strStudyTime)
cmd.Parameters.Append cmd.CreateParameter("@dteBirthDate ", adDBTime,
adParamInput, , dteBirthDate)

cmd.Parameters.Append cmd.CreateParameter("@strPatientFName ",
adVarChar, adParamInput, 50, LTrim$(arrPatientName(0)))
cmd.Parameters.Append cmd.CreateParameter("@strPatientLName", adVarChar,
adParamInput, 50, LTrim$(arrPatientName(1)))

cmd.Execute

test = cmd.Parameters("NoOfMatchedRecords")

If test > 0 Then
intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
n = m + 1
RepeatLoop
ElseIf test = 0 Then ' If not uploaded already, then get the matching
records from the Db.
CheckInDatabase
End If

Signature

pmud

Reply to this Message

Paul Clement - 27 Jun 2006 15:34 GMT

¤ Hi,
¤
¤ I am returning value from a stored procedure to vb 6.0 function. But I
¤ think something is wrong in the SP or in the function. Its not returnig the
¤ value proprly:
¤ Below is my SP:
¤ CREATE PROCEDURE [dbo].[sp_ImgAlreadyUploaded]
¤ @strStudy_Date varchar(20),
¤ @strStudyTime varchar(20),
¤ @dteBirthDate datetime,
¤ @strPatientFName varchar(50),
¤ @strPatientLName varchar(50),
¤ @NoOfMatchedRecords integer output
¤
¤ AS
¤
¤ Select @NoOfMatchedRecords= count( *) from studies s inner join
¤ patient p on p.id=s.patient_idnum
¤
¤ WHERE study_date=@strStudy_Date + ' ' +@strStudyTime and
¤ p.patient_dob = @dteBirthDate
¤ AND
¤ ( ( p.patient_first = @strPatientFName AND
¤ p.patient_last = @strPatientLName)
¤ OR
¤ (p.patient_last =@strPatientFName and
¤ p.patient_first=@strPatientLName ) )
¤ RETURN @NoOfMatchedRecords
¤ GO
¤
¤
¤ Code:
¤ cn.ConnectionString = strConnectionString
¤ cn.Open
¤
¤ Set cmd = New ADODB.Command
¤ cmd.ActiveConnection = cn
¤ cmd.CommandType = adCmdStoredProc
¤ cmd.CommandText = "sp_ImgAlreadyUploaded"
¤
¤ cmd.Parameters.Append cmd.CreateParameter("NoOfMatchedRecords",
¤ adInteger, adParamReturnValue, 6)
¤
¤ cmd.Parameters.Append cmd.CreateParameter("@strStudy_Date", adVarChar,
¤ adParamInput, 20, strStudyDate)
¤ cmd.Parameters.Append cmd.CreateParameter("@strStudyTime", adVarChar,
¤ adParamInput, 20, strStudyTime)
¤ cmd.Parameters.Append cmd.CreateParameter("@dteBirthDate ", adDBTime,
¤ adParamInput, , dteBirthDate)
¤
¤ cmd.Parameters.Append cmd.CreateParameter("@strPatientFName ",
¤ adVarChar, adParamInput, 50, LTrim$(arrPatientName(0)))
¤ cmd.Parameters.Append cmd.CreateParameter("@strPatientLName", adVarChar,
¤ adParamInput, 50, LTrim$(arrPatientName(1)))
¤
¤ cmd.Execute
¤
¤ test = cmd.Parameters("NoOfMatchedRecords")
¤
¤ If test > 0 Then
¤ intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
¤ n = m + 1
¤ RepeatLoop
¤ ElseIf test = 0 Then ' If not uploaded already, then get the matching
¤ records from the Db.
¤ CheckInDatabase
¤ End If

I don't believe the Return statement in your sp is required. See the following example:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q300488

Paul
~~~~
Microsoft MVP (Visual Basic)

Reply to this Message

pmud - 27 Jun 2006 15:51 GMT

Hi Paul,

Thats very helpfu. But I am bnot sure where to put the Command.Execute
statement, i,.e after the return value is used or before that? Below is my
code:

Set cmd = New adodb.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_ImgAlreadyUploaded"

cmd.Parameters.Append cmd.CreateParameter("@intRetCountMatchedRec",
adInteger, adParamOutput, 4)

cmd.Parameters.Append cmd.CreateParameter("@strStudy_Date", adVarChar,
adParamInput, 20, strStudyDate)

Dim test As Integer
test = cmd.Parameters("@intRetCountMatchedRec")
If test > 0 Then
intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
n = m + 1
RepeatLoop
ElseIf test = 0 Then
CheckInDatabase
End If

Where to put cmd.Execute?

Thanks

Signature

pmud

> ¤ Hi,
> ¤
[quoted text clipped - 71 lines]
> ~~~~
> Microsoft MVP (Visual Basic)

Reply to this Message

Dmitriy Antonov - 27 Jun 2006 16:14 GMT

> Hi Paul,
>
[quoted text clipped - 26 lines]
>
> Thanks

Restore it where it was before. Honestly, I don't think that Return keyword
in the SP would change a lot but you can try. Anyway it shouldn't be

RETURN @NoOfMatchedRecords

but rather just

RETURN

What is important here is that parameter's type was set to Output.

Dmitriy..

Reply to this Message

pmud - 27 Jun 2006 16:47 GMT

Hi,

The code below worked for me:
Set rsCheck = New ADODB.Recordset
Set rsCheck.ActiveConnection = cn
rsCheck.Open cmd

Also, i figured that the order of the parameters whicha re passed from vb
should be same as the order of teh parameters which the stored proceure
accepts.

Thanks

Signature

pmud

> > Hi Paul,
> >
[quoted text clipped - 39 lines]
>
> Dmitriy..

Reply to this Message

VB Forum / Database Access / June 2006

How to use the Return Value from Stored Procedure with VB 6.0??