Joint Query

Following query i am running from my VB6 form, it was ok with ACCESS Database but now
i have changed it to SQL Seraver 2008 R2, now its not working showing me 0 records. no error nothing..

Rs.Open "Select LS.field1, LS.field2, TR.field1, TR.field2, TR.field3, TR.field4 from LSheet as LS, TReport as TR where TR.field5 = LS.field1 and TR.field6 = '" & Text1(0).Text & "'", Con, adOpenKeyset, adLockOptimistic

Open in new window

LVL 1
crystal_TechAsked:
Who is Participating?
 
CluskittCommented:
What is the value of the textbox? Try inserting a breakpoint and manually inserting that query into SQL Studio. Or paste it over here so we can check it.
At first glance, there seem to be two possible sources of error:
1-The textbox, which may have an invalid value.
2-The join which says: TR.field5 = LS.field1
It seems strange to have different names, which might point to a typo, though not necessarily.

Either way, you should consider 2 things:
1-Change your query to use JOINS instead of relating them on WHERE. Not only does it provide better readibility, it increases performance:
Select LS.field1, LS.field2, TR.field1, TR.field2, TR.field3, TR.field4
FROM LSheet as LS INNER JOIN TReport as TR
ON TR.field5 = LS.field1
WHERE TR.field6 = '" & Text1(0).Text & "'
2-Consider using a parametrized query. Using concatenation the way you're using makes you vulnerable to SQL injection.
0
 
mastooCommented:
I need a little more context please.  This is in an access client against tables that are linked to sql server tables?

And what datatypes are the columns in the where clause?

And can you change to this and post the contents of the variable for me:

Dim sSql as string
sSql = "Select LS.field1, LS.field2, TR.field1, TR.field2, TR.field3, TR.field4 from LSheet as LS, TReport as TR where TR.field5 = LS.field1 and TR.field6 = '" & Text1(0).Text & "'
rs.Open sSql, Con, adOpenKeyset, adLockOptimistic
0
 
crystal_TechAuthor Commented:
First of all Thank you very much for QUICK REPLY :-).

INNER JOIN query is working perfectly..
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.