Populate checkboxlist with items from database?

Question

Ok, so I want to populate/bind some data to a checkboxlist but cannot seem to binf the right values? I want to populate it with the information from a ROW, not the whole column which is what's happening to me. Here's some code anyways to show you what the problem is.

This is the code in the xaml

<form id="form1" runat="server">
<div>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [Option1], [Option2], [Option3] FROM [Questions] WHERE ([QuestionID] = @QuestionID)">
        <SelectParameters>
            <asp:Parameter DefaultValue="1" Name="QuestionID" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

</div>
    <asp:CheckBoxList ID="CheckBoxList1" runat="server" DataTextField="QuestionID" DataValueField="QuestionID">
    </asp:CheckBoxList>
</form>

---

This is my database (only an example one) Links are from gyazo

Questions

Data

---

This is my code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;

namespace ExampleCheckbox
{
public partial class Question_One : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand command = new SqlCommand();
        command.Connection = con;
        command.CommandType = CommandType.Text;
        command.CommandText = "Select * from Questions";

        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        DataSet questionsDataSet = new DataSet();


            con.Open();
            dataAdapter.Fill(questionsDataSet, "Question");

            DataTable dt = questionsDataSet.Tables["Question"];

            foreach (DataRow dr in dt.Rows)
            {
                ListItem newItem = new ListItem(dr["Option1"].ToString(), dr["QuestionID"].ToString());
                CheckBoxList1.Items.Add(newItem);
            }

            CheckBoxList1.DataSource = questionsDataSet;
            CheckBoxList1.DataTextField = "Option1";
            CheckBoxList1.DataValueField = "QuestionID";
            CheckBoxList1.DataBind();
    }
}

}

---

This is also the problem i'm having PROBLEM

Thanks

Answer 1

What you want to do is loop through the columns instead of the rows, and get the column names out instead.

Check out this question/answer here:

http://social.msdn.microsoft.com/Forums/en-US/4b6ede3b-093d-46f1-8766-d4a96608997d/loop-thru-a-datatable-for-columnnames-columnvalues?forum=csharpgeneral

Here's a full example. This is what your code should look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;

namespace ExampleCheckbox
{
public partial class Question_One : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand command = new SqlCommand();
        command.Connection = con;
        command.CommandType = CommandType.Text;
        command.CommandText = "Select * from Questions";

        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        DataSet questionsDataSet = new DataSet();


            con.Open();
            dataAdapter.Fill(questionsDataSet, "Question");

            DataTable dt = questionsDataSet.Tables["Question"];

            int i = 0;
            string str1 = string.Empty;
        int i = 0;
                    dr = dt.Rows(ClientID);   //whatever you're using for the row index
            foreach (DataColumn dc in dt.Columns)
            {
                ListItem newItem = new ListItem(dr[dc].ToString(), i.ToString());
                CheckBoxList1.Items.Add(newItem);
                i++;
            }
        }
    } 
        } 

}