[RESOLVED] Combobox Filter With Databinding (VB.NET)

I have 2 comboboxes displaying 2 different columns from the same table in a SQL database. I would like to filter the second combobox depending upon the selection in the first combobox.

I have searched the internet and have only found semi-confusing tutorials and examples that I do not find very helpful as I am new to VB.NET.

SQL DB Tablename = Motor
Two column names are 1. Voltage 2. Horsepower
Two comboboxes are 1. cboMotorVoltage 2. cboMotorHP

Have an exception-free day,

Seth

Do you know how to use the Like SQL Command, I believe it can be helpful?

Quote:

---

Originally Posted by HanneSThEGreaT

Do you know how to use the Like SQL Command, I believe it can be helpful?

---

Thanks for the speedy reply.

I am very comfortable with coding SQL but I am not very familiar with calling SQL commands in VB.NET.

Quote:

---

Originally Posted by HanneSThEGreaT

Do you know how to use the Like SQL Command, I believe it can be helpful?

---

Please help!

I have still not found any helpful links. Could you provide some helpful code?

Thanks!

This is from one of my programs :

Code:

---

    Private Sub btnAdvSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdvSearch.Click

 Try

            Dim oSelCmd As OleDbCommand = New OleDbCommand
            oSelCmd.CommandType = CommandType.Text
            oSelCmd.Connection = oledbAdvSCon
            oSelCmd.CommandText = AppSearchField1 & AppSearchField2 & AppSearchField3
            Console.WriteLine(AppSearchField1 & AppSearchField2 & AppSearchField3)
            oledbAdvSCon.Open()
            Dim oDr As OleDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)

End Sub

    Private Sub cboAdvFieldName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboAdvFieldName.SelectedIndexChanged
        AppSearchField1 = "SELECT * FROM StudentInfo WHERE "

        Select Case cboAdvFieldName.SelectedIndex
            Case 0
                AppFieldName = "StudentNo"
                AppSearchField2 = "StudentNo LIKE '%" & AppSearchValue1 & "%'"
        End Select
    End Sub

---

It might be helpful. If you don't understand what was done, let me know, and I could work something out

Thanks for your input HannesTheGreat but I do not understand what the oSelCmd are for? As I said I'm new to VB.NET so any explanations of the code would help.

I do have my connections set up in a public class so can I just call them instead of using your first block of code?

I did however follow the sequence of events in the second sub group of code. That all makes sense to me. But how to get there???

Thanks again!

The oSelCmd is the Command that needs to execute in SQL, so basically that is the actual query that needs to fire. Because we are building the query dynamically, we need the Command object. Do you mind showing your Connections that you have set up - then I'll have a better understanding about your code.

hang in there

'Database connection variables
Private da As SqlDataAdapter
Private conn As SqlConnection
Private bsource As BindingSource = New BindingSource()
Private ds As DataSet = Nothing
Private udtSQLCmd As String

'Database connection for the BoM Database
Private sBoMConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=BoM;Persist Security Info=True;User ID=****;Password=****"

Private sCMMSConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=CMMS;Persist Security Info=True;User ID=****;Password=****"


Thanks again for the help!

P.S. Part fo the reason I am having these difficulties is that I am finishing a project started by another person who was also a beginner haha

Don't worry, it happens :)

OK, this is just written off the cuff, but it should point you into the right direction. I combined your code, with my code :

Code:

---

'Database connection variables
Private da As SqlDataAdapter
Private conn As SqlConnection
Private bsource As BindingSource = New BindingSource()
Private ds As DataSet = Nothing
Private udtSQLCmd As String

'Database connection for the BoM Database
Private sBoMConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=BoM;Persist Security Info=True;User ID=****;Password=****"

Private sCMMSConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=CMMS;Persist Security Info=True;User ID=****;Password=****"


Private Sub FilterMe()               
        Dim oSelCmd As OleDbCommand = New OleDbCommand
        oSelCmd.CommandType = CommandType.Text
        oSelCmd.Connection = conn
        oSelCmd.CommandText = udtSQLCmd

        udtSQLCmd = "SELECT * FROM Motor WHERE Voltage LIKE '%" & cboMotorVoltage.SelectedIndex.ToString() & "%'"

        conn.Open()
        Dim oDr As OleDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)
End Sub


    Private Sub cboMotorVoltage_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboMotorVoltage.SelectedIndexChanged

        FilterMe()

    End Sub

---

Does that help?

HanneSThEGreaT,

You are awesome. I think I can figure it out from here as far as customizing my code further but if I have any questions I will post in here.

Thanks!

No problem, I hope you come right. Good luck :thumb:

While modifying my code I realized something that confused me. How is this code dynamically pulling information from the DB if the Query is the same every time.

:::::If I choose 120 V in the first combobox then the second combobox will only display the motor types associated with 120 V.
But if I choose 240 V will the second combobox display ALL motor types regardless of voltage?

WHat I'm asking is if I would need to make multiple select queries (one for 120 V as you posted above, and one for 240 V), give them different names (240Vfilterme()) and then call the newly named filters in a conditional statement within the cboMotorVoltage_selectedindexchanged sub?

Or maybe I just have no idea what I'm talking about. haha

Quote:

---

Originally Posted by S_John

While modifying my code I realized something that confused me. How is this code dynamically pulling information from the DB if the Query is the same every time.

---

The query is the same, but the data is different based on each combobox selection. That is the beauty of the LIKE SQL statement.

Quote:

---

Originally Posted by S_John

WHat I'm asking is if I would need to make multiple select queries (one for 120 V as you posted above, and one for 240 V), give them different names (240Vfilterme()) and then call the newly named filters in a conditional statement within the cboMotorVoltage_selectedindexchanged sub?

---

If you want to narrow the results down further, you do not need another sub. We can build it into the exiting one, with the help of the SELECT CASE conditional statement. Then, based on selection, we'll get those results. But i don't think there is any need for that at this stage...

Quote:

---

Originally Posted by S_John

Or maybe I just have no idea what I'm talking about. haha

---

Don't worry about it :)
Give it a try

I am almost finished with this code. I am on the final line (Dim oDr As OleDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)) and I'm thrown an error when I try to run it. The error reads, "Dim oDr As OleDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)".

I googled the error and was led to multiple forums and the only fix for them was to put the Select statement above the error line...But that was already implied by your code example so that's how I have it. Any ideas where I might be going wrong?

Thanks again!

Hello again. That's bad news :(

OK, I had a quick look and it seems that I overlooked a couple of minor things. Sorry. It happens when you're coding the whole day...

Anyways I did a couple of changes

Code:

---

'Database connection variables
Private da As SqlDataAdapter
Private conn As SqlConnection
Private bsource As BindingSource = New BindingSource()
Private ds As DataSet = Nothing
Private udtSQLCmd As String

'Database connection for the BoM Database
Private sBoMConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=BoM;Persist Security Info=True;User ID=****;Password=****"

Private sCMMSConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=CMMS;Persist Security Info=True;User ID=****;Password=****"


Private Sub FilterMe()       
        conn = new SQLConnection(sBoMConnectionString) '<------ CHANGED HERE

        udtSQLCmd = "SELECT * FROM Motor WHERE Voltage LIKE '%" & cboMotorVoltage.SelectedIndex.ToString() & "%'" '<--- MOVED THIS UP
       
        Dim oSelCmd As OleDbCommand = New OleDbCommand
        oSelCmd.CommandType = CommandType.Text
        oSelCmd.Connection = conn
        oSelCmd.CommandText = udtSQLCmd

        conn.Open()
        Dim oDr As SQLDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default) '<---- CHANGED TO SQL DATA READER
End Sub


    Private Sub cboMotorVoltage_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboMotorVoltage.SelectedIndexChanged

        FilterMe()

    End Sub

---

Have a look at this and see if this works.

Have a good day

This is my version of what you gave to me (I had already made the changed to calling the SQL DB):

Code:

---

Private Sub FilterMe()
        Using conn As New SqlConnection(sBoMConnectionString)
            udtSQLCmd = "SELECT * FROM Motors WHERE Voltage LIKE '%" & cboMotorVoltage.SelectedIndex.ToString() & "%'"
            Dim oSelCmd As SqlCommand = New SqlCommand
            oSelCmd.CommandType = CommandType.Text
            oSelCmd.Connection = conn
            oSelCmd.CommandText = udtSQLCmd
            conn.Open()
            Dim oDr As SqlDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)
        End Using
    End Sub
'***************CBO SELECTED CHANGED***********
Private Sub cboMotorVoltage_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboMotorVoltage.SelectedIndexChanged
        'Disables controls in Motor Type unless Motor Voltage is selected
        If cboMotorVoltage.Text <> String.Empty Then
            cboMotorHP.Enabled = True
            FilterMe()
        Else
            cboMotorHP.Enabled = False
            spnMotorQTY.Enabled = False
        End If
    End Sub

---

While this code runs without error it still posts all values for the second combobox. Am I calling the procedure FilterMe() in the wrong place within the cbo change?

Thanks again :)

OK, at least it is progress :)

Do you know how to set a breakpoint?

If so, set one at this line :

Code:

---

udtSQLCmd = "SELECT * FROM Motors WHERE Voltage LIKE '%" & cboMotorVoltage.SelectedIndex.ToString() & "%'"

---

Then step into the next line. Use your mouse and hover over udtSQLCmd and report here what it shows

I always forget about that feature. :S
This is what it is trying to find in the line, Select * from Motors where Voltage Like '%0%'. So apparently it is not pulling the correct value from the 1st (Voltage) combobox. Is it the placement of the procedure call?

I think he want TWO DIFFERENT FILTER commands, based on whether or not one of the combo's are EMPTY

Quote:

---

Originally Posted by dglienna

I think he want TWO DIFFERENT FILTER commands, based on whether or not one of the combo's are EMPTY

---

That is partly correct. I was under the impression that one command should do the trick because the 2nd combobox can be filled with values depending upon the generic command:
SELECT * FROM Motors WHERE Voltage LIKE '%(result of combobox1)%'

This should pull the correct data values from the DB because of the databinding of the combobox.???? Or is the command nullified by the databinding of the combobox?

Just say IF BLANK then X, if Not BLANK, do Y. Then check for X and Y and fill either /or.

Should be able to do it in 2-3 IF statements. Create a BOOLEAN or two.

S_John

I have done it! :D

Right, first off, I was stupid. That is one problem when you're over worked, and over - used your brain...

anyways. I cannot comprehend why I never created a new datatable ( for our filtered results ) and never made use of a paramater. Again, I apologise - you could have been finished with this, If my brain wasn't needing a FORMAT and RELOAD .

Have a look here :

Code:

---

Imports System.Data.OleDb

Public Class Form1
    'Database connection variables
    Private da As OleDbDataAdapter
    Private conn As OleDbConnection
    Private bsource As BindingSource = New BindingSource()
    Private ds As DataSet = Nothing
    Private udtSQLCmd As String

    'Database connection for the BoM Database
    Private sBoMConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=BoM;Persist Security Info=True;User ID=****;Password=****"
    Private sCMMSConnectionString As String = "Data Source=OMDC-SCADADEV1;Initial Catalog=CMMS;Persist Security Info=True;User ID=****;Password=****"


    Private Sub FilterMe()

        conn = New OleDbConnection(sBoMConnectionString)

        udtSQLCmd = "SELECT * FROM Motor WHERE HorsePower = @HorsePower" '+ cboMotorVoltage.SelectedValue + "'" 'ADDED PARAMATER INTO QUERY AND REMOVED LIKE

        Dim oSelCmd As OleDbCommand = New OleDbCommand
        oSelCmd.Parameters.Add("@HorsePower", OleDbType.VarChar, 20).Value = cboMotorVoltage.SelectedValue.ToString 'CREATED PARAMETER HERE
        oSelCmd.CommandType = CommandType.Text
        oSelCmd.Connection = conn
        oSelCmd.CommandText = udtSQLCmd
        Dim dt As New DataTable 'DATATABLE TO STORE OUR FILTERED RESULTS
        conn.Open()

        dt.Load(oSelCmd.ExecuteReader)
        cboMotorHP.DisplayMember = "HorsePower"
        cboMotorHP.DataSource = dt 'SET DATA SOURCE TO NEW DATATABLE

    End Sub


    Private Sub cboMotorVoltage_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboMotorVoltage.SelectedIndexChanged

        FilterMe()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'THIS ASSUMES THAT YOU HAVE ADDED THE DATA CONTROLS AT DESIGN TIME
        'TODO: This line of code loads data into the 'VoltagesDataSet1.Motor' table. You can move, or remove it, as needed.
        Me.MotorTableAdapter.Fill(Me.VoltagesDataSet1.Motor)
        'TODO: This line of code loads data into the 'VoltagesDataSet.Motor' table. You can move, or remove it, as needed.


    End Sub

End Class

---

You just need to set the ValueMember Property of the cboMotorVoltage combobox to Voltage

Once again, I apologise. It happens :)

Let me know how it goes!!! :D

Hannes

Quote:

---

Originally Posted by dglienna

Just say IF BLANK then X, if Not BLANK, do Y. Then check for X and Y and fill either /or.

Should be able to do it in 2-3 IF statements. Create a BOOLEAN or two.

---

Ever heared of Parameters ? ;)

Just a note, I removed the LIKE part of the SQL Statement, but that was for my practising efforts only. You could always add it back, similar to what it was ( with the paramater of course )

This worked like a charm! I will have to do some research about the properties of comboboxes before I proceed. I have about 10 more of those to make or figure out how to generalize it a bit so I can call it in other functions effectively.

Thanks Hannes!

That is great news!! :) I am happy I could have helped. Good job! :thumb:

Please mark your thread resolved if you feel the issue has been solved. See you around

Hannes