ASP.NET Selecting and editing value in radiobuttonlist based on SQL database

I have a radiobutton list generated by the markup shown here:

<asp:RadioButtonList ID="RadioButtonList1" runat="server" 
     CellPadding="2" CellSpacing="2"  >
    <asp:ListItem>AwaitingReply</asp:ListItem>
    <asp:ListItem> Yes</asp:ListItem>
    <asp:ListItem> No</asp:ListItem>
</asp:RadioButtonList>

I am trying to use this to display values from a database and allow the user to edit the selection.

Basically when the page loads, a query string is passed with some details like this:

protected void Page_Load(object sender, EventArgs e)
{
    Session["FirstName"] = Request.QueryString["FirstName"].ToString();
    Session["LasttName"] = Request.QueryString["LastName"].ToString();
    Session["ReferenceEmail"] = Request.QueryString["ReferenceEmail"].ToString();
}

Then these values are used to import values from a database using a datasource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" 
     SelectCommand="SELECT [Endorsement], [RefNotes] FROM [AspNetUsers] WHERE (([FirstName] = @FirstName) AND ([LastName] = @LastName) AND ([ReferenceEmail] = @ReferenceEmail))">
    <SelectParameters>
        <asp:SessionParameter Name="FirstName" SessionField="FirstName" Type="String" />
        <asp:SessionParameter Name="LastName" SessionField="LastName" Type="String" />
        <asp:SessionParameter Name="ReferenceEmail" SessionField="ReferenceEmail" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Then I tried to bind this to the radiobutton list:

<asp:RadioButtonList ID="RadioButtonList1" runat="server" 
     CellPadding="2" CellSpacing="2"  
     DatasourceID="SqlDataSource1" 
     SelectedValue='<%# Bind("Endorsement") %>' >
    <asp:ListItem>AwaitingReply</asp:ListItem>
    <asp:ListItem> Yes</asp:ListItem>
    <asp:ListItem> No</asp:ListItem>
</asp:RadioButtonList>

However, the radiobutton list is not selecting the value based on the SQL database, and an error is generated:

> Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control.

So I guess I cannot use bind() or eval() with the radiobuttonlist? Any suggestion on how to solve this?

Any assistance is much appreciated.

Actually, yes, you can bind and send rows of data to a radio button list.

And even "more" amazing is you can actually send "markup" text to the button!

Anyway, so the RB can have a data source, and it works quite much like any other data bound control.

So, say I have this database list:

So, I can feed a database with this:

And then 2 RB on a page like this:

        <div style="clear: both; float: left">
            <div style="float: left; width: 120px">
                <h2>Project</h2>
            </div>
            <div style="float: left; margin-left: 1px">
                <asp:RadioButtonList ID="RProjectList" runat="server"
                    DataTextField="ProjectImage2"
                    DataValueField="ID"
                    RepeatDirection="Horizontal"
                    RepeatColumns="7">
                </asp:RadioButtonList>
            </div>
        </div>


        <div style="clear: both; float: left">
            <div style="float: left; width: 120px">
                <h2>Issue</h2>
            </div>
            <div style="float: left; margin-left: 1px">
                <asp:RadioButtonList ID="RIssues" runat="server" 
                    DataTextField="IssueImage2"
                    DataValueField="ID" 
                    RepeatDirection="Horizontal"
                    RepeatColumns="7" CellSpacing="1">
                </asp:RadioButtonList>
            </div>
        </div>

And code like this:

void LoadButtons()
{

    string strSQL =
        @"SELECT ID,ProjectImage + Project AS ProjectImage2, Project 
        FROM Projects ORDER BY ID";
    RProjectList.DataSource = General.MyRst(strSQL, Properties.Settings.Default.WebIssues);
    RProjectList.DataBind();    

    strSQL =
        @"SELECT ID, IssueImage + Issue  AS IssueImage2, Issue
        FROM Issues ORDER BY ID";
    RIssues.DataSource= General.MyRst(strSQL,Properties.Settings.Default.WebIssues);    
    RIssues.DataBind();

}

And now I get/see this:

In fact, I even often use a RB list for a menu, since when you select "one" then it stays highlighted, can be feed data, and you get click events, index changed, and even index of what button is selected.

RB lists are SO VERY often overlooked for what they can achieve, and in most cases with VERY little code.

so, don't worry - yes, you can feed/fill a RB list data. And the settings work very much like a dropdown list, or listbox.

So, like a dropdown list, or listbox?

You have 2 values. The often hidden database PK value, and then the 2nd "display" value. So, RB also work this way.

So, in fact, how data binding works for a RB list is not really any different then most data bound controls from gridviews to listview, or drop down lists? They all work very similar.

Once you learn one, then the others can be used with VERY little learning curve.

Let's address your question. OK, first issue:

In your page load event, ALWAYS but ALWAYS consider that page load fires for every post back. So, if you have code to load up a RB list, gridview, or even a simple combo box (drop down list)?

Then if you click some button, page load runs AND THEN your button code. Thus, if page load re-loads the grid, dropdown etc., then your selection will be lost!

Next up:

While a sql data source dropped into a page can be handy, they also can be a source of "pain" since often in code, it is better to have some "general" routine that returns a table based on given SQL.

So, I recommend you dump the sqldata source from the markup on the page. (its not going to help you much here - in fact it will get in your way most of the time).

So, on page load, we have some values passed.

I assume THEN you pull data from database into a "datatable" based on those value(s), and then fill out the list of choices to the RB list (by shoving in that data table to the RB list).

But, like a listbox, or dropdown list? You have "text" (the value that is displayed, and then the value that is hidden.

So, your RB should be like this:

        <asp:RadioButtonList ID="RadioButtonList1" runat="server"
            CellPadding="2" CellSpacing="2">
            <asp:ListItem Text="AwaitingReply"></asp:ListItem>
            <asp:ListItem Text="Yes"></asp:ListItem>
            <asp:ListItem Text="No"></asp:ListItem>
        </asp:RadioButtonList>

Note how I used "Text". (and we can also use Value=2. But, we don't need that 2nd value.

So, your code could be this:

void LoadButtons()
{
        string strSQL =
            @"SELECT Endorsement, RefNotes FROM AspNetUsers WHERE
            FirstName = @FirstName AND LastName = @LastName
            AND ReferenceEmail = @ReferenceEmail";

        SqlCommand cmdSQL = new SqlCommand(strSQL);
        cmdSQL.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar).Value =
            Request.QueryString["FirstName"].ToString();

        cmdSQL.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar).Value =
            Request.QueryString["LastName"].ToString();

        cmdSQL.Parameters.Add("@ReferenceEmail", System.Data.SqlDbType.NVarChar).Value =
            Request.QueryString["ReferenceEmail"].ToString();

        DataTable dt = General.MyRstP(cmdSQL);

        if (dt.Rows.Count > 0)
        {
            RadioButtonList1.Text = dt.Rows[0]["Endorsement"].ToString();
        }
}

and that "general" routine I used?

That was this in my "general" code module (static class)

    public static DataTable MyRstP(SqlCommand cmdSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
                cmdSQL.Connection = conn;
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

of course replace connection string with your correct one.

But, move your value in the RB list to Text="some text", and of course consider the optional Value="some value" (that does not display).

Also,note that in your case, we NOT filling the choices in the RB list, but only attempting to pull a choice from the database, and set "existing" and "hard coded" values in the RB list.

Since your case is NOT to fill out choices from a database?

then you could perhaps keep your datasource. However, as a general rule, while the "wizards" can create the datasource for me?

I often create the datasource, and THEN remove it! (and move that SQL to code behind). This is much due to code having "better" control over what the SQL query does, and more control over setting parameters and WHEN the query is to be loaded, or changed as a result of some event.

You can "often" try to mess with parameters of the SQL data source on the page, but in most cases, you often "lose" control as to when that datasource fires or is used. So the "automatic" part of a SQL data source in a page can be often nice, but often, it gets in your way, and you don't have great control as to when the datasource is used, or fired by a given control on the page.