Stored Procedures using DAO

Hi,

I'm trying to execute a stored procedure using DAO 3.6, but it is failing to execute.

I've used connect method to connect to the database and then created a querydef. Using the parameters property of the querydef, I've set all the parameters and direction, but when it comes to the execute method, it just fails with the error 3146.

The code looks like:

cTrackerSP = "{ call CST_ACM_CONV.GET_DATA (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }"

Set qdfTracker = dbTracker.CreateQueryDef("", cTrackerSP)
With qdfTracker

.Parameters(0).Direction = dbParamInputOutput

For i = 1 To 19
.Parameters(i).Direction = dbParamInput
Next

.Parameters(1) = "123"
.Parameters(2) = "Particulier"
.Parameters(3) = "0626082650"
.Parameters(4) = "Smith"
.Parameters(5) = ""
.Parameters(6) = "John"
.Parameters(7) = "0202002181"
.Parameters(8) = ""
.Parameters(9) = "Street Name 13"
.Parameters(10) = ""
.Parameters(11) = "City"
.Parameters(12) = "CA"
.Parameters(13) = "27718"
.Parameters(14) = "US"
.Parameters(15) = ""
.Parameters(16) = ""
.Parameters(17) = ""
.Parameters(18) = ""
.Parameters(19) = ""

.Execute

If .Parameters(0).Value <> 0 Then
'Success!
'Do something here
End If
End With

In the above code, when I use the word call to describe the stored procedure name, it fails, but goes further down when the word call is removed.

Can anyone suggest a robust method to call a stored procedure using parameters?

Regard,
Subrahmanyam

[Edited by Subrahmanyam Vadali on 05-25-2000 at 04:08 AM]

[MartinLiss]

I don't have any experience using stored procedures and this article doesn't refer to DAO, but maybe it will help anyhow.

Performing a Parameter-Driven Stored Procedure


RDO
Since many client/server applications depend heavily on stored procedures (SP), a data access interface should be able to perform them quickly and efficiently. Stored procedures, however, can be tricky to handle. In some cases, stored procedures require management of OUTPUT and return status values and other, more conventional arguments. In addition, an SP can return several complex resultsets, including PRINT or RAISERROR statement return values. In some cases, it's better to create complex SPs, while in others it’s better and easier to keep SPs simpler and more modular and use Visual Basic to tie them together.

The code below shows the RDO approach to these problems. First, it performs a simple parameter-based SP and shows the results in the grid. Some accommodations are made to the subsequent ADO design, and these are noted. It uses the same connections established in the earlier examples.

Note that the code requires us to include a correct ODBC "Call" statement. Again, this is not necessary in the UserConnection designer, but it’s essential in the RDO code-based approach. Here, you use the stand-alone rdoQuery object and assign the already open Connection to it. The rdoQuery object can then be used in subsequent calls to handle a parameter query.

Note also that the code does not attempt to refer to the return status argument. This value is not available until the resultset is fully populated; only then does SQL Server return this value.

Private Sub RunSPButton_Click()
Dim Qy As New rdoQuery
Dim rs As rdoResultset
sql = "{? = Call AuthorByYearBorn (?,?)}"
Set Qy.ActiveConnection = cn
Qy.sql = sql
Qy.rdoParameters(0).Direction = rdParamReturnValue
Qy(1) = "1947"
Qy(2) = "1948"
Set rs = Qy.OpenResultset()
rdoGrid1.ShowData rs
ShowRows = rs.RowCount
rs.Close
End Sub

ADO
ADO has a lot of flexibility when it comes to performing stored procedures. But this flexibility comes at the cost of more code. As with the previous example ("Performing a Parameter Query"), it's possible to build your own ADODB Parameters collection. In this case, you’re performing a simple two-argument SP, "AuthorByYearBorn", that returns a small resultset. Note that although ADO allows you to create your own parameters, it's not necessary to do so.

Note ADO collections are 0-based to match DAO. RDO collections are 1-based.

Private Sub RunSPButton_Click()
Dim Qy As New ADODB.Command
Dim Parm As New ADODB.Parameter
Dim Parm2 As New ADODB.Parameter

Set Qy.ActiveConnection = cn
Qy(0)="1947"
Qy(1)="1948"
Qy.CommandType = adCmdStoredProc
Qy.CommandText = "AuthorByYearBorn"

Set rs = Qy.Execute(ShowRows)
ADOGrid1.ShowData rs
End Sub