ADO Recordset and Stored Procedure

[jkb17]

I have just inherited a pre-designed ASP web project. AS it exists right now, there is a lot of embedded SQL and using the adRS.MoveFirst and adRS.MoveLast methods work fine.

However, the publisher of the site has requested some more advanced operations that are best handled via a parameterized stored procedure (MS SQL Server 7.0 is the database). The stored procedure accepts 2 parameters (a varchar and money data type).

I have tried every possible scenario for opening the recordset:

set adRS = adcmd.execute
adrs.open adcmd,,adOpenKeyset

But whenever an "adrs.MoveFirst" is encountered, an error is created. The error message is "Rowset position cannot be restarted." The Recordset object is display "foward only" as its cursor type.

And to answer any questions now, yes I have tried setting every possible scenario with the adConn, adCmd and adRS object (including both cursortype and cursorlocation). All I ever get is "fowardonly" cursor.

How do I open an ADO Recordset object using a parameterized stored procedure so that I will have bi-directional scrolling within the recordset object?

thanks to anyone who can explain this to me!

bye
Jim

p.s. I know about adRS.getRows but I was hoping to not re-invent the wheel here (i.e. re-write all the loop structures already designed within the page) and utilize functionality that should be present within the ADO Object Model (I never had this problem with RDO).

thanks again.

 

[DougP]

Well I use SQL server on my puny WEB site and have had great luck with this. They are not Stored procedures but it is quick.

Set Conn = server.CreateObject("ADODB.Connection&quot
Conn.Open "driver=SQL Server;server=yourserver.com;uid=youruser;pwd=yourpass;database=yourdatabase;"
Set RS2 = Conn.Execute("SELECT * From Customers Where [CUSTOMER] ='" & Customer & "'&quot
Response.Write "name: " & RS2("CUSTNAME&quot



DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[jkb17]

Thanks, DougP, but that is what I mean by "embedded SQL" which is what I want to avoid. Also, if my sql statements were that basic or easy (and most tend to be that easy - its just for the more complicated operations I need procs) then I wouldn't have a problem. yes, executing a statement like that works fine and in fact the recordset will allow bi-directional navigation (if specified).

thanks for the help anyway, DougP.

 

[DougP]

Ok maybe this will help
I found it here on tek-tips some where

"I am trying to pull a DTS package through a vb application. I am using a loadfromSQLServer command."

objPackage.LoadFromSQLServer "Michelle2", , , , , , , "DataWarehouseTest"
DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[markosimic]

When define an implicit recordset object by
set adRS = adcmd.execute
server will always create forward/read-only cursor

To define dyamic cursor you need to create an so-called explicit recordset object

set rs = Server.CreateObject("adodb.recordset&quot
rs.cursortype = 3 (dynamic cursor)
rs.ActiveConnection = adcmd (<-previously created and opened connection to sql server using connection object, it also can be done using only recordset object)
rs.open(&quot;stored_procedure parmater1,parmater2&quot

then you can go up and down, up and down with cursor however you want (rs.MoveFirst,rs.MoveLast,rs.MoveNext etc..)

Good Luck,
Marko Simic
Wb Web Managing