Populate Datagrid - comboboxes

[amecily]

Hey everyone,

I am developing a Windows application in Visual Studio 2005, V2.0 using VB.NET.
I am coding part of an Emergency Response Information System. On my form, I have two combo boxes (cboBaseStationID and cboPriority). I am trying to populate the datagrid with records from my database based on what is selected in both comboboxes. For the first combobox, the user selects a name so the datagrid must only show records with that name. For the second combobox, this is a little more complicated. There are four emergency priorities in the combobox that the user can choose - 1, 2, 3, 4. In the database, there are different response teams (to be dispatched to these emergencies) and each response team has a grade of either 1, 2 or 3.

So a grade 1 team can respond to emergency priorities of 1 and 2.
A grade 2 team can respond to emergency priorities of 1, 2 and 3.
A grade 3 team can respond to emergency priorities of 1, 2, 3 and 4.

So basically, if the user selects emergency priority of '4', then only grade 3 teams can be displayed. I am using two different tables in the datagrid. The datagrid is populating at the moment, but not based on anything that is selected in the comboboxes. It just displays a lot of duplicates of all the records in the tables. Here is my code so far (this code is on the search button click - I have already loaded items into the comboboxes on the form's load event):

Dim sqlConn As New OleDb.OleDbConnection
        Dim sqlCmd As New OleDb.OleDbCommand
        Dim sqlReader As OleDb.OleDbDataReader
        Dim strBaseStationName As String
        strBaseStationName = cboBaseStationID.SelectedValue

If cboBaseStationID.Text = "" Then
            MessageBox.Show("Please select a base station", "No Base Station Selected", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            sqlConn.ConnectionString = "provider=microsoft.jet.oledb.4.0; data source=C:/Final ERIS Database.mdb"
            sqlCmd.Connection = sqlConn
            sqlConn.Open()

            sqlCmd.CommandText = "SELECT BaseStationCode, BaseStationName, ServiceArea, ResponseTeamOnShiftID, ShiftGrade, State FROM BaseStation, Response_Team_On_Shift WHERE BaseStationName = '" & strBaseStationName & "' AND  State = 'Available'"

            If cboPriority.SelectedIndex = 0 Then

                sqlCmd.CommandText = "SELECT BaseStationCode, BaseStationName, ServiceArea, ResponseTeamOnShiftID, ShiftGrade, State FROM BaseStation, Response_Team_On_Shift WHERE ShiftGrade = '1' OR ShiftGrade = '2' OR ShiftGrade = '3'"

            ElseIf cboPriority.SelectedIndex = 1 Then
                sqlCmd.CommandText = "SELECT BaseStationCode, BaseStationName, ServiceArea, ResponseTeamOnShiftID, ShiftGrade, State FROM BaseStation, Response_Team_On_Shift WHERE ShiftGrade = '1' OR ShiftGrade = '2' OR ShiftGrade = '3'"
            ElseIf cboPriority.SelectedIndex = 2 Then
                sqlCmd.CommandText = "SELECT BaseStationCode, BaseStationName, ServiceArea, ResponseTeamOnShiftID, ShiftGrade, State FROM BaseStation, Response_Team_On_Shift WHERE ShiftGrade = '2' OR ShiftGrade = '3'"
            ElseIf cboPriority.SelectedIndex = 3 Then
                sqlCmd.CommandText = "SELECT BaseStationCode, BaseStationName, ServiceArea, ResponseTeamOnShiftID, ShiftGrade, State FROM BaseStation, Response_Team_On_Shift WHERE ShiftGrade = '3'"
            End If

sqlReader = sqlCmd.ExecuteReader
                 Dim dtAvailableTeams As New DataTable("BaseStation, Response_Team_On_Shift")

            dtAvailableTeams.Load(sqlReader)

            ' Bind data table to datagrid
            dgAvailableTeams.DataSource = dtAvailableTeams

            ' Close Reader
            sqlReader.Close()


            dgAvailableTeams.Visible = True
            sqlConn.Close()

Can someone please indicate where I'm going wrong? Am pretty sure it has everything to do with the datareader. Never used it before.

Thanks,
Amy

[lolafuertes]

The FROM Clause is merging each BaseStation with all the possible Response_Team_On_shift records, then the result if filtered according to the WHERE clause.
Hope this is the behaviour expected.

To eliminate the duplicates you can modify the SELECT clause adding the DISTINCT restriction like:
sqlCmd.CommandText = "SELECT DISTINCT BaseStationCode, BaseStationName, ServiceArea, ResponseTeamOnShiftID, ShiftGrade, State FROM BaseStation, Response_Team_On_Shift WHERE ShiftGrade = '1' OR ShiftGrade = '2' OR ShiftGrade = '3'"

Hope this helps