[RESOLVED] selecting data from mdb

[lars reus]

Hi there,

i have a problen selecting data from an Access Database.

i am using Microsoft DAO 3.6 Object Library

i have the followin tables:

testA (id number, vctext text)
1,"a"
4,"b"

testB(id number, vctext text)
1,"a"
2,"b"
1,"a "
2,"b "


my code is:

Private Sub Form_Load()
Dim sSql As String
Dim gdbloc As Database

DBEngine.SetOption dbImplicitCommitSync, "YES"
Set gdbloc = OpenDatabase("c:\test.mdb", False, False, "")

sSql = "select testa.id as item_no, testb.vctext as DESC_1TB from "
sSql = sSql & "testA left join testB on (testa.vctext = testb.vctext) "
sSql = sSql & " order by testa.id "

Set rs = gdbloc.OpenRecordset(sSql, dbOpenSnapshot)
While Not rs.EOF
MsgBox (rs!item_no & " " & rs!DESC_1TB)
rs.MoveNext
Wend
rs.Close
End Sub


and i get 4 Messageboxes saying:
"1 a"
"1 a "
"4 b"
"4 b "

I expected only 2!

Can someone help?

Best Regards

Lars

[Doogle]

I think it's the Left Join that may be the problem. How about just using a Where clause instead of the Join?

[lars reus]

Thanks doogle,

that might be, but i have tested the Query direct in my MDB and it works fine.

The code i privides just explains the problem. The original code is in a ERP-System i have the Source-code from (and the problem might there be a several times.....)

Lars

[lars reus]

Hi Doogle,

i tested with :

sSql = "select testa.id as item_no, testb.vctext as DESC_1TB from "
sSql = sSql & "testA , testB "
sSql = sSql & "where testa.vctext = testb.vctext "
sSql = sSql & "order by testa.id "

but i get the same 4 message-Boxes.

it seems that the trailing spaces in table testb are ignored.....

Lars

[Doogle]

Ah, I suggest you put a breakpoint on, just before you execute the query and then when that triggers, check that the Tables actually contain what you are expecting.

I've seen this sort of problem happen (works in Access but doesn't via SQL) when one or more of the variables / column names are reserved words in VB, which is another thing to look out for.

[lars reus]

.... so i tested again what i tested yesterday - the query in the mdb -
i get the wrong result ...... (shi....)

sorry i have to test further and will post again when i have fixed the problem or identified it to have only to be in VB6.

Thank you

Lars

[dilettante]

Probably related to ANSI 92 SQL compliance which requires compares to pad items to the same length.

INF: How SQL Server Compares Strings with Trailing Spaces

And yes I realize we're talking about a Jet database here.

Try:

testa.vctext = testb.vctext AND Len(testa.vctext) = Len(testb.vctext)

[lars reus]

Thank you dilettante for that WorkAround!

I think i have to prevent users from entering data with trailing spaces.

Lars