topic Re: How can this be true??!? in VBA Forum

How can this be true??!?

Anonymous — Thu, 30 Sep 2004 17:49:15 GMT

Simply put, I'm opening a database, searching through a table and then showing a message box with 2 numbers, one of which is the RecordCount of the RecordSet, but I'm being told by the computer that I have -1 for a RecordCount. I've using ADO... Here's the code... The first sub opens the database The second sub closes the database The third sub tells me I have -1 counts '================================================= Public Cnxn As ADODB.Connection Public strCnxn As String Public rstLayers As ADODB.Recordset Public Const AppName = "VBA::Set Layer" '================================================= Public Sub OpenDatabase() On Error GoTo ErrMsg ' Open connection strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=S:\CADDStds\Menus\Data\Layers.mdb" Set Cnxn = New ADODB.Connection Cnxn.Open strCnxn Exit Sub ErrMsg: MsgBox "An error has occurred in modMain.OpenDatabase" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical + vbOKOnly, AppName End Sub'================================================= Public Sub CloseDatabase() On Error Resume Next rstLayers.Close On Error Resume Next Cnxn.Close End Sub '================================================= Sub CountCurrent() OpenDatabase Dim rsSearch As Recordset Dim SQL As String Set rsSearch = New ADODB.Recordset SQL = "SELECT * FROM Layers ORDER BY Layer_Name" rsSearch.Open SQL, Cnxn MsgBox rsSearch.RecordCount 'shows that I have -1 records??!? Dim i As Integer i = 0 Do Until rsSearch.EOF If rsSearch!current = True Then i = i + 1 rsSearch.MoveNext Loop MsgBox "Of the " & rsSearch.RecordCount & " files in the Layers table, " & i & " are currently being used." CloseDatabase End Sub '================================================= -- I support two teams: the Red Sox and whoever beats the Yankees.

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 17:52:01 GMT

"Matt W" wrote in message news:415c4741$1_1@newsprd01... > Simply put, I'm opening a database, searching through a table and then > showing a message box with 2 numbers, one of which is the RecordCount of the > RecordSet, but I'm being told by the computer that I have -1 for a > RecordCount. > I've using ADO... The RecordCount property is far from that straight forward. I think there is a good explanation at www.aspfaq.com , go to the Database listing. (If I could give a coherent, detailed answer, I would...........I'll have to leave it to the reference above and/or other database wizards here.)

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 18:03:56 GMT

Matt W wrote: > Simply put, I'm opening a database, searching through a table and then > showing a message box with 2 numbers, one of which is the RecordCount of the > RecordSet, but I'm being told by the computer that I have -1 for a > RecordCount. The RecordCount is anything but reliable. Use the BOF and EOF properties to determine if you have records and when you've reached the end of the recordset. -- There are 10 kinds of people: those who understand binary and those who don't.

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 18:20:38 GMT

I can't believe I'm gonna say this, but I never thought I'd have a Frank O correction. It *is* quite reliable actually but Microsoft didn't make it very obvious. I checked out aspfaq.com from Tom D's reply. Here's what I found out: http://support.microsoft.com/?id=194973 -- I support two teams: the Red Sox and whoever beats the Yankees. "Frank Oquendo" wrote in message news:415c4a8a$1_1@newsprd01... | Matt W wrote: | > Simply put, I'm opening a database, searching through a table and then | > showing a message box with 2 numbers, one of which is the RecordCount of the | > RecordSet, but I'm being told by the computer that I have -1 for a | > RecordCount. | | The RecordCount is anything but reliable. | | Use the BOF and EOF properties to determine if you have records and when | you've reached the end of the recordset. | | -- | There are 10 kinds of people: those who understand binary and those who | don't.

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 18:21:11 GMT

Change this line Dim rsSearch As Recordset To read like the following Dim rsSearch As ADODB.Recordset See what happens. "Matt W" wrote in message news:415c4741$1_1@newsprd01... > Simply put, I'm opening a database, searching through a table and then > showing a message box with 2 numbers, one of which is the RecordCount of the > RecordSet, but I'm being told by the computer that I have -1 for a > RecordCount. > I've using ADO... > > Here's the code... > The first sub opens the database > The second sub closes the database > The third sub tells me I have -1 counts > > '================================================= > Public Cnxn As ADODB.Connection > Public strCnxn As String > Public rstLayers As ADODB.Recordset > Public Const AppName = "VBA::Set Layer" > '================================================= > Public Sub OpenDatabase() > On Error GoTo ErrMsg > ' Open connection > strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data > Source=S:\CADDStds\Menus\Data\Layers.mdb" > Set Cnxn = New ADODB.Connection > Cnxn.Open strCnxn > Exit Sub > > ErrMsg: > MsgBox "An error has occurred in modMain.OpenDatabase" & vbCrLf & > Err.Number & ":" & Err.Description, vbCritical + vbOKOnly, AppName > End Sub'================================================= > Public Sub CloseDatabase() > On Error Resume Next > rstLayers.Close > > On Error Resume Next > Cnxn.Close > End Sub > '================================================= > Sub CountCurrent() > OpenDatabase > > Dim rsSearch As Recordset > Dim SQL As String > > Set rsSearch = New ADODB.Recordset > SQL = "SELECT * FROM Layers ORDER BY Layer_Name" > rsSearch.Open SQL, Cnxn > MsgBox rsSearch.RecordCount 'shows that I have -1 records??!? > Dim i As Integer > > i = 0 > Do Until rsSearch.EOF > If rsSearch!current = True Then i = i + 1 > rsSearch.MoveNext > Loop > MsgBox "Of the " & rsSearch.RecordCount & " files in the Layers table, " > & i & " are currently being used." > CloseDatabase > End Sub > '================================================= > > -- > I support two teams: the Red Sox and whoever beats the Yankees. > > >

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 18:48:18 GMT

Matt W wrote: > It *is* quite reliable actually but Microsoft didn't make it very obvious. When you request the RecordCount for a serverside recordset, a -1 may return. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic. This occurs with ADO 1.5 only when the cursortype is adOpenForwardonly. Testing with the OLEDB provider for JET and SQL Server produces varying results, depending on the provider. Providers may not support some CursorTypes. When you select a CursorType that is not supported, the provider should select the CursorType closest to what you request. Please refer to the documentation that comes with your provider. Also, please note that not all combinations of LockType and CursorType work together. Changing a LockType may force a change in the CursorType. Be sure to use debug to check the value of CursorType when conducting tests with your OLEDB provider. That's way too many variables for something to be considered reliable from a programmatic standpoint. -- There are 10 kinds of people: those who understand binary and those who don't.

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 18:53:58 GMT

I knew you were going to reply... I agree that there are a lot of variables, however, the fact still remains that it is reliable. I don't like the fact that "This behavior is by design." It certainly doesn't make it easy, but it's still reliable. :) -- I support two teams: the Red Sox and whoever beats the Yankees. "Frank Oquendo" wrote in message news:415c54f2_1@newsprd01... | Matt W wrote: | | > It *is* quite reliable actually but Microsoft didn't make it very obvious. | | | When you request the RecordCount for a serverside recordset, a -1 may | return. This occurs with ActiveX Data Objects (ADO) version 2.0 or later | when the CursorType is adOpenForwardonly or adOpenDynamic. This occurs | with ADO 1.5 only when the cursortype is adOpenForwardonly. Testing with | the OLEDB provider for JET and SQL Server produces varying results, | depending on the provider. | | Providers may not support some CursorTypes. When you select a CursorType | that is not supported, the provider should select the CursorType closest | to what you request. Please refer to the documentation that comes with | your provider. Also, please note that not all combinations of LockType | and CursorType work together. Changing a LockType may force a change in | the CursorType. Be sure to use debug to check the value of CursorType | when conducting tests with your OLEDB provider. | | | That's way too many variables for something to be considered reliable | from a programmatic standpoint. | | -- | There are 10 kinds of people: those who understand binary and those who | don't.

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 19:47:03 GMT

"Matt W" wrote in message news:415c566c_3@newsprd01... > I don't like the fact that "This behavior is by design." > It certainly doesn't make it easy, but it's still reliable. :) I'm kind of with Frank on this issue. I avoid using it for this very reason. In most cases, you can whittle down your recordset significantly with an appropriate SQL string. I usually put the resulting records into an array and can get a count from that. Again, keep in mind that I'm NOT an expert with ADO. Frank's not likely to give bad advice. 😉

Re: How can this be true??!?

Anonymous — Thu, 30 Sep 2004 20:30:18 GMT

First of all, use a client side cursor if you want to use the record count property. Set rsSearch = New ADODB.Recordset rsSearch.CursorLocation = adUseClient rsSearch..CursorType = adOpenDynamic 'or Static That should do it. If you want to stick to a server side cursor then you could get the recordcount like this: dim rstemp as ADODB.Recordset dim recordcount as long Set rstemp = Cnxn.execute ("SELECT count(*) FROM Layers",,adcmdtext) if rstemp.eof or rstemp.bof then recordcount = -1 else recordcount=rstemp.fields(0) end if set rstemp = nothing -- Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica "Matt W" wrote in message news:415c4741$1_1@newsprd01... > Simply put, I'm opening a database, searching through a table and then > showing a message box with 2 numbers, one of which is the RecordCount of > the > RecordSet, but I'm being told by the computer that I have -1 for a > RecordCount. > I've using ADO... > > Here's the code... > The first sub opens the database > The second sub closes the database > The third sub tells me I have -1 counts > > '================================================= > Public Cnxn As ADODB.Connection > Public strCnxn As String > Public rstLayers As ADODB.Recordset > Public Const AppName = "VBA::Set Layer" > '================================================= > Public Sub OpenDatabase() > On Error GoTo ErrMsg > ' Open connection > strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data > Source=S:\CADDStds\Menus\Data\Layers.mdb" > Set Cnxn = New ADODB.Connection > Cnxn.Open strCnxn > Exit Sub > > ErrMsg: > MsgBox "An error has occurred in modMain.OpenDatabase" & vbCrLf & > Err.Number & ":" & Err.Description, vbCritical + vbOKOnly, AppName > End Sub'================================================= > Public Sub CloseDatabase() > On Error Resume Next > rstLayers.Close > > On Error Resume Next > Cnxn.Close > End Sub > '================================================= > Sub CountCurrent() > OpenDatabase > > Dim rsSearch As Recordset > Dim SQL As String > > Set rsSearch = New ADODB.Recordset > SQL = "SELECT * FROM Layers ORDER BY Layer_Name" > rsSearch.Open SQL, Cnxn > MsgBox rsSearch.RecordCount 'shows that I have -1 records??!? > Dim i As Integer > > i = 0 > Do Until rsSearch.EOF > If rsSearch!current = True Then i = i + 1 > rsSearch.MoveNext > Loop > MsgBox "Of the " & rsSearch.RecordCount & " files in the Layers table, > " > & i & " are currently being used." > CloseDatabase > End Sub > '================================================= > > -- > I support two teams: the Red Sox and whoever beats the Yankees. > > >

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 00:06:48 GMT

I agree it certainly is reliable you just have to know what you are doing. Here is a better explanation from ASP 101 website.
Regards - Nathan

So why isn't it working for you? Well it's supported only if the recordset supports approximate positioning (adApproxPosition) or bookmarks (adBookmark). I only mention this so that if you feel like testing for support you can do so using code like this:

If objRecordset.Supports(adBookmark) Or objRecordset.Supports(adApproxPosition) Then
' RecordCount will work!
End If

So what the heck does that mean?

Basically it's supported if you use the right kind of cursor. For the most part all client-side cursors support it as do server-side static and server-side keyset cursors. So the only two that don't are server-side dynamic and server-side forward only cursors. Unfortunately, most people don't specify the cursor type to use when they open a recordset and what you usually get when you don't specify one is a server-side, forward only cursor which is one of the ones that doesn't support a record count! So the trick is to specify what type of cursor you want.

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 00:32:21 GMT

Just a bit extra to what Nathan said and have been mentioned so far. In order get record count, the database server must loop through the retrieved recordset at least once. The reason of that two types of cursor (dynamic and forward-only) do not return RecordCount is to optimize DB Server output. In many operation, there is no need to know recorcount before hand, so use this cursor type saves server resources. If you need to know record count, simply choose other type of cursor that gives it. If there is high user competition to connect to db server, what type of recordset to open could be a key point to think of. "Nathan Taylor" wrote in message news:12337953.1096589239929.JavaMail.jive@jiveforum1.autodesk.com... > I agree it certainly is reliable you just have to know what you are doing. Here is a better explanation from ASP 101 website. > Regards - Nathan > > So why isn't it working for you? Well it's supported only if the recordset supports approximate positioning (adApproxPosition) or bookmarks (adBookmark). I only mention this so that if you feel like testing for support you can do so using code like this: > > If objRecordset.Supports(adBookmark) Or objRecordset.Supports(adApproxPosition) Then > ' RecordCount will work! > End If > > So what the heck does that mean? > > Basically it's supported if you use the right kind of cursor. For the most part all client-side cursors support it as do server-side static and server-side keyset cursors. So the only two that don't are server-side dynamic and server-side forward only cursors. Unfortunately, most people don't specify the cursor type to use when they open a recordset and what you usually get when you don't specify one is a server-side, forward only cursor which is one of the ones that doesn't support a record count! So the trick is to specify what type of cursor you want.

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 03:34:22 GMT

Tip for you Tom - use a collection or dictionary. If you're not relying on a recordcount to redim your array, then you are either adjusting as you go which leads to unnecessary processing or you are dimming the array larger than you need to at start. Also, if by chance you hit a null value, the array's gonna pop an error where the collection won't. -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 03:44:20 GMT

Actually, you're both right and you're both wrong --- how's that for sticking to the middle of the road =) The key is whether you know what you're doing or not --- not you two but the programmer in general. It is unreliable if you are using a server-side cursor and/or don't understand how cursors & locking work. If you do understand, or you are using a client-side cursor then it is reliable. Personally, unless you are writing a web-based app or a stand alone configurator, you shouldn't be using a server-side cursor. Performance is great on the server side but you lose whatever advantage when you consider the network latency and you run the risk of lockiing others out whilst your app is doing whatever it does. I've rarely seen a cad-integrated app that requires the server-side --- exception being Map/MapGuide. -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 14:49:43 GMT

"Mike Tuersley" wrote in message news:qdktn6e77ty9.1pof2bibt2gbe.dlg@40tude.net... > Tip for you Tom - use a collection or dictionary. If you're not relying on > a recordcount to redim your array, then you are either adjusting as you go > which leads to unnecessary processing or you are dimming the array larger > than you need to at start. Also, if by chance you hit a null value, the > array's gonna pop an error where the collection won't. Thanks for the tip, Mike. Going to collections is one of the things I'm going to be doing.

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 14:52:40 GMT

"Mike Tuersley" wrote in message news:9lyed7kyigf2.17tlwsb6d9cm9$.dlg@40tude.net... > Actually, you're both right and you're both wrong --- how's that for > sticking to the middle of the road =) > > The key is whether you know what you're doing or not --- not you two but > the programmer in general. It is unreliable if you are using a server-side > cursor and/or don't understand how cursors & locking work. If you do > understand, or you are using a client-side cursor then it is reliable. > > Personally, unless you are writing a web-based app or a stand alone > configurator, you shouldn't be using a server-side cursor. Performance is > great on the server side but you lose whatever advantage when you consider > the network latency and you run the risk of lockiing others out whilst your > app is doing whatever it does. I've rarely seen a cad-integrated app that > requires the server-side --- exception being Map/MapGuide. I'm going to plead ignorance, Mike. Thanks, again, for the info. I hadn't even considered the server/client cursor aspect. Most of the DB stuff I've written is web-based, PHP stuff, which is inherently server-side. Time to do a little more reading, I think.

Re: How can this be true??!?

Anonymous — Fri, 01 Oct 2004 14:53:10 GMT

"Jorge Jimenez" wrote in message news:415c6c73$1_1@newsprd01... > First of all, use a client side cursor if you want to use the record count > property. > > Set rsSearch = New ADODB.Recordset > rsSearch.CursorLocation = adUseClient > rsSearch..CursorType = adOpenDynamic 'or Static Thanks, Jorge, for the Cursor info. It is most helpful.