-
July 12th, 2012, 08:20 AM
#16
Re: Combobox Filter With Databinding (VB.NET)
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
Last edited by S_John; July 12th, 2012 at 08:48 AM.
-
July 12th, 2012, 08:47 AM
#17
Re: Combobox Filter With Databinding (VB.NET)
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
-
July 12th, 2012, 09:03 AM
#18
Re: Combobox Filter With Databinding (VB.NET)
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?
-
July 12th, 2012, 11:22 AM
#19
Re: Combobox Filter With Databinding (VB.NET)
I think he want TWO DIFFERENT FILTER commands, based on whether or not one of the combo's are EMPTY
-
July 12th, 2012, 11:49 AM
#20
Re: Combobox Filter With Databinding (VB.NET)
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?
-
July 13th, 2012, 12:48 AM
#21
Re: Combobox Filter With Databinding (VB.NET)
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.
-
July 13th, 2012, 03:32 AM
#22
Re: Combobox Filter With Databinding (VB.NET)
S_John
I have done it!
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!!!
Hannes
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 ?
-
July 13th, 2012, 04:08 AM
#23
Re: Combobox Filter With Databinding (VB.NET)
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 )
-
July 13th, 2012, 08:20 AM
#24
Re: Combobox Filter With Databinding (VB.NET)
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!
-
July 13th, 2012, 08:45 AM
#25
Re: Combobox Filter With Databinding (VB.NET)
That is great news!! I am happy I could have helped. Good job!
Please mark your thread resolved if you feel the issue has been solved. See you around
Hannes
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
|