Populate checkboxes via database

Question

I have to populate checkboxes with data coming from database, but no checkboxes are showing on my page. Please let me know the correct way to do that. In C#, the page_load method I've written is this:

public partial class dbTest1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string Server = "al2222";
        string Username = "hshshshsh";
        string Password = "sjjssjs";
        string Database = "database1";

        string ConnectionString = "Data Source=" + Server + ";";
        ConnectionString += "User ID=" + Username + ";";
        ConnectionString += "Password=" + Password + ";";
        ConnectionString += "Initial Catalog=" + Database;
        string query = "Select * from Customer_Order where orderNumber = 17";

        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    if (!IsPostBack)
                    {
                        Interests.DataSource = dr;
                        Interests.DataTextField = "OptionName";
                        Interests.DataValueField = "OptionName";
                        Interests.DataBind();
                    }
                }
                conn.Close();
                conn.Dispose();
            }
        }
    }
}

And in the .aspx, I have this:

<asp:CheckBoxList ID="Interests" runat="server"></asp:CheckBoxList>

Please tell me the correct way to accomplish this.

Answer 1

Although your question is already answered (via the connection string comments), I thought I'd chime in with a possible way to rewrite this. I'd started this off as a comment, but it got a bit long and unwieldy. Note that this doesn't directly answer your question, but it is something to consider for code cleanliness and a possible (likely very mild) performance boost on postbacks.

protected void Page_Load(object sender, EventArgs e)
{
    // If we're in postback, let's not poll the database.
    if (Page.IsPostback)
        return; // Change this if you do need some postback processing here.

    // I assume that in the real world you pull this info from web.config
    string Server = "al2222";
    string Username = "hshshshsh";
    string Password = "sjjssjs";
    string Database = "database1";

    string ConnectionString = "Data Source=" + Server + ";";
    ConnectionString += "User ID=" + Username + ";";
    ConnectionString += "Password=" + Password + ";";
    ConnectionString += "Initial Catalog=" + Database;
    string query = "Select * from Customer_Order where orderNumber = 17";

    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            // Going to assume that you're only getting 1 record 
            // due to apparent key (orderNumber = 17) in query?
            // You can also consider "if (dr.Read())", but fundamentally
            // they will do the same thing.
            while (dr.Read())
            {
                Interests.DataSource = dr;
                Interests.DataTextField = "OptionName";
                Interests.DataValueField = "OptionName";
                Interests.DataBind();
            }
            // I've excised the calls to .Close() and .Dispose(),
            // as the using block covers them for you.
        }
    }
}

Why would we go this route?

1. In your original code, you were polling the database (and potentially looping, if my assumption about that being a single-record query was wrong) every page load, whether or not you were in postback. You weren't checking postback until you were inside the loop, where the damage was mostly already done. In the code I've listed, you short-circuit out of Page_Load() altogether if you're in postback. You can, of course, change that to an if/else and bracket the groups if you need some load-event processing on postbacks as well. This also simplified your in-loop code.
2. Your using blocks covered the disposal/closure of the connection for you. Thus, you do not need that additional code.
3. As OrbMan stated in the comments, hopefully in your actual code you're retrieving all the connection string info from your web.config file instead of hard-coding it, correct?

Final final unrelated note: This is a lot of data access code that newer versions of the .NET Framework simplify greatly with tools such as Entity Framework and LINQ-to-SQL. There are also 3rd-party data access layer tools (such as SubSonic and ActiveRecord) that will simplify this. Using tools such as those will greatly reduce the amount of code you're writing here -- and I'm guessing you're using quite a bit of similar code throughout your app as well, so those tools will provide you the developer with quite the productivity boost. (And much simpler down-the-road maintenance.)

Just food for thought.