Solved

VB5 & MSSQL SErver 6.5 Stored Procedure

Posted on 1998-07-18
8
245 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now