Trouble Retrieving Recordset from Stored Procedure

[Penstar]

I am using the following code to pass parameters into stored procedure to
select records. It all seems OK, but I can't seem to get the recordset
returned to the form. I have been trying to get it working for days, with no
success.

As the code is now, it gets to Set rs = cmd.Execute and says Runtime error
2147217900(80040e14) expected query name after execute.

Can someone please help.

Penny


Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm1 As ADODB.Parameter

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command

With cmd
Set .ActiveConnection = cn
.CommandText = testtimesheet
.CommandType = adCmdStoredProc
Set prm1 = .CreateParameter("prm1", adVarChar, adParamInput, 8000)
.Parameters.Append prm1
prm1.Value = "1"
End With

Set rs = cmd.Execute
Set Me.Recordset = rs

End Sub

 

[Cory J. Laidlaw, Beyond01.com]

Hi Penny,

It may not be good programming form, but I usually get my records from a
Stored Procedure as a recordset directly... Such as...

' Open recordset based on a stored procedure. Note I am setting values to
make it read only. You can change the end 2 paramaters to give you dynamic if
you want to modify records.

Dim rs as new adodb.recordset
rs.open "testtimesheet prm1",currentproject.connection,adOpenStatic,
adLockReadOnly


(I tried to adapt your current example)

I basically pass the SQL Command just as you would sending the command via
the SQL Query Analyzer.

I hope this helps! Good Luck!

Cory

 

[Penstar]

Trying your way I get a runtime error - invalid SQL statement; expected
'DELETE', 'INSERT', 'PROCEDURE','SELECT', or 'UPDATE'.

I have since been told how to retrieve the recordset by fixing my current
code.

My problem now is that the returned recordset using one sp is dynamic, but
using another sp (more complex SQL) is not dynamic (using the same VB code,
parameters etc)

 

[Cory J. Laidlaw, Beyond01.com]

Hi Penny,

try this example:

1.Create a new access data project,
2. connect to your SQL server.
3. Choose the 'Pubs' Database.
4. Create a new module. Cut and paste the following code:

================================================

Public Sub Example()

Dim rs As New ADODB.Recordset

With rs
.Open "sp_denpubs 'usa'", CurrentProject.Connection, adOpenStatic,
adLockReadOnly
Do Until rs.EOF
MsgBox rs("pub_id")
rs.MoveNext
Loop
rs.Close
End With

Set rs = Nothing


End Sub

====================================================
* the line 'adLockReadOnly' actually belongs on the above line. This editor
is work wrapping.

As you can see, you can retrieve records without the command object. This
example returns the ID's from the Stored procedure and feeds it the paramater
'usa'.

I hope this helps!! Good Luck!

Cory

 

[Penstar]

Thank you for your suggestions. Unfortunately, I am so busy I have had to
put this issue on the backburner for the moment. Hopefully I will get to it
in the next couple of days and try your suggestions.

I will let you know how I go.

Thanks again
Penny