SQL Server Performance Forum – Threads Archive
ADO Recordset, Cursor And Stored Procedures
HiI have a problem changing the cursor type for the ADO Recordset when i am querying SQL SERVER 2000.
when i run a simple query, like "select * from dsa" and on the recordset i change the cursor type to static (not only the move forward) i can see the property "recordcount" or i can moveback in the recordset.
but, when i use the exact same query, but i do it in a stored procedure (the recordset opens the Stored procedue), the cursor is only moveforward (althought i changed [email protected])
because of it i cant use moveback and recordcount.
what can i do? i have to use both the moveback and also Stored Procedures..
Are you executing the SQL statement with the ADO connection object, or the command object? if the command object, which command type are you using?
As mentioned, how are you executing the stored procedure? The easiest way to have control over your recordset and SP is to use the command object.<br /><br />An example of an ASP function:<br /><pre>—————————<br />Function GetAuthor(authorID)<br />Dim rs, cmd<br /><br />’Instantiate objects<br />Set rs = Server.CreateObject("adodb.Recordset")<br />Set cmd = Server.CreateObject("adodb.Command")<br /><br />’Open connection <br />cmd.ActiveConnection = GetConnectionString()<br /> <br />’Specify SP<br />cmd.CommandText = "dbo.GetAuthorByAuthorID"<br />cmd.CommandType = adCmdStoredProc<br /><br />’Specify parameters<br />cmd.Parameters.Append cmd.CreateParameter("@authorID", adInteger, adParamInput, , authorID)<br /> <br />’Execute the query with clientside cursor<br />rs.CursorLocation = adUseClient<br />rs.Open cmd, , adOpenStatic, adLockReadOnly<br /><br />’Disconnect the recordset and clean up<br />Set cmd.ActiveConnection = Nothing<br />Set cmd = Nothing<br /><br />’Return the recordset<br />Set GetAuthor = rs<br />End Function<br /><br />Function GetConnectionString() <br /> GetConnectionString = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=MyPass;App=MyApp"<br />End Function <br />—————————</pre><br />/Argyle
i am opening it with the connection. for example, set objConn = new adodb.connection
set rTemp = new adodb.recordset
….
rTemp.Open "sp_test",objConn,3 where 3 is the cursor type. do you the problem? cause if i use "select * from.." instead of the "sp_test" it does work!
Strange. I’ve tried your exact syntax and I get a static cursor in both scenarios. I have MDAC 2.6 on the web server, connecting to a SQL 2000 SP3 /Argyle
i have the same configuration, just MDAC2.7 anyway, try to use local Variables in the stored procedure.
i think i read thats the prob.. do you get the error?
do you have any idea how to overcome [email protected]
Can’t see how local variables in the SP could affect the cursor type. I know SELECT TOP statments can affect your cursor type but only if you have a locking type different from read only. Could you post an example of your SP code? To overcome it try and execute the SP the way I did in a previous post. /Argyle
it does affect. i read an article about it on the MSDN…
Can you please post the link to the article. Would be interesting to read. /Argyle
]]>