Command object and running stored procedures.

[beaniebear]

Can I use a command object to return a recordset? If so, could you please give me a small example?

Also I have a stored procedure that runs one of three querries depending on the parameters entered. I'm running the stored procedure using the connection object. The SP returns the recordset but even if I set the cursor and lock properties of the recordset correctly I cannot use .movelast etc as it brings up an error that the rowset cannot be restarted. If I put the queries into seperate SP's then I am able to move through the recordset using .movelast etc. Why is there a difference?

 

[Tarwn]

An example of using the command object:

Dim strConnect
Dim objCommand, sql_count, rs_count
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
			 "Data Source=C:\data\database.mdb;" & _
			 "Persist Security Info=False"


sql_count = "SELECT DISTINCTROW TutorialList.r_level, Count(*) AS total FROM TutorialList GROUP BY TutorialList.r_level"

Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_count 
objCommand.CommandType=adCmdText
Set rs_count = objCommand.Execute
Set objCommand = Nothing

Hope that helps for the Command object stuff. I wrote a FAQ a while back using only this format to connect and query to the DB, it's a heavily commented piece of code if your more interested in what each line does (see faq333-2120).

As to the stored procedure question, I don't think the above method will give you any beter results. I am not to experienced with stored procedures, I prefer sending all of my SQL in queries. (Grr, me cave man, me not change my ways)
-Tarwn "Customer Support is an art not a service" - marketing saying
"So are most other forms of torture" - programmers response
(The Wiz Biz - Rick Cook)

 

[beaniebear]

Thanks - think I'm having a mental block this afternoon.

I gave this method a try but I'm still having trouble being able to move through the recordset.

Stored procedures are not so bad and are great when you need to use the same query over and over, but when you can't get them to work....aaaaahhh!!!

If anyone has any ideas.....