VB6 - Negative recordcounts

Posted on 2008-11-16
Last Modified: 2013-11-25
Hi Experts
I'm getting negative recordcounts with this SQL query and can't run logic based on it

It's a VB6 app connecting to a SQL server file via a connection string. ie:
cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=c:\BPSSAMPLES3.mdf;Data Source=peter-84s5hz3wy\SQLEXPRESS"
Everything runs nicely, except when i need to make a variable T/F based on the recordcount. No matter the nr of corresponding records in the table, the rs.recordcount always returns -1.  rs doesn't allow rr.nomatch or rr.movefirst either, so i'm stuck for determining match / nomatch.

 many thanks


Dim rs As ADODB.Recordset 'recordset

Set rs = New ADODB.Recordset

rs.Open "Select * from DIABETES where InternalID = " & currentnr, cn

                Debuglist.AddItem "Recordcount for " & currentnr & " = " & rs.RecordCount


               '*recordcount always comes out as -1 so the following logic never works properly

                If rs.RecordCount = 0 Then

                    DSWORK("PD_Diabetic") = False


                    DSWORK("PD_Diabetic") = True

                End If



Open in new window

Question by:peterdarazs
    LVL 6

    Expert Comment

    are you sure you can connect to the database?
    pls go to for the correct connection string.

    Author Comment

    It certainly does connect and i'm able to populate alll my access tables correctly (except when the logic requires the recordcount.) If you don't mind, i'm going to allow this question to linger a little and see what else comes up by way of answers. many thanks
    LVL 48

    Accepted Solution

    try set the CursorLocation to adUseClient


    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseClient
    LVL 6

    Assisted Solution

    ryancys's answer could be it. CursorLocation determines whether your returning data or not, you should set the CursorLocation to adusec.lient

    Author Closing Comment

    Thanks Guys - This certainly looks like the answer.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now