?
Solved

VB5 & MSSQL SErver 6.5 Stored Procedure

Posted on 1998-07-18
8
Medium Priority
?
305 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
[X]
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
  • 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 300 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

762 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