Solved

Access SQL select inner join  statement

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

18 Experts available now in Live!

Get 1:1 Help Now