-
July 10th, 2012, 09:01 AM
#1
[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
-
July 10th, 2012, 09:45 AM
#2
Re: Combobox Filter With Databinding (VB.NET)
Do you know how to use the Like SQL Command, I believe it can be helpful?
-
July 10th, 2012, 10:11 AM
#3
Re: Combobox Filter With Databinding (VB.NET)
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.
-
July 10th, 2012, 02:13 PM
#4
Re: Combobox Filter With Databinding (VB.NET)
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!
-
July 11th, 2012, 01:07 AM
#5
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
-
July 11th, 2012, 09:20 AM
#6
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!
-
July 11th, 2012, 09:35 AM
#7
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
-
July 11th, 2012, 09:48 AM
#8
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
-
July 11th, 2012, 10:31 AM
#9
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?
-
July 11th, 2012, 10:46 AM
#10
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!
-
July 11th, 2012, 11:18 AM
#11
Re: Combobox Filter With Databinding (VB.NET)
No problem, I hope you come right. Good luck
-
July 11th, 2012, 11:25 AM
#12
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.
-
July 11th, 2012, 11:35 AM
#13
Re: Combobox Filter With Databinding (VB.NET)
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.
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...
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
Last edited by HanneSThEGreaT; July 11th, 2012 at 11:38 AM.
-
July 11th, 2012, 01:19 PM
#14
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!
-
July 12th, 2012, 01:31 AM
#15
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|