SQL stored procedure returning unusual ado recordset
Author |
Message |
Jim Bancrof #1 / 9
|
 SQL stored procedure returning unusual ado recordset
I'm running a SQL Stored procedure which "maufactures" a return value, which I then put into a recordset. Problem is, when I do this the recordset has unusual PageCount and RecordCount values; -1, to be exact. This is hurting me later in my VB 6 program. The stored procedure (SQL 2000) reads like this: --------------------------------------------------------------------
/*is this field editable?*/
GO ---------------------------------------------------------------------- I call the stored procedure and store the data in a recordset like so: '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim objRecordset As ADODB.Recordset Set objRecordset = New ADODB.Recordset objRecordset.Open _ "EXEC www_get_fld_restrictable " & objInbound("lgItemID").FieldValue, _ m_Connect, adOpenStatic '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' When I do this, "objRecordset" has the unusual properties I mentioned earlier. Obviously, because I'm returning manufactured data, and not actual, "queried" values from a table, the recordset has some trouble digesting it. Is there anything I can do in the stored procedure to out accurate values into the recordset's RecordCount and other properties? Thanks for the advice.
|
Sun, 04 Sep 2005 02:28:25 GMT |
|
 |
Aaron Bertrand [MVP #2 / 9
|
 SQL stored procedure returning unusual ado recordset
Quote: > Problem is, when I do this the recordset has unusual PageCount and > RecordCount values; -1, to be exact.
http://www.aspfaq.com/2193 -- Aaron Bertrand, SQL Server MVP http://www.aspfaq.com/ Please reply in the newsgroups, but if you absolutely must reply via e-mail, please take out the TRASH.
|
Sun, 04 Sep 2005 03:08:18 GMT |
|
 |
Jim Bancrof #3 / 9
|
 SQL stored procedure returning unusual ado recordset
Thanks for the tip-- even though the article mentions forward-only cursors, I assume the same problem is present with static cursors, such as I'm using in the code snippet?
Quote: > > Problem is, when I do this the recordset has unusual PageCount and > > RecordCount values; -1, to be exact. > http://www.aspfaq.com/2193 > -- > Aaron Bertrand, SQL Server MVP > http://www.aspfaq.com/ > Please reply in the newsgroups, but if you absolutely > must reply via e-mail, please take out the TRASH.
|
Sun, 04 Sep 2005 05:55:18 GMT |
|
 |
Luis Alons #4 / 9
|
 SQL stored procedure returning unusual ado recordset
Hi, Jim. HTH, Luis Alonso Private Sub Command1_Click() Dim cnn1 As ADODB.Connection Dim rsTemp As ADODB.Recordset Dim stSelectSQL As String Set cnn1 = New ADODB.Connection cnn1.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pubs;Data Source=." Set rsTemp = New ADODB.Recordset rsTemp.CursorLocation = adUseClient stSelectSQL = "SELECT * FROM Sales" rsTemp.Open stSelectSQL, cnn1, adOpenDynamic, adLockOptimistic MsgBox "With adUseClient + adOpenDynamic, RecordCount = " & CStr(rsTemp.RecordCount) rsTemp.Close rsTemp.CursorLocation = adUseClient stSelectSQL = "SELECT * FROM Sales" rsTemp.Open stSelectSQL, cnn1, adOpenStatic, adLockOptimistic MsgBox "With adUseClient + adOpenStatic, RecordCount = " & CStr(rsTemp.RecordCount) rsTemp.Close rsTemp.CursorLocation = adUseServer stSelectSQL = "SELECT * FROM Sales" rsTemp.Open stSelectSQL, cnn1, adOpenDynamic, adLockOptimistic MsgBox "With adUseServer + adOpenDynamic, RecordCount = " & CStr(rsTemp.RecordCount) Set rsTemp = Nothing Set cnn1 = Nothing End Sub "Jim Bancroft" <bobbygambles_at_nospam.msn.com> escribi en el mensaje
Quote: > I'm running a SQL Stored procedure which "maufactures" a return value, which > I then put into a recordset. > Problem is, when I do this the recordset has unusual PageCount and > RecordCount values; -1, to be exact. This is hurting me later in my VB 6 > program. > The stored procedure (SQL 2000) reads like this: > --------------------------------------------------------------------
yEditRights=1) > /*is this field editable?*/
> GO > ---------------------------------------------------------------------- > I call the stored procedure and store the data in a recordset like so:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Quote: > ''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > Dim objRecordset As ADODB.Recordset > Set objRecordset = New ADODB.Recordset > objRecordset.Open _ > "EXEC www_get_fld_restrictable " & > objInbound("lgItemID").FieldValue, _ > m_Connect, adOpenStatic
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Quote: > ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > When I do this, "objRecordset" has the unusual properties I mentioned > earlier. Obviously, because I'm returning manufactured data, and not > actual, "queried" values from a table, the recordset has some trouble > digesting it. > Is there anything I can do in the stored procedure to out accurate values > into the recordset's RecordCount and other properties? Thanks for the > advice.
|
Sun, 04 Sep 2005 07:24:02 GMT |
|
 |
Aaron Bertrand [MVP #5 / 9
|
 SQL stored procedure returning unusual ado recordset
Did you try using , 1, 1 instead of , adOpenStatic -- Aaron Bertrand, SQL Server MVP http://www.aspfaq.com/ Please reply in the newsgroups, but if you absolutely must reply via e-mail, please take out the TRASH.
Quote: > Thanks for the tip-- even though the article mentions forward-only cursors, > I assume the same problem is present with static cursors, such as I'm using > in the code snippet?
> > > Problem is, when I do this the recordset has unusual PageCount and > > > RecordCount values; -1, to be exact. > > http://www.aspfaq.com/2193 > > -- > > Aaron Bertrand, SQL Server MVP > > http://www.aspfaq.com/ > > Please reply in the newsgroups, but if you absolutely > > must reply via e-mail, please take out the TRASH.
|
Sun, 04 Sep 2005 22:26:04 GMT |
|
 |
Jim Bancrof #6 / 9
|
 SQL stored procedure returning unusual ado recordset
Quote: > Did you try using > , 1, 1 > instead of > , adOpenStatic
Yes, I did. Unfortunately, I still got a RecordCount value of -1. I think the problem lies in my stored procedure. The fact that I'm manufacturing my return value, bPermission, instead of pulling it from a table via a query is probably hurting me.
|
Mon, 05 Sep 2005 00:24:22 GMT |
|
 |
Aaron Bertrand [MVP #7 / 9
|
 SQL stored procedure returning unusual ado recordset
That shouldn't be the problem. However, why do you need a recordcount? The definition of your stored procedure makes it hard to imagine this will ever be anything but exactly 1. Just get the value and move on. -- Aaron Bertrand, SQL Server MVP http://www.aspfaq.com/ Please reply in the newsgroups, but if you absolutely must reply via e-mail, please take out the TRASH.
Quote:
> > Did you try using > > , 1, 1 > > instead of > > , adOpenStatic > Yes, I did. Unfortunately, I still got a RecordCount value of -1. > I think the problem lies in my stored procedure. The fact that I'm > manufacturing my return value, bPermission, instead of pulling it from a > table via a query is probably hurting me.
|
Mon, 05 Sep 2005 00:29:25 GMT |
|
 |
Jim Bancrof #8 / 9
|
 SQL stored procedure returning unusual ado recordset
Quote: > That shouldn't be the problem. However, why do you need a recordcount? The > definition of your stored procedure makes it hard to imagine this will ever > be anything but exactly 1. Just get the value and move on.
I appreciate your help, Aaron. The reason I need the record count is because I have a component which encapsulates an ADO recordset and generates XML from its contents. If the recordset's count is -1, my component won't run through the recordset properly. I can code around this problem, but I'd like to understand why I'm getting a -1, and try to prevent it if possible. Things work fine when my recordset opens stored procedures which run a query against a database. However, when I run a stored proc like the one outlined in my original post, the recordset count isn't accurate, and it's strange why that would be, since I am using a static cursor.
|
Mon, 05 Sep 2005 01:34:26 GMT |
|
 |
Aaron Bertrand [MVP #9 / 9
|
 SQL stored procedure returning unusual ado recordset
Why do you need to rely on the recordcount property? Define your own local variable, set it to 1, and reference that. I'd like to understand why you're getting -1 too, but I can't reproduce your scenario, so I'm doing my best to help you overcome this. I'm just having a hard time getting past "I *HAVE* to use the recordcount property." It makes little sense when the answer is *ALWAYS* going to be the same. -- Aaron Bertrand, SQL Server MVP http://www.aspfaq.com/ Please reply in the newsgroups, but if you absolutely must reply via e-mail, please take out the TRASH.
Quote:
> > That shouldn't be the problem. However, why do you need a recordcount? > The > > definition of your stored procedure makes it hard to imagine this will > ever > > be anything but exactly 1. Just get the value and move on. > I appreciate your help, Aaron. > The reason I need the record count is because I have a component which > encapsulates an ADO recordset and generates XML from its contents. If the > recordset's count is -1, my component won't run through the recordset > properly. I can code around this problem, but I'd like to understand why > I'm getting a -1, and try to prevent it if possible. > Things work fine when my recordset opens stored procedures which run a query > against a database. However, when I run a stored proc like the one outlined > in my original post, the recordset count isn't accurate, and it's strange > why that would be, since I am using a static cursor.
|
Mon, 05 Sep 2005 02:34:15 GMT |
|
|
|