ppmb
asked on
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.
Thanks.
What are you using?
RDO or SQLDMO
RDO or SQLDMO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
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
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("spn ame" ' " & strparameter & ' " & intparameter)
rs being resultset
db being database
Server and is considerably faster than ODBC and RDO.
The following will be the syntax for executing an sp
set rs = db.executewithresults("spn
rs being resultset
db being database
ASKER
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.Cou nt
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.ShippingMeth od.Shipmet hod
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!
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.Cou
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.ShippingMeth
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!
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
Resultset = dbconn.openresultset("Exec