Safri Safri - 4 months ago 10
SQL Question

How to populate RadioButtonList from my database

I have been having issues with the following problem the past 2 days.

I want to edit username "Peter Chew" role from "Staff" to "Admin". But it didn't populate the data to the radiobuttonlist from my database

Here's my database table:

click table

Here's my radiobuttonlist:

click image

My code:

namespace BookReservation.Staff
{
public partial class EditStaff : System.Web.UI.Page
{
int id = 0;
SqlConnection conn = null;
SqlCommand cmd = null;
string connectionString = null;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlDataReader dr = null;

connectionString = ConfigurationManager.ConnectionStrings["LeaveManagementCS"].ConnectionString;

conn = new SqlConnection(connectionString);

string sql = "SELECT * FROM Staff";

try
{
cmd = new SqlCommand(sql, conn);

conn.Open();

dr = cmd.ExecuteReader();

while (dr.Read())
{
ListItem item = new ListItem(dr["Title"].ToString(), dr["StaffId"].ToString());
ddlTitle.Items.Add(item);
}
dr.Close();
}
catch (Exception ex)
{
lblOutput.Text = "Error Message:" + ex.Message;
}
finally
{
if (conn != null)
conn.Close();
}

if (Request.Params["username"] != null)
{
string username = Request.QueryString["username"];

sql = "SELECT * FROM Staff where Username=@username";

try
{
cmd = new SqlCommand(sql, conn);

cmd.Parameters.AddWithValue("@username", username);

conn.Open();

dr = cmd.ExecuteReader();

dr.Read();


lblIdOut.Text = dr["StaffId"].ToString();
tbUsername.Text = username.ToString();
tbPassword.Text = dr["Password"].ToString();
tbStaffName.Text = dr["StaffName"].ToString();
tbEmail.Text = dr["Email"].ToString();
tbPhone.Text = dr["PhoneNo"].ToString();


dr.Close();

}
catch (Exception ex)
{
lblOutput.Text = "Error Message: " + ex.Message;
}
finally
{
if (conn != null)
conn.Close();
}

}
else
{
lblOutput.Text = "Error. There is no ID to retrieve from the DB.";
}
}
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
lblMessage.Text = "";
lblMessage.Text += "Username " + tbUsername.Text + "<br />";
lblMessage.Text += "Password " + tbPassword.Text + "<br />";
lblMessage.Text += "Staff Name " + tbPassword.Text + "<br />";
lblMessage.Text += "Email " + tbEmail.Text + "<br />";
lblMessage.Text += "Phone No " + tbPhone.Text + "<br />";
lblMessage.Text += "Title " + ddlTitle.SelectedItem.Text + "<br />";
lblMessage.Text += "Role " + rblRole.SelectedItem.Text + "<br />";

connectionString = ConfigurationManager.ConnectionStrings["LeaveManagementCS"].ConnectionString;

conn = new SqlConnection(connectionString);

string sql = "UPDATE Staff SET Username=@user, Password=@Pwd, StaffName=@staff, Email=@email, PhoneNo=@phone, Title=@title, Role=@role ";
sql += " WHERE StaffId=@id";

string ddl = ddlTitle.SelectedItem.Text;
string rbl = rblRole.SelectedItem.Text;

try
{
cmd = new SqlCommand(sql, conn);

cmd.Parameters.AddWithValue("@id", lblIdOut.Text);
cmd.Parameters.AddWithValue("@user", tbUsername.Text);
cmd.Parameters.AddWithValue("@Pwd", tbPassword.Text);
cmd.Parameters.AddWithValue("@staff", tbStaffName.Text);
cmd.Parameters.AddWithValue("@email", tbEmail.Text);
cmd.Parameters.AddWithValue("@phone", tbPhone.Text);
cmd.Parameters.AddWithValue("@title", ddl);
cmd.Parameters.AddWithValue("@role", rbl);

conn.Open();

int rows = cmd.ExecuteNonQuery();

if (rows > 0)
{
lblOutput.Text = "Record update successfully";
}
}
catch (Exception ex)
{
lblOutput.Text = "Error Message: " + ex.Message;
}
finally
{
if (conn != null)
conn.Close();
}

}
}
}

Answer

To set the initial value of the RadioButtonList based on the current database value, add this line in Page_Load() between dr.Read() and dr.Close() (where you are setting the values of the other controls):

rblRole.SelectedValue = dr["Role"].ToString();

To update the database value based on the selected RadioButtonList item, change this line from:

string rbl = rblRole.SelectedItem.Text;

to this:

string rbl = rblRole.SelectedItem.Value;

The SelectedItem.Text property gets the visible text of the selected item ("Admin" or "Staff"), which is what you would use for displaying the selected item to the user. For database entry, however, you want the hidden value of the selected item ("A" or "S"), which is accessed through the SelectedItem.Value (or SelectedValue) property.