Joint Query

Posted on 2012-08-23
Medium Priority
Last Modified: 2012-08-23
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

Question by:crystal_Tech
LVL 18

Accepted Solution

Cluskitt earned 1000 total points
ID: 38325153
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.
LVL 21

Expert Comment

ID: 38325198
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

Author Comment

ID: 38325359
First of all Thank you very much for QUICK REPLY :-).

INNER JOIN query is working perfectly..
Thanks again

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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