How to call SQL stored procedure from VB 6.0 code?

Hi,

I am trying to use a sql stored procedure instead of a query from my VB 6.0 code. I am passing parameters. Basically the stored procedure checks if a given record or set of records exists in the db. Below is my code:

Private Function AlreadyUploaded()
    Dim cn As ADODB.Connection
    Dim rsCheck As ADODB.Recordset
    Dim cmd As ADODB.Command
   
       Dim rsRecCount As Integer  ' For counting records in a record set
   
    Set cn = New ADODB.Connection
    cn.ConnectionString = strConnectionString
    cn.Open
   
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_ImgAlreadyUploaded"


' When the debugger  comes to the line below it jumps out of this function. I think something is wrong here    

    cmd.Parameters.Append cmd.CreateParameter("@strStudy_Date", adVarChar, adParamInput, , " & strStudyDate & ")
    cmd.Parameters.Append cmd.CreateParameter("@strStudyTime", adVarChar, adParamInput, , " & 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) & ")
   
   rsCheck = cmd.Execute
   
    rsRecCount = rsCheck.RecordCount
   
    If rsRecCount > 0 Then
        'If images have already been uploaded, then go to the next sub Directory in the chosen drive
       'MsgBox "Images for him have ALREADY BEEN uploaded."
       intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
       n = m + 1
       RepeatLoop
    ElseIf rsRecCount = 0 Then ' If not uploaded already, then get the matching records from the Db.
       CheckInDatabase
    End If
   
    'Close the connection and recordset object.
    rsCheck.Close
    Set rsCheck = Nothing
    cn.Close
    Set cn = Nothing
End Function

The code is not executing properly, it jumps out of the function after coming to the first statement in the parameters list.  This is the first time I am using stored procedure with VB . So there might be some stupid mistakes here. Also, I need to implement transactions in my stored procedures. So that if there is any error, the transction can be rolled back. how can i implement transcations in my stored procedure below?

CREATE PROCEDURE [dbo].[sp_ImgAlreadyUploaded]
(
@strStudyDate       varchar(20),
@strStudyTime      varchar(20),
@dteBirthDate        datetime,
@PatientFName      varchar(50),
@PatientLName      varchar(50)
)
AS

Select * from studies s inner join patient p on p.id=s.patient_idnum

  WHERE  study_date=@strStudyDate + ' '  +@strStudyTime  and
                p.patient_dob =@dteBirthDate                               AND
               (    ( p.patient_first = @PatientFName     AND p.patient_last = @PatientLName)
             OR
                  (p.patient_last =@PatientFName     and p.patient_first=@PatientLName  )    )
Go


ThanksStart Free Trial