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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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