Joint Query

Posted on 2012-08-23
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

    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

    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
    LVL 1

    Author Comment

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

    INNER JOIN query is working perfectly..
    Thanks again

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Read about achieving the basic levels of HRIS security in the workplace.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now