SQL Query does not return expected records

[TheVampire]

I've got a very simple SQL query that is not returning all the records it should return.

Here's how I set the database connection:

DB.Mode = adModeReadWrite
DB.Open "Provider=MSDASQL;Driver={Microsoft Paradox Driver (*.db )};" & "DriverID=538;" & "Fil=Paradox 7.X;" & "DefaultDir=" & MyDatabasePath & ";" & "Dbq=" & MyDatabasePath & ";" & "CollatingSequence=ASCII"

And here is the query to the data:

TENANTS.ActiveConnection = DB
TENANTS.Source = "SELECT * FROM tenant WHERE ACCESSNUMBER = '" & Password & "'"
TENANTS.CursorType = adOpenKeyset
TENANTS.LockType = adLockOptimistic
TENANTS.CursorLocation = adUseServer
TENANTS.Open

Now, at this point I would expect the tenants recordset to include all records that have the ACCESSNUMBER field set to the value I have for "Password" ( both are strings ). However, this is not the case, It often will only contain the first record in the database that matches.

To verify that the database actually does have more than one record, I can do this:

TENANTS.ActiveConnection = DB
TENANTS.Source = "SELECT * FROM tenant"
TENANTS.CursorType = adOpenKeyset
TENANTS.LockType = adLockOptimistic
TENANTS.CursorLocation = adUseServer
TENANTS.Open

Do Until TENANTS.EOF = True
If (TENANTS.Fields("ACCESSNUMBER") = Password) Then
     ' here I count up the number of records and it matches what I should see.
End If

So, why does the SQL command that asks for the matching password only show one record, but the one that fetches all records and filters them with an If statement show all correctly?

Obvously I prefer the first method to work because with a large number of tenant records I have to do a lot of looping and this slows things down. But until I figure out why the first method is doing what it's doing, I have no other choice.

Now, I realize that Paradox 7.0 files are not fully supported by VB6 and it may very well be that I'm stuck with this, but I want to see what can be done first.

Thanks

 

[RonRepp]

Try this:

    ''Set ref to ADOxx
    Dim Con As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SQL As String
    Dim ConStr As String
    Set Con = New ADODB.Connection
    Set RS = New ADODB.Recordset
    SQL = "SELECT * FROM tenant WHERE ACCESSNUMBER = '" & Password & "'"

    ConStr = "Provider=MSDASQL;Driver={Microsoft Paradox Driver (*.db )};" & "DriverID=538;" & "Fil=Paradox 7.X;" & "DefaultDir=" & MyDatabasePath & ";" & "Dbq=" & MyDatabasePath & ";" & "CollatingSequence=ASCII"
    Con.Open (ConStr)
    RS.Open SQL, Con, adOpenDynamic, adLockBatchOptimistic, adCmdText
    Do While Not RS.EOF
      MsgBox  RS("ACCESSNUMBER").Value   
    RS.MoveNext
    Loop
    RS.Close
    Con.Close
    Set RS = Nothing
    Set Con = Nothing

Also, is PASSWORD a Global Variable? It looks like you are passing it through an arglist (byval Password as string). Also, isn't PASSWORD unique (if it's a real password)? Passwords are in my company.

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[TheVampire]

Thanks, but that code still only returns one record ( the first record in the DB that matches ) even when there are other records that match. It's got me stumped. I tried it in VisData using the SQL Statement window and that gives me the same results, only the first matching record in the database appears in the recordset. If I use a different field that also has a matching value it still does the same thing. At this point I'm pretty sure it's something to do with the paradox DB drivers.

 

[TheVampire]

If I change the SQL to this:

SQL = "SELECT * FROM tenant WHERE ACCESSNUMBER LIKE '" & Password & "'"

it works fine. Isn't = and LIKE supposed to be the same if the comparison text does not include an asterisk?

 

[ArtieChoke]

Maybe you're running into case sensitivity for =, but not like?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[TheVampire]

The field is all numerals, so I shouldn't have any case differences. It's stored as a 10 character string though.

No matter. If I can get it to work using Like and not have any adverse effects then I'm happy.

 

[CaptainD]

Maybe white spaces. Try

SQL = "SELECT * FROM tenant WHERE RTRIM(ACCESSNUMBER) = '" & Password & "'

 

[MarkSweetland]

You should change the CursorLocation to adUseClient. Or, if you're trying to validate a count of records you can use count(*):

SELECT count(*)as RecCount FROM tenant WHERE ACCESSNUMBER LIKE '" & Password & "'"

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[RonRepp]

SQL = "SELECT * FROM tenant WHERE ACCESSNUMBER LIKE '" & Password & "'"

I'm not a SQL expert by any means, but when you use LIKE, you should use a wildcard.

SQL = "SELECT * FROM tenant WHERE ACCESSNUMBER LIKE '[b]%[/b]" & Password & "[b]%[/b]'"

i.e. If password is 1234567890, and you seek a password with 456 in it, then the actual SQL statement would look like this:

[COLOR=blue]SELECT * FROM tenant WHERE ACCESSNUMBER LIKE '[b]%456%[/b]'[/color]

It would also RETURN the first instance of a Password with that string in it.


I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net