combobox selected value connected to a SQL statement

Question

kavithabhaskar 0 Junior Poster

16 Years Ago

Hi I have a prob with my code.
This way I get to see all the fields in table1 in the grid view but they hold no values! :( if u can help me make changes in this code..it will great!

Imports System.Data.oledb

Public Class Form1

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\USERS.mdb")

Dim cmd As OleDbCommand

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

cmd = New OleDbCommand("select * from table1", con)

con.open()

ComboBox1.Items.Add("DELL")

ComboBox1.Items.Add("HP")

ComboBox2.Items.Add("HP xw6600 WS")

ComboBox2.Items.Add("HP xw6400 WS")

ComboBox2.Items.Add("HP xw4400 WS")

ComboBox2.Items.Add("HP Precision WS 670")

ComboBox2.Items.Add("HP Precision WS 690")

ComboBox2.Items.Add("HP Precision WS 650")

ComboBox2.Items.Add("HP Precision WS 470")

ComboBox2.Items.Add("HP Precision WS M65")

ComboBox2.Items.Add("HP Precision WS 530")

ComboBox2.Items.Add("HP Precision WS 550")

ComboBox3.Items.Add("1 GB")

ComboBox3.Items.Add("2 GB")

ComboBox3.Items.Add("3 GB")

ComboBox3.Items.Add("3.25 GB")

ComboBox3.Items.Add("8 GB")

ComboBox4.Items.Add("NVIDIA QUADRO FX4600")

ComboBox4.Items.Add("NVIDIA QUADRO FX540")

ComboBox4.Items.Add("NVIDIA QUADRO FX1500")

ComboBox4.Items.Add("NVIDIA QUADRO FX3450/4000 SDI")

ComboBox4.Items.Add("NVIDIA QUADRO 2 PRO")

ComboBox4.Items.Add("NVIDIA QUADRO FX1400")

ComboBox4.Items.Add("NVIDIA QUADRO FX3400")

ComboBox4.Items.Add("NVIDIA QUADRO 4900XGL")

ComboBox4.Items.Add("NVIDIA QUADRO 700XGL")

ComboBox4.Items.Add("3D Labs Wildcat T4 7110")

ComboBox5.Items.Add("6.14.11.6262")

ComboBox5.Items.Add("8.1.7.6")

ComboBox5.Items.Add("6.14.11.6008")

ComboBox5.Items.Add("6.6.0.35")

ComboBox5.Items.Add("8.4.2.6")

ComboBox5.Items.Add("6.1.2.9")

ComboBox5.Items.Add("8.4.8.0")

ComboBox5.Items.Add("6.6.0.38")

ComboBox5.Items.Add("6.14.11.6250")

ComboBox5.Items.Add("6.5.7.3.")

ComboBox5.Items.Add("8.3.1.3")

ComboBox5.Items.Add("3.0.8.2")

ComboBox5.Items.Add("5.6.7.3")

ComboBox5.Items.Add("6.1.6.1")

ComboBox5.Items.Add("7.2.2.3")

ComboBox5.Items.Add("3.0.8.2")

ComboBox5.Items.Add("8.4.3.7")

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

cmd = New OleDbCommand("select * from table1 where MFG='& combobox1.text &' ", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

End Sub

End Class

vb.net

2 Contributors
3 Replies
901 Views
3 Days Discussion Span
Latest Post 16 Years Ago Latest Post by tuse

tuse 22 Junior Poster

16 Years Ago

cmd = New OleDbCommand("select * from table1 where MFG='& combobox1.text &' ", con) I guess this should be- cmd = New OleDbCommand("select * from table1 where MFG='" & Me.ComboBox1.SelectedItem & "'" ,con) The code you have at present checks if the value & combobox1.text & is present in the field MFG and hence you are getting no records in your Data Grid View.

Its just that you have not used the concatenation correctly.

Hope it helps!

kavithabhaskar commented: Very helpful! +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kavithabhaskar 0 Junior Poster · Answer 1 · 2008-06-23T00:53:10+00:00

Hi Tuse:
Thanks for your reply. I had observed that mistake and i rectified it. Now, when i pull down any value from combobox1 and hit the click button it works perfectly fine with the queries being displayed on my datagridview.

How do i do this for my combobox2 ? I have a set of values in combobox2 and I am sending my code along with this email. Please take a look at it and reply.

What I want to do is, even if i dont choose any value from combobox1 and just choose a value from combobox2, i still want the queries to be displayed.

It 'll be very helpful if you can correct me.

Thanks,
Kavitha.

Imports System.Data.oledb

Public Class Form1

Dim con As OleDbConnection = New OleDb.OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\USERS.mdb")

Dim cmd As OleDbCommand

Dim cmd1 As OleDbCommand

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

con.open()

ComboBox1.Items.Add("DELL")
ComboBox1.Items.Add("HP")

ComboBox2.Items.Add("HP xw6600 WS")
ComboBox2.Items.Add("HP xw6400 WS")
ComboBox2.Items.Add("HP xw4400 WS")
ComboBox2.Items.Add("HP Precision WS 670")
ComboBox2.Items.Add("HP Precision WS 690")
ComboBox2.Items.Add("HP Precision WS 650")
ComboBox2.Items.Add("HP Precision WS 470")
ComboBox2.Items.Add("HP Precision WS M65")
ComboBox2.Items.Add("HP Precision WS 530")
ComboBox2.Items.Add("HP Precision WS 550")

ComboBox3.Items.Add("1 GB")
ComboBox3.Items.Add("2 GB")
ComboBox3.Items.Add("3 GB")
ComboBox3.Items.Add("3.25 GB")
ComboBox3.Items.Add("8 GB")

ComboBox4.Items.Add("NVIDIA QUADRO FX4600")
ComboBox4.Items.Add("NVIDIA QUADRO FX540")
ComboBox4.Items.Add("NVIDIA QUADRO FX1500")
ComboBox4.Items.Add("NVIDIA QUADRO FX3450/4000 SDI")
ComboBox4.Items.Add("NVIDIA QUADRO 2 PRO")
ComboBox4.Items.Add("NVIDIA QUADRO FX1400")
ComboBox4.Items.Add("NVIDIA QUADRO FX3400")
ComboBox4.Items.Add("NVIDIA QUADRO 4900XGL")
ComboBox4.Items.Add("NVIDIA QUADRO 700XGL")
ComboBox4.Items.Add("3D Labs Wildcat T4 7110")

ComboBox5.Items.Add("6.14.11.6262")
ComboBox5.Items.Add("8.1.7.6")
ComboBox5.Items.Add("6.14.11.6008")
ComboBox5.Items.Add("6.6.0.35")
ComboBox5.Items.Add("8.4.2.6")
ComboBox5.Items.Add("6.1.2.9")
ComboBox5.Items.Add("8.4.8.0")
ComboBox5.Items.Add("6.6.0.38")
ComboBox5.Items.Add("6.14.11.6250")
ComboBox5.Items.Add("6.5.7.3.")
ComboBox5.Items.Add("8.3.1.3")
ComboBox5.Items.Add("3.0.8.2")
ComboBox5.Items.Add("5.6.7.3")
ComboBox5.Items.Add("6.1.6.1")
ComboBox5.Items.Add("7.2.2.3")
ComboBox5.Items.Add("3.0.8.2")
ComboBox5.Items.Add("8.4.3.7")

MsgBox("please select any combination which could be just one value or more than one of your choice and hit the click button")

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

cmd = New OleDbCommand("select * from table1 where Mfg='" & ComboBox1.Text & "' ", con)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

cmd1 = New OleDbCommand("select * from table1 where Make='" & ComboBox2.Text & "' ", con)

Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

MsgBox(" If you are done with your search, please hit the exit button to exit")

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
End
End Sub
End Class

cmd = New OleDbCommand("select * from table1 where MFG='& combobox1.text &' ", con) I guess this should be- cmd = New OleDbCommand("select * from table1 where MFG='" & Me.ComboBox1.SelectedItem & "'" ,con) The code you have at present checks if the value & combobox1.text & is present in the field MFG and hence you are getting no records in your Data Grid View.
Its just that you have not used the concatenation correctly.
Hope it helps!

tuse 22 Junior Poster · Answer 2 · 2008-06-23T01:12:29+00:00

You should use the OR statement in your SQL query.

So your SQL command text should be- cmd = New OleDbCommand("select * from table1 where MFG='" & Me.ComboBox1.SelectedItem & "' OR MFG='" & Me.ComboBox2.SelectedItem & "'" ,con) I am guessing that the values from the 2nd ComboBox are also queried against the MFG field that you have in the database. If this is not so, change it accordingly as per the required field.

You can also add multiple clauses with the OR.

However if what you wish is something like this-

ComboBox1 - Value A
ComboBox2 - Value B
ComboBox3 - Value C
ComboBox4 - Value D

i.e. if you select value A in 1st box, value B in 2nd , value C for 3rd....
and it should show records where field1 in database matches Value A, field2 matches Value B .....etc, then use the AND statement