?
Solved

Access SQL select inner join  statement

Posted on 2006-06-26
4
Medium Priority
?
668 Views
Last Modified: 2008-01-09
Could someone please help me! I am trying to select specific records from a table using sql and I get all the records all the time.

I am using two datatables    Shifts and jobs, the join should be on JobNo and only select the ones that match the text selected in the listbox.


Here is the code.


        Dim jobString As String
        jobString = "'"  &  lstJobs.SelectedItem & "'"              '  double, single, double quotes & [contents of textbox] & double,single,double quotes.
        dbCommand.Connection = dbConnection                   'defined at the top of the form
        dbCommand.CommandType = CommandType.Text
        dbCommand.CommandText = "Select * from shift inner join jobNo on  shift.jobNo = jobs.jobNo where shift.JobNo = " & jobstring


        da.SelectCommand = dbCommand
        da.Fill(dt)
        grdShift.Visible = True
        grdShift.DataSource = dt
   

'  the data grid shows all the records not just the ones that match the criteria



Thanks,
Bsturge

0
Comment
Question by:bsturge
4 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16985141
Have you checked that jobString contains a valid value ? What is the data-type of shift.JobNo ?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16985147
you probably need either single or double quotes around the shift.JobNo value.  I'm not sure about Acess but for SQL it would be

dbCommand.CommandText = "Select * from shift inner join jobNo on  shift.jobNo = jobs.jobNo where shift.JobNo = '" & jobstring & "'"
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 1000 total points
ID: 16985836
Try this

        dbCommand.CommandText = "Select * from shift inner join jobs on  shift.jobNo = jobs.jobNo where shift.JobNo = " & jobstring

At the moment you have "inner join jobNo" but at that point you need to name the table, not the field.

Roger
0
 
LVL 2

Accepted Solution

by:
Bill_PSC earned 1000 total points
ID: 16986153
Do the tables have a foreign key relationship?  I would try putting the datatables into a dataset and enable the foreign key relationship.

dataSet.Relations.Add(yourKey)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

615 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