Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to call SQL stored procedure from VB 6.0 code?

Posted on 2006-06-26
11
17,071 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
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…
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…

791 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