help with combobox

Question

3.50/5 (2 votes)

See more:

I am new to vb. I am trying to get a value for a textbox from a database according to the selected item in combobox.
Could anyone please tell me what is wrong with the following code?

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
  Dim connstrg As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\hedris\My Documents\REGISTER.mdb;"
  Dim conn As New OleDbConnection(connstrg)
  Try
    conn.Open()
    Dim DA1 As New OleDbDataAdapter("select* FROM CLARUS where product=" & ComboBox1.SelectedItem & "", conn)
    Dim ds1 As New DataSet
    Dim dt As New DataTable
    DA1.Fill(ds1, "clarus")
    dt = ds1.Tables("clarus")
    conn.Close()
    TextBox1.Text = dt.Rows.Item(0).Item(0)
  Catch ex As Exception
    MsgBox(ex.Message)
  End Try
End Sub

Posted 11-Nov-10 1:32am

edriso

Updated 11-Nov-10 2:00am

JF2015

v2

Add a Solution

Comments

CPallini 11-Nov-10 7:56am

What's the exception you're getting?

edriso 11-Nov-10 8:30am

the ex is Operator '&' in not defined for string "select*from ......" and type 'dtarowview'

5 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Geoff Williams · Accepted Answer · 2010-11-11T03:24:00

Is the Product field in your Clarus table a numeric field or a text field? If the latter, I'm not surprised you are having a problem as you are not wrapping the value in quotes e.g.

"SELECT * FROM Clarus WHERE (Product = '" & ComboBox1.SelectedItem & "')"

In either case it would be better to use a parameterised query such as:

Dim DA1 As New OleDbDataAdapter("SELECT * FROM Clarus WHERE (Product = ?)", conn)
DA1.Parameters.AddWithValue("@Product", ComboBox1.SelectedItem)

as you then don't have to worry about what type of data is contained in the Product field.

Geoff Williams · Accepted Answer · 2010-11-11T04:04:00

In your code above you have the statement

Dim DA1 As New OleDbDataAdapter("select* FROM CLARUS where product=" & ComboBox1.SelectedItem &
"", conn)

split over two lines. is that how it appears in your source file? If so, you need to append an underscore '_' character to the end of the first line

Dim DA1 As New OleDbDataAdapter("select* FROM CLARUS where product=" & ComboBox1.SelectedItem & _
"", conn)

so the VB.NET compiler knows that the statement is continued on the second line.

Geoff Williams · Accepted Answer · 2010-11-11T04:24:00

Solution 5

The SelectedItem property of a ComboBox is an Object not a String. To use it as part of a string concatenation, you need to explicitly invoke the ToString method:

"select* FROM CLARUS where product = '" & ComboBox1.SelectedItem.ToString() & "'"

Posted 11-Nov-10 4:24am

Geoff Williams

Updated 11-Nov-10 4:38am

v2

Comments

Scubapro 11-Nov-10 10:39am

Revised your answer (still gonna need the quotes). And indeed, that should be it.

edriso 11-Nov-10 10:51am

hi Geoff
now i have another ex.message ( there is no row at position (0) )
thanks

Geoff Williams 11-Nov-10 10:57am

Have you changed the assignment to TextBox1.Text to

TextBox1.Text = dt.Rows.Item(0).ToString()

as mentioned in one of the earlier answers?

edriso 12-Nov-10 4:37am

I did that, but still the same.

Geoff Williams 12-Nov-10 4:43am

Then presumably the Select query is not returning any records. You'll need to replace the line

TextBox1.Text = dt.Rows.Item(0).ToString()

with something like

If (dt.Rows.Count > 0) Then
TextBox1.Text = dt.Rows.Item(0).ToString()
Else
TextBox1.Text = String.Empty ' or an appropriate message
End If

edriso 12-Nov-10 4:55am

but in this case the code return no value from database whatever the selected item in combobox was

Geoff Williams 12-Nov-10 5:01am

Then it looks like the problem lies with the list of items you have in your combobox.

edriso 12-Nov-10 5:06am

the listed items comes from the same table from database see below
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim connstrg As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\hedris\My Documents\REGISTER.mdb;"
Dim conn As New OleDbConnection(connstrg)
Try
conn.Open()
Dim DA As New OleDbDataAdapter("select* FROM CLARUS", conn)
Dim ds As New DataSet
DA.Fill(ds, "clarus")
ComboBox1.DisplayMember = "clarus.product"
ComboBox1.DataSource = ds
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Geoff Williams 12-Nov-10 5:16am

Did you remember to add the single-quote characters to the Select string in your ComboBox event handler?

edriso 12-Nov-10 5:23am

did you mean in here
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

Dim connstrg As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\hedris\My Documents\REGISTER.mdb;"
Dim conn As New OleDbConnection(connstrg)
Try
conn.Open()
Dim DA As New OleDbDataAdapter("select* FROM CLARUS where product='" & ComboBox1.SelectedItem.ToString() & "'", conn)
Dim ds As New DataSet
Dim dt As New DataTable
DA.Fill(ds, "clarus")
dt = ds.Tables("clarus")
If (dt.Rows.Count > 0) Then
TextBox1.Text = dt.Rows.Item(0).ToString()
Else
TextBox1.Text = " Please Add Description"
End If
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)

End Try
End Sub

edriso 12-Nov-10 5:27am

i have changed the combobox dropdown style to dropdown list to disable writing in combobox, does it afect anything?

Geoff Williams 12-Nov-10 5:37am

It should not affect anything.

It would be worth checking what the contents of your Select query are e.g.

Dim StrQuery = "SELECT * FROM Clarus WHERE (Product = '" & ComboBox1.SelectedItem.ToString() & "')"
MsgBox(strQuery)
Dim DA As New OleDbDataAdapter(strQuery, conn)

to check what you are passing to the database.

edriso 12-Nov-10 5:52am

it comes up with this message " select*WHERE(product='system.data.datarowview') "

Geoff Williams 12-Nov-10 6:09am

Is that what you are expecting?

edriso 12-Nov-10 6:23am

Well, i expect that when i select a product name from combobox the textbox will show the description for this product as is in the table, so i need one item from the table row.

Geoff Williams 12-Nov-10 7:02am

You need to modify your query string.

Dim StrQuery as String = "SELECT * FROM Clarus WHERE(Product = '" & CType(ComboBox1.SelectedItem, DataRowView).Item("Product") & "')"

When you bind a DataSet to a ComboBox, you don't bind a simple list of items, you bind a set of DataRowView items.

edriso 12-Nov-10 7:20am

Geoff,
i change the query statment as you adviced, but now textbox1 shows this text "System.Data.DataRow" not the description.

Geoff Williams 12-Nov-10 7:31am

In your assignment to TextBox1.Text, you are missing the index to the Rows collection:

TextBox1.Text = dt.Rows(0).Item(0).ToString()

edriso 12-Nov-10 7:41am

THANKS , it works now , you'r a star mate thank you for your patience

Geoff Williams 12-Nov-10 7:45am

You're welcome

Scubapro · Accepted Answer · 2010-11-11T03:39:00

Solution 3

Looks like the column 'product' in the Table 'CLARUS' has a VarChar format. So your SQL-script should be:

"select * from clarus where product = '" & ComboBox1.SelectedItem & "'"

Posted 11-Nov-10 3:39am

Scubapro

Updated 11-Nov-10 3:41am

v2

Comments

edriso 11-Nov-10 10:16am

i done that and still have the same ex.message

Scubapro 11-Nov-10 10:20am

Did you add the items in your combobox as strings?

edriso 11-Nov-10 10:53am

i amend the code to be
Dim DA1 As New OleDbDataAdapter("select* FROM CLARUS where product='" & ComboBox1.SelectedItem.ToString() & "'", conn)
but i have another ex.message now " there is no row at position (0)"

Scubapro 12-Nov-10 1:19am

This means that now the SQL-command is executed okay, but that it returns nothing. Try omitting the 'where' CLAUSE ("Select * from CLARUS") and see if this works. If yes, then make absolute sure the product exists. If no, you're problem lies else where.

Abdul Quader Mamun · Accepted Answer · 2010-11-11T01:43:00

Solution 1

TextBox1.Text = dt.Rows(0).Item(0).ToString()

Posted 11-Nov-10 1:43am

Abdul Quader Mamun

Comments

edriso 11-Nov-10 8:31am

i done that and still not working

help with combobox

5 solutions

Solution 2

Solution 4

Solution 5

Solution 3

Solution 1

Add your solution here

Preview 0

help with combobox

5 solutions

Solution 2

Solution 4

Solution 5

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us