VB5 & MSSQL SErver 6.5 Stored Procedure

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.
ppmbAsked:
Who is Participating?
 
mshowellConnect With a Mentor Commented:
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
 
VerolandCommented:
if u r using rdo you can say :

Resultset = dbconn.openresultset("Exec SP")
0
 
vinoopaulsCommented:
What are you using?
RDO or SQLDMO
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ppmbAuthor Commented:
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
 
mshowellCommented:
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
 
vinoopaulsCommented:
 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
 
ppmbAuthor Commented:
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
 
mshowellCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.