?
Solved

VB6 - Negative recordcounts

Posted on 2008-11-16
5
Medium Priority
?
345 Views
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"
       
    cn.Open
   
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

Peter


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
                Else
                    DSWORK("PD_Diabetic") = True
                End If
                
                rr.Close

Open in new window

0
Comment
Question by:peterdarazs
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:carlsiy
ID: 22970058
are you sure you can connect to the database?
pls go to http://www.connectionstrings.com for the correct connection string.
 
0
 

Author Comment

by:peterdarazs
ID: 22970177
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
0
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 640 total points
ID: 22970244
try set the CursorLocation to adUseClient

...

Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient
...
0
 
LVL 6

Assisted Solution

by:carlsiy
carlsiy earned 360 total points
ID: 22970267
ryancys's answer could be it. CursorLocation determines whether your returning data or not, you should set the CursorLocation to adusec.lient
0
 

Author Closing Comment

by:peterdarazs
ID: 31517218
Thanks Guys - This certainly looks like the answer.

Peter
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

839 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