Stored procedure / recordset problem

[Keith Greeley]

I have managed to run a SQL stored procedure from the following (after setting up ODBC workspace & connection)..

Set rsTemp=conPubs.Openrecordset('nameofmyproc')

it runs ok, but when I then try and run certain methods on this recordset such as:

rsTemp.MoveFirst
rsTemp.MoveLast etc..

I get a run-time error message saying Invalid operation. I have tried opening the recordset with the 'type' parameter also, i.e.

rsTemp=Conpubs.Openrecordset(nameofmyproc',dbOpenDynamic) etc.. and I still get the problem

Any ideas anyone.
Thanks in advance
Keith

The Error you got seems to have either of the following two reasons.
1.The Type of Recordset is specified as Forward only cursor.
2.The Recordset is empty.

[kieran_rush]

i hope you are declaring somewhere the recordset, what I do to open recordsets is like this, I declare and open my connections in diff way but this will help you, also I use ADO
This example is for ADO

Q = "EXEC SP_TEST"
Set rs = Conn.Execute (Q)

This example is for RDO

Q = "EXEC SP_TEST"
Set rs = Conn.Openresultset Q

Both ways work with Stored Procedures
If you need more help please let me know
Regards
RSV

[Keith Greeley]

Thankyou for your reply kieran.

Being a relative novice, I had to do some reading up on the ADO and RDO

terminologies in your code. I have VB5 'professional' edition and had read

that the RDO is only available in the 'enterprise'. So I checked out the

Project/References and added the 'ActiveX Data Objects 2.0' library (is this

what you mean by ADO ?). I tried your code using:

Dim wrkODBC As Workspace
Dim conPubs As Connection
Set wrkODBC = CreateWorkspace("myODBC", _
"admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Connect1" _
, , , "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=SQLDATA_ON_NTSERVER")
Dim rsTemp As Recordset, mQuery as String
mQuery = "EXEC pr_vb_test"
Set rsTemp = conPubs.Execute(mQuery) << Error occurs here

..and the code returned the error "Compile error" on the last line (expected

Function or variable).

Any ideas?

Thanks again



PS. Although I have previously returned a recordset from a Stored procedure

name before, my main problem is that the only type of recordset returned was
a forward-only, no matter what 'type' parameter I used in the Openrecordset

method.

[kieran_rush]

Yes Keith ADO is for ActiveX Data Object, the library you are using is not the latest, but it works fine (the latest Library is 2.1), here is an example about how I connect and open a session, retrieve data from SQL (I'm using SQL 7.0), but the code works in VB 5.0(SP3) or VB 6.0(SP3)

I declare all the global variables in a module called globals and goes something like this

Global Conn as New ADODB.Connection
Global rs as ADODB.Recordset
Global Q as String

Public Sub Connect ()
Dim sconnect as String
On Error GoTo errorlabel
sconnect = "UID=" & frmLogin.txtUserName & ";PWD=" & frmLogin.txtpassword & ";DSN=" & frmLogin.txtDSN & ";"
With Conn
.ConnectionString = sconnect
.Open
End With
frmLogin.Hide
Exit Sub

errorlabel:
MsgBox "Wrong User or Passwrod, please try again", vbCritical, "System Error"
Exit Sub

End Sub

Then when I use the Login Form in the event click of the button Ok I call the Sub with just a simple Call Connect, I open a permanent connection with the server, to close this connection I use the event unload of the MDIForm to close the connection with the a simple Conn.Close, Ok, I hope this is still clear for you.Remember I'm using ADO.

Now to get a Recordset using a SP, first the SP must have the instructions, for example
CREATE PROCEDURE SP_EXAMPLE AS
SELECT * FROM V_VENDOR

Then the instruction from VB must be like this one

Q = "EXEC SP_EXAMPLE"
Set rs = Conn.Execute (Q)

With this the recordset is open, after that you must do the instructions to fill your listview, combobox, or any control you are using.
You can declare your variables using a Module with the global, or in the procedure with the Dim, try the code I'm sending you, It works, alse the eror in your code is that you are trying to use ADO without declaring the ADO Connection, the ConPubs variable must be like
Dim ConPubs as New ADODB.Connection
you are using
Dim ConPubs as Connection
this is wrong with ADO, if you need further help please write me
Regards
RSV