 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / August 2006record count -1
I have some code that does different things depending on record count in a recordset. stepping through the code rs.BOF and rs.EOF are both false (so there is a record) when I look at recordCount on the locals window, it says -1 which causes the code to fail. where is it getting -1 or how do I determine what is causing this?  Signaturesam >I have some code that does different things depending on record count in a > recordset. [quoted text clipped - 7 lines] > > where is it getting -1 or how do I determine what is causing this? RecordCount doesn't always work. It depends on underlying data source and arguments, which you use, when you open recordset. Generally, RecordCount doesn't work with server-side and forward-only cursors , IIRC. When I need RecordCount I usually use client-side static cursor. This is known and documented behavior - check ADO's help. Dmitriy. Recordcount is not going to reliably return the count of records. If you need to the count of records do Select count(*) .. using the same tables/joins/criteria first. SignatureTerry Kreft > I have some code that does different things depending on record count in a > recordset. [quoted text clipped - 6 lines] > > where is it getting -1 or how do I determine what is causing this? > Recordcount is not going to reliably return the count of records. I don't think it is true. It may not return any count at all, like in the case with OP, but if it, indeed, returns some value greater than -1, then it must be reliable (at least, I never heard about problems with incorrect counting and never had such problems in my experience). Dmitriy. > > Recordcount is not going to reliably return the count of records. > [quoted text clipped - 4 lines] > > Dmitriy. You touch on an interesting bit of Data Access history. (to me anyway) In earlier days there was such a wide variety of providers, library versions, and a general lack of understanding of the different cursors types. There was even a reported 'bug' in some ODBC driver that it returned an erronous recordcount, though I am not sure it was ever really tracked down. Anyway a number of authors at this time and even MS tended to report that "RecordCount" may be unreliable, although has you pointed out such was never really the case - for any given cursor/library/provider stack results are always quite predictable. Yet the legend lives on. -ralph >> > Recordcount is not going to reliably return the count of records. >> [quoted text clipped - 12 lines] > was even a reported 'bug' in some ODBC driver that it returned an erronous > recordcount, though I am not sure it was ever really tracked down. I wasn't working as a programmer at that time - hence my "confidence" in this property's behavior :-) Dmitriy. 1) The OP is getting -1, this is not the correct count of records returned and so fits the statement. 2) Why use a method which is documented as unreliable when there is a method available which is reliable. SignatureTerry Kreft > > Recordcount is not going to reliably return the count of records. > [quoted text clipped - 4 lines] > > Dmitriy. > 1) The OP is getting -1, this is not the correct count of records returned > and so fits the statement. Not it doesn't (IMO, of course). RecordCount ALWAYS returns -1 with given combination of provider, cursorside and arguments of Open methods, and it ALWAYS returns correct count under any other combination (well I can't negate probability of bugs and errors in certain providers, but this applies for anything made by people). I wouldn't call it "unreliable" - it is just a matter of using proper combination. > 2) Why use a method which is documented as unreliable when there is a > method > available which is reliable. Again, I've never seen, where it was documented as unreliable. Documentation says that there are specifics of its usage, which we should take in account. While using Count(*) may be a proper method in certain cases, it is not ideal solution at all times. It requires additional two-way request to a server, which may unnecessary degrade performance and it complicates code, which may or may not be necessary. There are cases, when Count(*) is not applicable at all: "in-memory" recordsets, shaped recordsets (in this case using Count(*), while possible, can be extremely complicated, confusing and expensive). So choice between these two methods must be made based on particular task and existing or anticipated conditions. I wouldn't take or recommend Count(*) as a prescription, but rather as another available option. Dmitriy. >> > Recordcount is not going to reliably return the count of records. >> [quoted text clipped - 6 lines] >> >> Dmitriy. If you can't see that -1 is not the number of records returned then, well, to be honest I'm speechless. If you want to use a property which is documented as unreliable that is entirely up to you, personally where I require accuracy I would rather use a reliable method. If you can't think of a way to get the count and records in one call to the server then, I am sorry but, that is a restriction on your own methodology for virually all databases. To be honest, as I cannot even think of an occasion where I have had to use this property in release code it isn't a problem to me, at all. SignatureTerry Kreft > > 1) The OP is getting -1, this is not the correct count of records returned > > and so fits the statement. [quoted text clipped - 36 lines] > >> > >> Dmitriy. I can assure you that both me and Dmitriy know that -1 is _not_ a record count. It's just saying it can not get the record count in this environment. Agree with Dmitriy in the fact it is not unreliable, it just needs certain conditions to work. It is either working or not. If working it is correct all the time, if not it returns -1 always. For me Count() has it's point if I'm only interested in how many records would return a hit, not the contents of the records. As usual we all use the available tools our own way, and I can't say others are wrong. /Henning > If you can't see that -1 is not the number of records returned then, well, > to be honest I'm speechless. [quoted text clipped - 60 lines] > > >> > > >> Dmitriy. I can assure you that I am aware that Dmitriy realises that -1 indicates that a record count cannot be returned but at the same time I would have thought it obvious that this is a prime example that the recordcount property cannot be trusted. Of course you can continue to qualify that statement such as (to paraphrase Dmitriy and yourself) "For all the providers that I am aware of, using the correct settings for the connection and the recordset objects, a recordcount which is not -1 is reliable" .. but then that cannot be guaranteed for all data providers can it? So the safe statement is that recordcount cannot be relied upon. As I said before, this is completely irrelevant to me really as a) I would not use the recordcount property b) If I needed an accurate recordcount would use other more reliable methods. SignatureTerry Kreft > I can assure you that both me and Dmitriy know that -1 is _not_ a record > count. It's just saying it can not get the record count in this environment. [quoted text clipped - 84 lines] > > > >> > > > >> Dmitriy. We all code our apps and test them and use some installer to create an environment in the target computer that suits our app. Then if RecordCount works in my test computer, it has always worked in the target. If and when I can use RecordCount I will use it. It has never let me down. One point in this can be that I only use MySQL, so I never had any provider problems. /Henning > I can assure you that I am aware that Dmitriy realises that -1 indicates > that a record count cannot be returned but at the same time I would have [quoted text clipped - 118 lines] > > > > >> > > > > >> Dmitriy. Circular arguments with no resolution. SignatureTerry Kreft > We all code our apps and test them and use some installer to create an > environment in the target computer that suits our app. [quoted text clipped - 144 lines] > > > > > >> > > > > > >> Dmitriy. ><snipped> > [quoted text clipped - 10 lines] > ><snipped> The characterization of RecordCount as "unreliable" comes from the fact that the method cannot always be expected to return an actual count in all scenarios, not that the method can on occasion return an erroneous value. Actually the mis-characterization of RecordCount in the earlier days was a blessing in disguise. It caused one to abandon data-managment designs that depended on record counts (ie, constructs that were common in xBase and ISAM), and to work with data in a more relational manner. Whenever a programmer feels they need a record count, it is usually best if he/she pauses for a moment and considers either counting them in code, making a specific query, or perhaps even to use GetRows(). -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.