Solved

VB5 & MSSQL SErver 6.5 Stored Procedure

Posted on 1998-07-18
8
289 Views
Last Modified: 2010-05-03
If I have a SP resided in the SQL server, how can I kick start that SP and return the result via my VB5 application? Or how can I pass a SP to the SQL server and return the result from the SQL server?

Thanks.
0
Comment
Question by:ppmb
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 2

Expert Comment

by:Veroland
ID: 1466130
if u r using rdo you can say :

Resultset = dbconn.openresultset("Exec SP")
0
 

Expert Comment

by:vinoopauls
ID: 1466131
What are you using?
RDO or SQLDMO
0
 

Accepted Solution

by:
mshowell earned 100 total points
ID: 1466132
Here is a code sample of calling a stored procedure that requires parameters to be passed to it using RDO:

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'Save the PickTicket object that is passed as parameter
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Public Function SavePickTicket(objPickTicket As ICCIShippingMaintenance.PickTicket) _
                                    As Boolean
    Dim qd As New rdoQuery
    Dim lngShippingId As Long
    Dim i As Integer
    Dim objShipping As ICCIShippingMaintenance.Shipping
   
    On Error GoTo ErrHandler
   
    SavePickTicket = True
   
    db.BeginTrans
    Set qd.ActiveConnection = db
    qd.SQL = "{Call sp_SaveShipping(?,?,?,?,?,?,?,?,?,?) }"
    ' As per the sp_SaveShipping save procedure call,
    '      the interface is as follow:
   
    '    @pickticketno numeric(10),
    '    @Boxno tinyint,
    '    @trackingno varchar(30),
    '    @weight float(8),
    '    @ShipAmt money,
    '    @transtime smallint,
    '    @shipdate datetime,
    '    @TotAmt money,
    '    @ShipMethod as varchar(50),
    '    @shipoptions as varchar(3) as

    For i = 0 To 9
        qd(i).Direction = rdParamInput
    Next
   
   
    For i = 1 To objPickTicket.Shipping.Count
        Set objShipping = objPickTicket.Shipping(i)
        With objShipping
            qd(0) = objPickTicket.PickTicketNo
            qd(1) = .BoxNo
            qd(2) = .TrackingNo
            qd(3) = .Weight
            qd(4) = .ShippingAmount
            qd(5) = .TransitTime
            qd(6) = .ShippingDate
            qd(7) = .TotalAmount
            qd(8) = objPickTicket.ShippingMethod.Shipmethod
            qd(9) = .ShipOptions
        End With
        qd.Execute
    Next
   
    qd.Close
    db.CommitTrans
   
    Exit Function
   
ErrHandler:
    db.RollbackTrans
    SavePickTicket = False
    Err.Raise Number:=Err.Number, Description:="Error While Saving Shipping Pick Ticket" & vbCrLf & Err.Description
End Function

0
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!

 

Author Comment

by:ppmb
ID: 1466133
I have been using DAO throughout my app. Is that possible to do it with DAO + Stored Procedure??? If so, how? Or is RDO the only way to do it?
0
 

Expert Comment

by:mshowell
ID: 1466134
You can do it with DAO by doing the following in your code:
Create a querydef object
set the .connect property to the ODBC connect string of the sql server ODBC DataSet (this lets DAO know that you want the query processed as a passthrough query)

Set the .returnsdata property to true (check the spelling on this one)  This lets DAO know that you will be getting something back from Sql Server

Set the .sql property to "Execute sp_myproc???(" & parm1 & ", " & parm2 & ")"

Then just run the execute method of the query and look at the resultant recordset for your answer
0
 

Expert Comment

by:vinoopauls
ID: 1466135
 If you are using SQL Server it is advisable to go for SQL DMO.  This is specially for SQL
Server and is considerably faster than ODBC and RDO.

The following will be the syntax for executing an sp

 set rs = db.executewithresults("spname" ' " & strparameter & ' " & intparameter)
rs being resultset
db being database
0
 

Author Comment

by:ppmb
ID: 1466136
To: mshowell,

With the example you have given in the earlier time, you have the following lines in your code:
For i = 0 To 9
            qd(i).Direction = rdParamInput
        Next
         
         
        For i = 1 To objPickTicket.Shipping.Count
            Set objShipping = objPickTicket.Shipping(i)
            With objShipping
                qd(0) = objPickTicket.PickTicketNo
                qd(1) = .BoxNo
                qd(2) = .TrackingNo
                qd(3) = .Weight
                qd(4) = .ShippingAmount
                qd(5) = .TransitTime
                qd(6) = .ShippingDate
                qd(7) = .TotalAmount
                qd(8) = objPickTicket.ShippingMethod.Shipmethod
                qd(9) = .ShipOptions
            End With
            qd.Execute
        Next

Are these lines the correct and needed code to pass parameters onto the stored proc???
Thank you!
0
 

Expert Comment

by:mshowell
ID: 1466137
This code was cut and pasted here from a working, debugged system, so I believe it is one way that works.  There may be others
0

Featured Post

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!

Question has a verified solution.

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

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

685 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