?
Solved

Access SQL select inner join  statement

Posted on 2006-06-26
4
Medium Priority
?
657 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

752 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