Solved

How to call SQL stored procedure from VB 6.0 code?

Posted on 2006-06-26
11
17,107 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:pmud
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16987282
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) )
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 16987292
>' 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 & ")

First off, you need to remove the double-quotes around your strStudyDate, otherwise instead of passing the value inside strStudyDate you pass the variable name.
cmd.Parameters.Append cmd.CreateParameter("@strStudy_Date", adVarChar, adParamInput, 50, strStudyDate)

You'll need to do that for all your cmd.Parameters.Append lines.

Also, you are also missing the varchar length, where I have 50 above.
0
 

Author Comment

by:pmud
ID: 16987552
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16987648
> cmd.Execute rsCheck, , adExecuteNoRecords
should be:
 cmd.Execute rsCheck
as you actually WANT the records back
0
 

Author Comment

by:pmud
ID: 16988130
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
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 16989363
I would change your code as follows :

    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))


Change adDBTime (134) to adDBDateTime (135)
Add correct lengths to all parameters.
Fix the Field Value.

Also, do not use:
rsRecCount = rsCheck.RecordCount

Also, do not use the RecordCount property as it will always be -1 for a firehose (Forward-Only, Read-Only) cursor.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 275 total points
ID: 16989761
>   rsCheck = cmd.Execute
in fact, that one should work better

even better:
   
set rsCheck = new adodb.recodset
set rsCheck.ActiveConnection = cn
rsCheck.open  cmd
0
 

Author Comment

by:pmud
ID: 16992631
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16993004
>>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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 275 total points
ID: 16993227
you should start error handling:


on error goto ErrHandler:
 ...

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

Exit Function

ErrHandler:
  debug.print Err.description

End Function

and post here what the error is.
0
 

Author Comment

by:pmud
ID: 16993878
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..:)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question