VB & Database, recordcount returning -1 when there are 50 records

recordcount returning -1 when there are 50 records

Author	Message
Ed Vos #1 / 5	recordcount returning -1 when there are 50 records Hi Matt, Recordcount is an unreliable way of finding how many records are in a recordset. One way of getting an acurate record count out of a recordset is to loop through it and increment a integer variable for each loop, like this... oRec.MoveFirst dim i as integer i = 0 do while not oRec.EOF i = i + 1 oRec.MoveNext Loop msgbox i There are other ways, but this one is pretty foolproof, and will have a negligable impact on performance unless you are dealing with thousands of records. HTH Ed Voss MCP Quote: >-----Original Message----- >Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would Quote: >the following return -1? >Dim oCon, oRec >set oCon = CreateObject("ADODB.Connection") >set oRec = CreateObject("ADODB.RecordSet") >'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE & Quote: >";Trusted_Connection=yes;" >oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass" >'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " ' ", >con, adOpenStatic >oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID = Quote: >107 ", oCon >msgbox oRec.recordcount >oRec.close >Set oRec = Nothing >Set oCon = Nothing >I'm working in Windows Script right now.. >I read the deal about using .movelast before calling .recordcount.. I tried Quote: >that but no luck. I've also tried different cursors and such.. maybe I just >didn't get the right combo? >This Query works fine when I run it from WinSQL and returns 50 records. >TIA >Matt >.
Sun, 08 May 2005 08:03:31 GMT

Matt Williamso #2 / 5	recordcount returning -1 when there are 50 records Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would the following return -1? Dim oCon, oRec set oCon = CreateObject("ADODB.Connection") set oRec = CreateObject("ADODB.RecordSet") 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE & ";Trusted_Connection=yes;" oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass" 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " ' ", con, adOpenStatic oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID = 107 ", oCon msgbox oRec.recordcount oRec.close Set oRec = Nothing Set oCon = Nothing I'm working in Windows Script right now.. I read the deal about using .movelast before calling .recordcount.. I tried that but no luck. I've also tried different cursors and such.. maybe I just didn't get the right combo? This Query works fine when I run it from WinSQL and returns 50 records. TIA Matt
Sun, 08 May 2005 06:23:26 GMT

Sukesh Hooga #3 / 5	recordcount returning -1 when there are 50 records Matt Apart from Ed's solution, you can try as follows set the cursortype to adOpenStatic or adOpenKeyset and get the recordcount (as only client side recordsets or keyset cursors return the correct number of rows). oRec.Open strSql, cn, adOpenKeyset, adLockOptimistic, adCmdText or oRec.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText textbox.text=oRec.RecordCount Sukesh Quote: > Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would > the following return -1? > Dim oCon, oRec > set oCon = CreateObject("ADODB.Connection") > set oRec = CreateObject("ADODB.RecordSet") > 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE & > ";Trusted_Connection=yes;" > oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass" > 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " ' ", > con, adOpenStatic > oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID = > 107 ", oCon > msgbox oRec.recordcount > oRec.close > Set oRec = Nothing > Set oCon = Nothing > I'm working in Windows Script right now.. > I read the deal about using .movelast before calling .recordcount.. I tried > that but no luck. I've also tried different cursors and such.. maybe I just > didn't get the right combo? > This Query works fine when I run it from WinSQL and returns 50 records. > TIA > Matt
Sun, 08 May 2005 12:52:40 GMT

Richard T. Edward #4 / 5	recordcount returning -1 when there are 50 records Set the cursor location to 3 or adUseClient and you'll get your recordcount. oRec.Cursorlocation=3 oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID = 107 ", oCon msgbox oRec.recordcount HTH Quote: > Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would > the following return -1? > Dim oCon, oRec > set oCon = CreateObject("ADODB.Connection") > set oRec = CreateObject("ADODB.RecordSet") > 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE & > ";Trusted_Connection=yes;" > oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass" > 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " ' ", > con, adOpenStatic oRec.Cursorlocation=3 oRec.Open "select * from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID = 107 ", oCon msgbox oRec.recordcount - Hide quoted text - - Show quoted text - Quote: > oRec.close > Set oRec = Nothing > Set oCon = Nothing > I'm working in Windows Script right now.. > I read the deal about using .movelast before calling .recordcount.. I tried > that but no luck. I've also tried different cursors and such.. maybe I just > didn't get the right combo? > This Query works fine when I run it from WinSQL and returns 50 records. > TIA > Matt
Sun, 08 May 2005 13:50:02 GMT

Val Mazu #5 / 5	recordcount returning -1 when there are 50 records Matt, RecordCount -1 means that provider cannot determine actual record count based on current setting of cursor. Usually (but not all) server side cursors cannot return that information. Rules for that quite simple: the RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.What you need to do is to change settings of your cursor to state, which would allow to get recordcount and will be suitable for your application. Also check next KB about RecordCount http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194973 You need to do that ONLY in case if you really need to get record count. If you just need to find out if recordset has any records, then you could use EOF property right after you opened reordset If Not MyRecordset.EOF then 'there are some records in recordset else 'there are no records in recordset endif Another way to find actual record count is to use COUNT function in your SELECT statement SELECT COUNT() AS RESULT WHERE .... it will return 0 or actual count any time when you call it -- Val Mazur Microsoft MVP Quote: > Brand new to ADO.. just got the SDK and I'm fumbling through it. Why would > the following return -1? > Dim oCon, oRec > set oCon = CreateObject("ADODB.Connection") > set oRec = CreateObject("ADODB.RecordSet") > 'con.Open "Driver={SQL Server};Server=" & SERVER & ";Database=" & DATABASE & > ";Trusted_Connection=yes;" > oCon.Open "DSN=PacerViews;" & "Uid=User;" & "Pwd=Pass" > 'oRec.Open "delete from " & TABLE & " where portcd = ' " & portcode & " ' ", > con, adOpenStatic > oRec.Open "select from FVAL_HOLD where PORTCD = '051504' and PROFILE_ID = > 107 ", oCon > msgbox oRec.recordcount > oRec.close > Set oRec = Nothing > Set oCon = Nothing > I'm working in Windows Script right now.. > I read the deal about using .movelast before calling .recordcount.. I tried > that but no luck. I've also tried different cursors and such.. maybe I just > didn't get the right combo? > This Query works fine when I run it from WinSQL and returns 50 records. > TIA > Matt
Sun, 08 May 2005 21:08:11 GMT

Page 1 of 1

[ 5 post ]