Get DropDown Values from Database Table

Question

I have a ASP:DropDown list which is populated by the following:

public void Fill1()
{
    string connectionString = WebConfigurationManager.ConnectionStrings["CRM2Sage"].ConnectionString;
    using (SqlConnection _con = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Products", _con))
    {
        cmd.Connection.Open();

        SqlDataReader ddlValues;
        ddlValues = cmd.ExecuteReader();

        txtProduct.DataSource = ddlValues;
        txtProduct.DataValueField = "Description";
        txtProduct.DataTextField = "Description";
        txtProduct.DataBind();

        cmd.Connection.Close();
        cmd.Connection.Dispose();
    }
}

I need to call the SalesPrice from same table and based on the selected value in the drop down update a ASP:TextBox Field called txtUnitAmount.

I have tried writing it into the same function and it wont let me set the .Text value for the ASP:TextBox. Can any one suggest a way to achieve this?

Thanks to all for their help so far!

Cheers

Justin

Extra Code for Discussion

public void Fill2()
    {
        string getProdValue = WebConfigurationManager.ConnectionStrings["CRM2Sage"].ConnectionString;
        using (SqlConnection _con = new SqlConnection(getProdValue))
        using (SqlCommand cmd = new SqlCommand("SELECT SalesPrice FROM Products WHERE Description = " + Request.Form["txtProduct"].ToString(), _con))
        {
            int getProdCost = (int)cmd.ExecuteScalar();
            txtUnitAmount.Text = getProdCost.ToString("###,###0.000");
        }           
    }

You don't need to dispose of the SQLCommand if you are using a using block. Remember all a using block is, is simply a try/catch with a dispose in the finally block — The Muffin Man
– The Muffin Man, Commented Mar 30, 2011 at 23:12
@Nick thanks can you post an example I am just getting to grips with c# and it would really help. Thanks — Justin Erswell
– Justin Erswell, Commented Mar 30, 2011 at 23:13
I suggest taking a look at LINQ to SQL for your database access. You could write the same logic as above with half the code. Might as well spend your time learning the new way, rather than learning ADO.NET. Regardless, whichever route you do take, I highly suggest getting comfortable with an ORM. — The Muffin Man
– The Muffin Man, Commented Mar 30, 2011 at 23:19

Slappy · Accepted Answer · 2011-03-30 23:39:04Z

1

The question is a little hard to follow, but I will give it a stab.

You going to have to do another query to the DB based on the onChnge event produced by the drop down change.

You need to wire up the OnChange event for the dropdown and enable autopostback. This can be done through the properties explorer. From the OnChangeEvent, fire your code

Note this code is far from production. You need to do input and return sanitization.

var description = Request.Form["txtProduct"].ToString();
using (SqlConnection _con = new SqlConnection(getProdValue))
using (SqlCommand cmd = new SqlCommand("SELECT UnitPrice FROM Products WHERE Description = '" + description "'", _con))
{
    cmd.Connection.Open();
    var unitPrice = cmd.ExecuteScalar();
    txtUnitPrice.Text= unitPrice.ToString("###,###0.000");       
}

edited Mar 30, 2011 at 23:39

answered Mar 30, 2011 at 23:01

Slappy

4,1022 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Justin Erswell Over a year ago

Ok I thought so if I so that and get the query setup something like this code appended above how do i then set the .text of the ASP:TextBox? as the txt.UnitPrice.DataSource = setVal errors and if I change it to .text it errors

Slappy Over a year ago

You dont need to use binding, you could executeScalar on the query to get the value of the unit price and just set the value: txtUnitPrice.Text = unitPrice.ToString("###,###0.000"), where unitPrice is a floating point variable

Justin Erswell Over a year ago

@Slappy are you able to post an example of what you mean it's late and I am new to c# and I am a bit confused. thanks so much for helping thus far

gbs Over a year ago

Yep..ExecuteScalar is what you need to get single value: csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx and then set txtUnitPrice.Text and no datasoure/databinding required.

Slappy Over a year ago

I will give it a shot. Dont have an IDE in front of me atm so it might look a little funny

|

Collectives™ on Stack Overflow

Get DropDown Values from Database Table

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related