How can I make a DropDownList from the database?-CodePudding

When I choose any option from the dropdown list and then insert it into the database, the first option is chosen automatically, even if I choose the second or third option; only the first option is inserted each time.

`Order.aspx.cs`

protected void selectList()
        {
            conn = new SqlConnection(connstr);
            conn.Open();
            sql = "SELECT * FROM Product";
            SqlCommand comm = new SqlCommand(sql, conn);
            adap = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            adap.Fill(ds);
            ProductID.DataTextField = ds.Tables[0].Columns["Name"].ToString();
            ProductID.DataValueField = ds.Tables[0].Columns["Id"].ToString();
            ProductID.DataSource = ds.Tables[0];
            ProductID.DataBind();
        }

    protected void Page_Load(object sender, EventArgs e)
        {
            bindGrid();
            selectList();

        }

     protected void btnAdd_Click(object sender, EventArgs e)
        {
            selectList();
            sql = "INSERT INTO [Order] (CustomerID,ProductID ,EmployeeID,Quantity,Date) VALUES ('"   CustomerID.Text   "','"   ProductID.SelectedValue   "','"   EmployeeID.Text   "','"   Quantity.Text   "','"   Date.Text    "')";
            conn = new SqlConnection(connstr);
            conn.Open();
            comm = new SqlCommand(sql, conn);
            comm.ExecuteNonQuery();
            conn.Close();
            bindGrid();
            ScriptManager.RegisterStartupScript(Page, Page.GetType(),
                "myPrompt", "alert('Successfully Updated!');", true);
        }

`Order.aspx`

   Product ID:
         <asp:DropDownList ID="ProductID" runat="server"  CssClass="form-control" ></asp:DropDownList>

CodePudding user response：

Page_Load() is too late to bind your list.

Remember, when using web forms, you start from scratch and have to recreate all of your data items on every single request... even simple button click events^*. This is why you call bindGrid() in the Page_Load() method. However, part of this process also involves restoring ViewState, so the button click will know what item was selected. The problem is this ViewState data is restored before the Page_Load() method is called. Therefore the grid is still empty, and the SelectedValue information you need to get from ViewState cannot be set correctly.

You can fix this by moving the code that binds your grid data up to the Init or Pre_Init events.

While I'm here, I need to reiterate my comment about SQL Injection. This is a really big deal... the kind of thing that's too important to do wrong even with learning and proof-of-concept projects. I suggest using Google to learn more about using parameterized queries with C#.

Additionally, it's rare to insert selections directly into an Orders table. Often there's a separate "ShoppingCart" table, using a Session Key for the table's primary key, where the user can build up the cart before completing the order and creating the final Order and OrderLines or OrderDetail records.

_{* For this reason, it's often worthwhile in web forms to do more of this work on the client browser, in javascript.}