Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB5 & MSSQL SErver 6.5 Stored Procedure

Posted on 1998-07-18
8
Medium Priority
?
315 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

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!

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

609 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