CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jul 2012
    Posts
    46

    [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

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

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

  3. #3
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    Quote Originally Posted by HanneSThEGreaT View Post
    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.

  4. #4
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    Quote Originally Posted by HanneSThEGreaT View Post
    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!

  5. #5
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

    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

  6. #6
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    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!

  7. #7
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

    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

  8. #8
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    '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

  9. #9
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

    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?

  10. #10
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    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!

  11. #11
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

    No problem, I hope you come right. Good luck

  12. #12
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    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
    Last edited by S_John; July 11th, 2012 at 11:31 AM.

  13. #13
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

    Quote Originally Posted by S_John View Post
    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 View Post
    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 View Post
    Or maybe I just have no idea what I'm talking about. haha
    Don't worry about it
    Give it a try
    Last edited by HanneSThEGreaT; July 11th, 2012 at 11:38 AM.

  14. #14
    Join Date
    Jul 2012
    Posts
    46

    Re: Combobox Filter With Databinding (VB.NET)

    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!

  15. #15
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Combobox Filter With Databinding (VB.NET)

    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

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured