Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

Access SQL select inner join statement

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
bsturge
Asked:
bsturge
2 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
Have you checked that jobString contains a valid value ? What is the data-type of shift.JobNo ?
0
 
Brian CroweDatabase AdministratorCommented:
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
 
SanclerCommented:
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
 
Bill_PSCCommented:
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
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now