Click here to Skip to main content
16,016,306 members
Please Sign up or sign in to vote.
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
Updated 11-Nov-10 2:00am
v2
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'

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() & "'"
 
Share this answer
 
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
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.
 
Share this answer
 
Comments
edriso 11-Nov-10 10:13am    
it is in one line in my source file
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 & "'"
 
Share this answer
 
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.
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.
 
Share this answer
 
Comments
edriso 11-Nov-10 9:55am    
thanks for your answer, i amend the code but still have the same ex.message, i forgot to mention that the combobox items comes from the same table does it make any conflection.
thanks
TextBox1.Text = dt.Rows(0).Item(0).ToString()
 
Share this answer
 
Comments
edriso 11-Nov-10 8:31am    
i done that and still not working

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900