Link to home
Start Free TrialLog in
Avatar of pmud
pmud

asked on

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


Thanks
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

defining the parameters like this should work better:
    cmd.Parameters.Append cmd.CreateParameter("@strStudy_Date", adVarChar, adParamInput, 50 , strStudyDate )
    cmd.Parameters.Append cmd.CreateParameter("@strStudyTime", adVarChar, adParamInput, 50 , 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) )
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pmud
pmud

ASKER

Hi,

Thats helpful. But now when I come to the execute statement, I get Sorry for the incovenience and the visual studio closes on me! I think something is wrong with the execute line or my SP:

I modified my execute statement as
 cmd.Execute rsCheck, , adExecuteNoRecords

my stored procedure is:

CREATE PROCEDURE [dbo].[sp_ImgAlreadyUploaded]
(
@strStudy_Date       varchar(20),
@strStudyTime      varchar(20),
@dteBirthDate        datetime,
@strPatientFName      varchar(50),
@strPatientLName      varchar(50)
)

AS

Select * 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  )    )
GO


Thanks
> cmd.Execute rsCheck, , adExecuteNoRecords
should be:
 cmd.Execute rsCheck
as you actually WANT the records back
Avatar of pmud

ASKER

Hi angel III,

I tried the statement you mentioned above and when the debugger reaches that line and I hit f5, Visual Studio again closes on me. So i tried to exceute like:
cmd.Execute and then the debugger passed to the next statement. So i think, the problem is with this statement only. I need to get all the records in the rsCheck recordSet.

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pmud

ASKER

Hi,

The code below works. But when I use the stmt, rsCheck.RecordCount after ths last statemnt in the code below, the debugger doesnt rech that line and exits the function.

set rsCheck = new adodb.recodset
set rsCheck.ActiveConnection = cn
rsCheck.open  cmd

As acperkins mentioned that using the RecordCount is not a good idea so I tried to calcuate the no. of records in teh stored procedure itself and tried to use the return value from the SP  in my code. But gaian the debugger doent rech that line where I use the return value. SOmething is wrong I think with the synatx: Below is my SP:

CREATE PROCEDURE [dbo].[sp_ImgAlreadyUploaded]

@NoOfMatchedRecords integer  output,
 @strStudy_Date       varchar(20),
 @strStudyTime      varchar(20),
@dteBirthDate        datetime,
 @strPatientFName      varchar(50),
@strPatientLName      varchar(50),

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

VB Code:
    Dim NoOfMatchedRecords As Integer
   
    Dim cn As adodb.Connection
    Dim rsCheck As adodb.Recordset
    Dim cmd As adodb.Command
         
    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"
   
     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 ", adDBTimeStamp, adParamInput, 8, 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
     
     NoOfMatchedRecords = cmd.Parameters("NoOfMatchedRecords")
 
    If NoOfMatchedRecords > 0 Then
      intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
       n = m + 1
       RepeatLoop
    ElseIf NoOfMatchedRecords = 0 Then  
          CheckInDatabase
    End If
   
    'Close the connection and recordset object.
    rsCheck.Close
    Set rsCheck = Nothing
    cn.Close
    Set cn = Nothing

Thanks for all your help.
>>so I tried to calcuate the no. of records in teh stored procedure itself and tried to use the return value from the SP  in my code.<<
In order to read an output parameter AND return a resultset you must close the resultset first, before attempting to read the output (in your case Return) value.  I know, this is not very useful, but that is the way it is.  Alternatively you can use the less efficient method that angelIII has suggested, namely:
   rsCheck.open  cmd
This wil allow you to set the recordset properties so that you can use the RecordCount property.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pmud

ASKER

Hi All,

Thanks so mcuh for your responses. With the error handling i figures that the order of parameters which are passed from VB should be same as the order of parameters which are accepted by the stored procedure. This helped me a great deal in conjuction with all the other things that you guys have mentioned.

Thanks..:)