Recordset Recordcount
-
I'm trying to find out how many records are in a table using this code but I keep getting -1 for n.
Dim command As String, data As ADODB.Recordset, n As Integer rsGuest.Open("Guest", ADOConnection,ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) command = "SELECT [Guest ID] from Guest" data = ADOConnection.Execute(command) n = data.RecordCountI read on another webisite that I need to add this line:data.CursorLocation = ADODB.CursorLocationEnum.adUseClientbut when I do I get this error: Operation is not allowed when the object is open. So what should I do. Thank you for your help. Mike -
I'm trying to find out how many records are in a table using this code but I keep getting -1 for n.
Dim command As String, data As ADODB.Recordset, n As Integer rsGuest.Open("Guest", ADOConnection,ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) command = "SELECT [Guest ID] from Guest" data = ADOConnection.Execute(command) n = data.RecordCountI read on another webisite that I need to add this line:data.CursorLocation = ADODB.CursorLocationEnum.adUseClientbut when I do I get this error: Operation is not allowed when the object is open. So what should I do. Thank you for your help. Mike -
I'm trying to find out how many records are in a table using this code but I keep getting -1 for n.
Dim command As String, data As ADODB.Recordset, n As Integer rsGuest.Open("Guest", ADOConnection,ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) command = "SELECT [Guest ID] from Guest" data = ADOConnection.Execute(command) n = data.RecordCountI read on another webisite that I need to add this line:data.CursorLocation = ADODB.CursorLocationEnum.adUseClientbut when I do I get this error: Operation is not allowed when the object is open. So what should I do. Thank you for your help. MikeHi, you could try a
SELECT COUNT(*) FROM Guestand use ExecuteScalar, then cast to integer. :)Luc Pattyn [Forum Guidelines] [My Articles]
This month's tips: - before you ask a question here, search CodeProject, then Google; - the quality and detail of your question reflects on the effectiveness of the help you are likely to get; - use PRE tags to preserve formatting when showing multi-line code snippets.
-
I'm trying to find out how many records are in a table using this code but I keep getting -1 for n.
Dim command As String, data As ADODB.Recordset, n As Integer rsGuest.Open("Guest", ADOConnection,ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) command = "SELECT [Guest ID] from Guest" data = ADOConnection.Execute(command) n = data.RecordCountI read on another webisite that I need to add this line:data.CursorLocation = ADODB.CursorLocationEnum.adUseClientbut when I do I get this error: Operation is not allowed when the object is open. So what should I do. Thank you for your help. Mike -
Hi, you could try a
SELECT COUNT(*) FROM Guestand use ExecuteScalar, then cast to integer. :)Luc Pattyn [Forum Guidelines] [My Articles]
This month's tips: - before you ask a question here, search CodeProject, then Google; - the quality and detail of your question reflects on the effectiveness of the help you are likely to get; - use PRE tags to preserve formatting when showing multi-line code snippets.
Hi thank you both for your help. I don't have much experience with database programming and I'm not sure what ExecuteScalar is a method of or how I would use it. To clarify the question of whether I was trying to get the number of rows or the highest id number, I was looking for the number of rows. thanks again Luc and blueboy, Mike
-
Hi thank you both for your help. I don't have much experience with database programming and I'm not sure what ExecuteScalar is a method of or how I would use it. To clarify the question of whether I was trying to get the number of rows or the highest id number, I was looking for the number of rows. thanks again Luc and blueboy, Mike
-
If you are using vb.net , I would recomment using the System.Data.Oledb classes rather than com interop with ADODB. It would be both easier and more performant. To get the number of records currently in a table Use "Select Count(*) as nRecs from Mytable" for your query. For your ADODB approach to work, you have to navigate to the end of the recordset before the count is accurate (use Recordset.MoveLast). Obviously, this is slow... To changed to a client side cursor, set the cursortype variable BEFORE opening the connection. Hope thies suggestions help.