i want to display mysql data base from the dropdownlist. i populate data from dropdownlist using this code and it works perfectly. in this code it will show the productnames in the dropdownlist
If Not Me.IsPostBack Then Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString Using con As New MySqlConnection(constr) Using cmd As New MySqlCommand("SELECT tbl_productid,tbl_productname FROM tbl_products") cmd.CommandType = CommandType.Text cmd.Connection = con con.Open() cmbProducts.DataSource = cmd.ExecuteReader() cmbProducts.DataTextField = "tbl_productname" cmbProducts.DataValueField = "tbl_productid" cmbProducts.DataBind() con.Close() End Using End Using cmbProducts.Items.Insert(0, New ListItem("Select Product")) End If
Now base from the selected product name i want to display its productID to textbox. but this code gives me no output? i dont know what is wrong with my code anyone who can help me
This is code
Protected Sub cmbProducts_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles cmbProducts.SelectedIndexChanged 'MsgBox("Hellow World!", MsgBoxStyle.Critical) Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString Dim con As New MySqlConnection(constr) con.Open() Dim cmd As New MySqlCommand("SELECT tbl_productid from tbl_products where tbl_productname = '" + cmbProducts.Text + "'", con) Dim sda As New MySqlDataAdapter(cmd) 'Dim dr As New MySqlDataReader Dim dr As MySqlDataReader dr = cmd.ExecuteReader If dr.Read Then txtProductID.Text = dr.GetValue(0) End If con.Close() con.Dispose() End Sub
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Ok, a few things.
The drop list has two columns. data value (id), and data text.
What you have looks good.
However, when you get/grab/use the drop list, then you have this:
DropDownList1.Text - this will return the data value (1st column) DropDownList1.SelectedValue - this will ALSO return data value (1st column) DropDownList1.SelectedItem.Text - this gets the 2nd display text value (2nd column)
So, because a LOT of drop lists can be only one column, then the .text and .SelectedValue can both be used. (in other words, you can use .text, but it gets the first value, and since a lot of drop lists might only have one column, then .text always gets that first value). But I would consider the habit of SelectedValue for the column that drives the drop list.
In your case, you really do want the 2nd column, and thus you want to use:
New MySqlCommand("SELECT tbl_productid from tbl_products where tbl_productname = '" + cmbProducts.SelectedItem.Text + "'", con)
I used my own data to demonstrate. Also I used given control names in my test program. This is not what your want to do in your application. Your control names are good.
I broke the code into the data access part and the user interface code. There is very little code in the actual event procedure.
You have set the
.DataValueField to the id so you can retrieve that value in the
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not IsPostBack Then FillDropDownList() End If End Sub Private Sub FillDropDownList() Dim dt = GetListBoxData() DropDownList1.DataTextField = "Name" DropDownList1.DataValueField = "ID" DropDownList1.DataSource = dt DropDownList1.DataBind() End Sub Private Function GetListBoxData() As DataTable Dim dt = New DataTable Dim Query = "Select Top 10 ID, Name FROM Coffees;" Using cn As New SqlConnection(ConStr), cmd As New SqlCommand(Query, cn) cn.Open() dt.Load(cmd.ExecuteReader) End Using Return dt End Function Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged TextBox1.Text = DropDownList1.SelectedValue End Sub
There is no need to make a second round trip to the database. You already have the data you require.