Using 2 combo boxes to populate a datagridview in VB.NET-CodePudding

I having problems understanding why my one of my comboboxes displays a filtered search but the other one doesn't, I am using the same code for both comboboxes but modified some SQL queries linked to my database. I have also noticed that when I remove or comment out the code for any one of the comboboxes the the filtered search happens for the one hasn't been commented or removed. I also used an "If, Else" statement but still doesn't work. I would also want for both comboboxes to be used to filter a datagridview. Just to keep in mind once the item is selected from the combobox a search button is pressed to filer/display data into the datagridview.

Kind Regards

Here is my code and form:

[Redundant Data being displayed] https://i.stack.imgur.com/JEQI4.png

[ComboBox Brand works as intended] https://i.stack.imgur.com/6YyBf.png

[ComboBox Category displays everything rather than displaying the category chosen] https://i.stack.imgur.com/oEfII.png

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click

     If Not CmbBrand.SelectedIndex & CmbCategory.SelectedIndex = Nothing Then
         BrandDisplay()
     ElseIf CmbBrand.SelectedIndex & Not CmbCategory.SelectedIndex = Nothing Then
         CategoryDisplay()
     ElseIf Not CmbBrand.SelectedIndex & Not CmbCategory.SelectedIndex = Nothing Then

If DbConnect() Then 
             DgvRecord.Rows.Clear()
             Dim SQLCmd As New OleDbCommand 
             With SQLCmd 
                 .Connection = cn 
                 .CommandText = "Select * " &
                                "From TblStock " &
                                "Where STCategory Like @CategorySearch" 
                 .Parameters.AddWithValue("@CategorySearch", "%" & CmbCategory.Text & "%") 
                 Dim rs As OleDbDataReader = .ExecuteReader() 
                 SQLCmd.ExecuteReader()
                 While rs.Read 
                     Dim NewStockRow As New DataGridViewRow()
                     NewStockRow.CreateCells(DgvRecord)
                     NewStockRow.SetValues({rs("StockID"), rs("STDateTime"), rs("STCategory"), rs("STBrand"), rs("STItemDescription"), rs("STSerialNumber"), rs("StockIn"), rs("StockOut"), rs("Stock")})
                     NewStockRow.Tag = rs("StockID")
                     DgvRecord.Rows.Add(NewStockRow)
                 End While
                 rs.Close()

                 If DgvRecord.Rows(0).Selected = True Then
                     MessageBox.Show("Please select a Category from the drop down list", "Category", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                 End If
             End With
         End If
     End If
     cn.Close()
 End Sub

 Private Sub BrandDisplay()
     If DbConnect() Then 
         DgvRecord.Rows.Clear()
         Dim SQLCmd As New OleDbCommand 
         With SQLCmd 
             .Connection = cn 
             .CommandText = "Select * " &
                            "From TblStock " &
                            "Where STBrand Like @BrandSearch" 
             .Parameters.AddWithValue("@BrandSearch", "%" & CmbBrand.Text & "%") 
             Dim rs As OleDbDataReader = .ExecuteReader() 
             SQLCmd.ExecuteReader()
             While rs.Read 
                 Dim NewStockRow As New DataGridViewRow()
                 NewStockRow.CreateCells(DgvRecord)
                 NewStockRow.SetValues({rs("StockID"), rs("STDateTime"), rs("STCategory"), rs("STBrand"), rs("STItemDescription"), rs("STSerialNumber"), rs("StockIn"), rs("StockOut"), rs("Stock")})
                 NewStockRow.Tag = rs("StockID")
                 DgvRecord.Rows.Add(NewStockRow)
             End While
             rs.Close() 

             If DgvRecord.Rows(0).Selected = True Then
                 MessageBox.Show("Please select a Brand from the drop down list", "Brand", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
             End If
         End With
     End If
     cn.Close()
 End Sub

 Private Sub CategoryDisplay()
     If DbConnect() Then 
         DgvRecord.Rows.Clear()
         Dim SQLCmd As New OleDbCommand 
         With SQLCmd 
             .Connection = cn 
             .CommandText = "Select * " &
                            "From TblStock " &
                            "Where STCategory Like @CategorySearch" 
             .Parameters.AddWithValue("@CategorySearch", "%" & CmbCategory.Text & "%")
             Dim rs As OleDbDataReader = .ExecuteReader() 
             SQLCmd.ExecuteReader()
             While rs.Read 
                 Dim NewStockRow As New DataGridViewRow()
                 NewStockRow.CreateCells(DgvRecord)
                 NewStockRow.SetValues({rs("StockID"), rs("STDateTime"), rs("STCategory"), rs("STBrand"), rs("STItemDescription"), rs("STSerialNumber"), rs("StockIn"), rs("StockOut"), rs("Stock")})
                 NewStockRow.Tag = rs("StockID")
                 DgvRecord.Rows.Add(NewStockRow)
             End While
             rs.Close() 

             If DgvRecord.Rows(0).Selected = True Then
                 MessageBox.Show("Please select a Category from the drop down list", "Category", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
             End If
         End With
     End If
     cn.Close()
 End Sub
 ```

CodePudding user response：

It is a good idea to separate your User Interface code from you database code. Your event procedure code should be rather brief.

Declare your Connections, Commands and DataReaders in the method where they are used so they can be disposed. Using...End Using blocks do this for us; they also close the connection. Pass your connection string directly to the constructor of the connection.

We have a different CommandText and ParametersCollection for each possibility. For Sql Server use the Add method rather than AddWithValue.

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
    Dim dt = GetSearchData(CmbBrand.Text, CmbCategory.Text)
    DGVRecord.DataSource = dt
End Sub

Private Function GetSearchData(Brand As String, Category As String) As DataTable
    Dim dt As New DataTable
    Dim sqlString = "Select * From From TblStock "
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand()
        cmd.Connection = cn
        If Not String.IsNullOrEmpty(Brand) AndAlso Not String.IsNullOrEmpty(Category) Then
            cmd.CommandText = sqlString & "Where STCategory = @CategorySearch And STBrand = @BrandSearch;"
            cmd.Parameters.Add("@CategorySearch", SqlDbType.VarChar).Value = Brand
            cmd.Parameters.Add("@BrandSearch", SqlDbType.VarChar).Value = Category
        ElseIf Not String.IsNullOrEmpty(Brand) Then
            cmd.CommandText = sqlString & "Where STBrand = @BrandSearch;"
            cmd.Parameters.Add("@BrandSearch", SqlDbType.VarChar).Value = Category
        ElseIf Not String.IsNullOrEmpty(Category) Then
            cmd.CommandText = sqlString & "Where STCategory = @CategorySearch;"
            cmd.Parameters.Add("@CategorySearch", SqlDbType.VarChar).Value = Brand
        Else
            cmd.CommandText = sqlString & ";"
        End If
        cn.Open()
        Using reader = cmd.ExecuteReader()
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

CodePudding user response：

For better understanding you need to change those first "if... then... else...". If the combobox is not selected it will have value -1 so you can do it like this:

Dim bBrandIsSelected as boolean = CmbBrand.SelectedIndex <> -1
Dim bCategoryIsSelected as boolean = CmbCategory.SelectedIndex <> -1

Now you can build the code more easily like:

If bBrandIsSelected AndAlso bCategoryIsSelected then
  ' do something
else
    if bBrandIsSelected then  
        BrandDisplay()
    else
       if bCategoryIsSelected then
           CategoryDisplay()
       End if
    End if
 End if