How to display data from mysql base from Dropdownlist selection

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

Answers:

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.

Method 1

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:

DropDownList1.SelectedItem.Text

So,
New MySqlCommand("SELECT tbl_productid from tbl_products where tbl_productname = '" 
    + cmbProducts.SelectedItem.Text + "'", con)

Method 2

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 SelectedIndexChanged event.

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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x