Wrox Programmer Forums
|
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 20th, 2006, 12:14 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default RecordCount

Hi All!

I want to count records from my Recordset, So I'm using here RECORDCOUNT property of RECORDSET but it always returns me [-1] though Recordset has 14 records.
Can somebody tell me how to use this property.
This is my source i'm using..


Dim con As New ADODB.Connection, rs As New ADODB.Recordset Class1
Private Sub Form_Load()
    Set con = con_open(con)
    rs.Open "select * from Table1", con, adOpenDynamic, adLockOptimistic
        rs.MoveNext
        MsgBox rs.RecordCount
    End
End Sub
Public Function con_open(con1 As ADODB.Connection) As ADODB.Connection
        con1.Provider = "Microsoft.jet.oledb.4.0"
        con1.ConnectionString = App.Path & "\dbase.mdb"
        con1.Open
        Set con_open = con1
End Function


Thanks.....

DPK..
__________________
DPK..
 
Old March 20th, 2006, 12:19 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you just want the number of records, I would suggest using an SQL statement rather than a RecordSet.

Example:
    SELECT Count(*) FROM Table1;

- A.Kahtava
 
Old March 20th, 2006, 06:22 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default

Thanks adam for your reply..
I always do this as you said but I don't want to open the recordset twice as I did below in code...
Any other way to do this without opening of Recordset...


Thanks again...

Dim con As New ADODB.Connection, rs As New ADODB.Recordset, ref As New Class1
Private Sub Form_Load()
    Set con = ref.con_open(con)
    rs.Open "select count(*) from table1", con, adOpenDynamic, adLockOptimistic
        Count = rs.Fields(0)
    rs.Close
    rs.Open "select * from Table1", con, adOpenDynamic, adLockOptimistic
        rs.MoveNext
        MsgBox rs.RecordCount
    End
End Sub
Public Function con_open(con1 As ADODB.Connection) As ADODB.Connection
        con1.Provider = "Microsoft.jet.oledb.4.0"
        con1.ConnectionString = App.Path & "\dbase.mdb"
        con1.Open
        Set con_open = con1
End Function


DPK..
 
Old March 20th, 2006, 02:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts
Default

not all sql engines support the RecordSet property, that AFAIK is available only using client cursors.
trying set the recordset.CursorLocation = adUseClient and see if it helps
Marco
 
Old March 21st, 2006, 04:51 AM
Authorized User
 
Join Date: Mar 2006
Posts: 80
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi.

The cursor type of the Recordset object affects whether the number of records can be determined.
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.

Peko


 
Old March 21st, 2006, 07:04 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you intend to manipulate the records within your RecordSet?
If you just want to display the number of records in Table1 then the following procedure should work.

Private Sub Form_Load()
    Set con = ref.con_open(con)
    rs.Open "select count(*) from table1", con, adOpenDynamic, adLockOptimistic
MsgBox rs.Fields(0)
    rs.Close
End Sub

- A.Kahtava
 
Old March 21st, 2006, 07:11 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default

Quote:
quote:Originally posted by adam_kahtava
 Do you intend to manipulate the records within your RecordSet?
If you just want to display the number of records in Table1 then the following procedure should work.

Private Sub Form_Load()
    Set con = ref.con_open(con)
    rs.Open "select count(*) from table1", con, adOpenDynamic, adLockOptimistic
MsgBox rs.Fields(0)
    rs.Close
End Sub

- A.Kahtava

Hi Adam !

Thanks Adam for your reply I know about this SQL statement I jst wanted to know that why RecordSet.RecordCount does not work with my code.
But now I knew that..

Thank you very much again....


DPK..
 
Old March 21st, 2006, 07:16 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Gotcha... Sorry; I didn't quite understand your problem.
Anyhow; glad you got it figured out.. :)

- A.Kahtava
 
Old March 23rd, 2006, 01:00 AM
Registered User
 
Join Date: Oct 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hello dpkbahuguna
plese try 2 change ur cursor and lock type combination

 
Old March 23rd, 2006, 01:16 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 180
Thanks: 1
Thanked 1 Time in 1 Post
Send a message via ICQ to dpkbahuguna Send a message via MSN to dpkbahuguna Send a message via Yahoo to dpkbahuguna
Default

Quote:
quote:Originally posted by kvingupta
 hello dpkbahuguna
plese try 2 change ur cursor and lock type combination

Hi kvingupta !

I already did it, and it worked...
But however...

Thanks for your reply ...
Have a Good Day !:)

DPK..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help RecordCount jpaul Pro VB Databases 1 January 29th, 2008 01:46 PM
Recordset.recordcount = -1 Huss Classic ASP Databases 0 February 17th, 2007 04:41 AM
RecordCount property doesn't work eapsokha Classic ASP Databases 6 February 10th, 2005 01:28 PM
RecordCount in DataGrid and Excel AyatKh ASP.NET 1.x and 2.0 Application Design 1 March 22nd, 2004 04:23 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.