Solved

Access SQL select inner join  statement

Posted on 2006-06-26
4
652 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 250 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 250 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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