Stored Procedures - Recordset
-
Hi there, In the past I used SQL commands to retrieve recordsets from my DB. Now I moved those SQL commands to stored procedures in my DB to retrieve the recordsets. The main issue I have is that I cannot set the absolute position etc on that recordset from my stored procedure. Here is what I did in the past:
Dim rsRecord As Recordset Set rsRecord = New Recordset rsRecord.Open “SELECT * FROM Customers”, cnDatabase, adOpenKeysetIf I open the recordset like the sample above I can get the rsRecordset.RecordCount, but if I use a stored procedure to fetch the same data into a recordset the rsRecordset.RecordCount property returns -1Dim cmCommand As ADODB.Command Set cmCommand = New ADODB.Command Set cmCommand.ActiveConnection = cnDatabase cmCommand.CommandType = adCmdStoredProc cmCommand.CommandText = " [dbo].[FetchCustomers]" Set rsRecord = cmCommand.ExecutersRecordset.RecordCount = -1 How can I use a stored procedure to scroll through a recordset? :confused: Thanks, WerriesA programmer's life is good... or is it?? Ek dink nie so nie!
-
Hi there, In the past I used SQL commands to retrieve recordsets from my DB. Now I moved those SQL commands to stored procedures in my DB to retrieve the recordsets. The main issue I have is that I cannot set the absolute position etc on that recordset from my stored procedure. Here is what I did in the past:
Dim rsRecord As Recordset Set rsRecord = New Recordset rsRecord.Open “SELECT * FROM Customers”, cnDatabase, adOpenKeysetIf I open the recordset like the sample above I can get the rsRecordset.RecordCount, but if I use a stored procedure to fetch the same data into a recordset the rsRecordset.RecordCount property returns -1Dim cmCommand As ADODB.Command Set cmCommand = New ADODB.Command Set cmCommand.ActiveConnection = cnDatabase cmCommand.CommandType = adCmdStoredProc cmCommand.CommandText = " [dbo].[FetchCustomers]" Set rsRecord = cmCommand.ExecutersRecordset.RecordCount = -1 How can I use a stored procedure to scroll through a recordset? :confused: Thanks, WerriesA programmer's life is good... or is it?? Ek dink nie so nie!
Hi Werries Your problem lays with the way that you are opening your recordset, rather than with the stored procedure. ADO is using the "adOpenForwardOnly" cursor-type in your second example. Try:
rsRecord.Open "[dbo].[FetchCustomers]", cnDatabase, adOpenKeyset, adLockReadOnly, adCmdStoredProcedureThere are several other overloads for the
Openmethod, including one that allows you to open the recordset from a command object. Regards AndyIf you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
-
Hi Werries Your problem lays with the way that you are opening your recordset, rather than with the stored procedure. ADO is using the "adOpenForwardOnly" cursor-type in your second example. Try:
rsRecord.Open "[dbo].[FetchCustomers]", cnDatabase, adOpenKeyset, adLockReadOnly, adCmdStoredProcedureThere are several other overloads for the
Openmethod, including one that allows you to open the recordset from a command object. Regards AndyIf you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
Hi Andy, Maybe I must rephrase my problem and question. I wànt to use a stored procedure to fetch the information, but the way I retrieve the recordsets does not allow me to scroll the records in the recordset. I need to be able to set the absolute position of the recordset and then fetch records from there. If I use a SQL command to retrieve the recordset like in my first attempt, I can set the absolute position of the recordset, but if I retrieve a recordset through a stored procedure the recordset does not allow me to set the absolute position on it. It raises an error 3251, ‘Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.’ If I can change the cursortype when using a stored procedute that supports bookmarks it will be great. I hope I have explained myself better this time. Thanks for your previous reply on this matter. Any other suggestions? :~ Thanks, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
-
Hi Andy, Maybe I must rephrase my problem and question. I wànt to use a stored procedure to fetch the information, but the way I retrieve the recordsets does not allow me to scroll the records in the recordset. I need to be able to set the absolute position of the recordset and then fetch records from there. If I use a SQL command to retrieve the recordset like in my first attempt, I can set the absolute position of the recordset, but if I retrieve a recordset through a stored procedure the recordset does not allow me to set the absolute position on it. It raises an error 3251, ‘Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.’ If I can change the cursortype when using a stored procedute that supports bookmarks it will be great. I hope I have explained myself better this time. Thanks for your previous reply on this matter. Any other suggestions? :~ Thanks, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
I went back and modified my original reply to invoke your stored procedure. Your problem was caused by ADO using its default forward-only cursor type, rather than the keyset cursor type that you were originally using with your SQL select.
-
I went back and modified my original reply to invoke your stored procedure. Your problem was caused by ADO using its default forward-only cursor type, rather than the keyset cursor type that you were originally using with your SQL select.
Hi Andy, I did see your modification on your first reply and applied it to my code, but it seems to me I still have a forward-only cursor type. When I'm trying to set the absolute position of the recordset the same error occurs 'Current Recordset does not support bookmarks....' I still don’t have a solution for my problem, but I'm thinking of trying a whole different way of implementing what I actually want to do. But if you've got a solution of why the recordset still have a forward-only cursor type, I’ll be glad to hear it from you. I appreciate all your help. Have a nice weekend, Regards, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
-
Hi Andy, I did see your modification on your first reply and applied it to my code, but it seems to me I still have a forward-only cursor type. When I'm trying to set the absolute position of the recordset the same error occurs 'Current Recordset does not support bookmarks....' I still don’t have a solution for my problem, but I'm thinking of trying a whole different way of implementing what I actually want to do. But if you've got a solution of why the recordset still have a forward-only cursor type, I’ll be glad to hear it from you. I appreciate all your help. Have a nice weekend, Regards, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
Hi Werries If ADO doesn't think that a requested cursor-type is possible then it silently changes to another type. I don't normally use bookmarks - so its not something that I have a problem with. I normally use GetRows() to grab the recordset's contents into a 2-dimensional array. This allows me to close the recordset and grab any output parameters. Have a good weekend. Regards Andy PS: Have you set "Set nocount on" in the stored procedure?
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".