Solved

How to call SQL stored procedure from VB 6.0 code?

Posted on 2006-06-26
11
16,976 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 142

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
 
LVL 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 142

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 142

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now