CODEWITHSUNDEEP
sqlvb.netms-access
Hayden
Hayden

Reputation: 25

Datagrid Duplication due to Select Statement VB

I am using an Access database, I believe the problem lies in my SQL statement. I have a relational database, with two tables - 

StaffDetails [columns(
StaffID, 
FirstName, 
LastName)]

and 

StaffTraining [columns(
StaffID, 
Month)].

I have a combobox (cbMonth) and dependent on what month is chosen if the user selects 'January' then I would like the datagrid (DGTraining) to show the First Name and Last Name of the members of staff whose ID are within the chosen month. Sorry if this is not the clearest explanation, hopefully my code below makes my issue clearer:

Dim SqlQuery As String = "SELECT [StaffDetails.StaffID], [StaffDetails.FirstName], [StaffDetails.LastName], [StaffTraining.StaffID] FROM [StaffDetails], [StaffTraining] WHERE StaffTraining.TrainingMonth='" & cbMonth.Text & "'"
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(SqlQuery, conn)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, "Training")
    Dim dt As DataTable = ds.Tables("Training")

    With DGTraining
        .AutoGenerateColumns = True
        .DataSource = ds
        .DataMember = "Training"
    End With

Upvotes: 1

Views: 52

Answers (2)

Mych
Mych

Reputation: 2563

Your last comment needs amending like this...

Dim SqlQuery As String = "SELECT [StaffDetails.StaffID], [StaffDetails.FirstName],
[StaffDetails.LastName], FROM [StaffDetails]
INNER JOIN [StaffTraining] ON [StaffDetails].StaffID = [StaffTraining].StaffID
WHERE [StaffTraining].TrainingMonth='" & cbMonth.Text & "'"

Also... dependant on how you have set up cbMonth you may want cbMonth.SelectedValue or cbMonth.SelectedText

Upvotes: 0

Twelfth
Twelfth

Reputation: 7180

You are missing your join and are getting a cross join. 2 ways of addressing:

FROM [StaffDetails] inner join [StaffTraining] on  [StaffDetails].staffID = [StaffTraining].staffID

That is the join logic thats more common and easier to read. You could add to your where clause (old method, harder to read and not as commonly accepted:

...where [StaffDetails].staffID = [StaffTraining].staffID and ...

Upvotes: 1

Related Questions