How to update the role of the user by deleting its role and inserting a new role?
<p>I have the following database structure:</p>

<p><strong>User table:</strong> Name, Username, Department (Username is the primary key)</p>

<p><strong>Roles table:</strong> RoleID, RoleName (RoleID is the primary key)</p>

<p><strong>UserRole table:</strong> UserRoleID, Username, RoleID (UserRoleID is the primary key)</p>

<p>I want to update the role of the user by deleting its current role and inserting a new role instead of using UPDATE command, but I don't know how to do that. </p>

<p>My ASP.NET Code:</p>

<pre><code>            &lt;div class="content"&gt;
                &lt;table&gt;
                    &lt;tr&gt;
                        Current Role:
                        &lt;asp:Label ID="lblRole" runat="server"&gt;&lt;/asp:Label&gt;
                    &lt;/tr&gt;
                    &lt;tr&gt;
                        &lt;asp:Label ID="Label1" runat="server" BackColor="#FFFF99" Font-Bold="True" ForeColor="#000099" /&gt;
                        &lt;asp:RadioButtonList ID="radio1" runat="server" TextAlign="left"&gt;
                            &lt;asp:ListItem id="option1" runat="server" Value="Admin" /&gt;
                            &lt;asp:ListItem id="option2" runat="server" Value="Contribute" /&gt;
                            &lt;asp:ListItem id="option3" runat="server" Value="User" /&gt;
                        &lt;/asp:RadioButtonList&gt;
                        &lt;asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Clicked" /&gt;
                        &lt;span id="infoSpan" runat="server" style="color:Red;"&gt;&lt;/span&gt;
                    &lt;/tr&gt;
                &lt;/table&gt;
            &lt;/div&gt;
</code></pre>

<p>And the C# code-behind:</p>

<pre><code>protected void Button1_Clicked(object sender, EventArgs e)
    {
        //If one of the items is selected AND a username exists in the Username session object update the user role
        if (!String.IsNullOrEmpty(radio1.SelectedValue) &amp;&amp; Session["Username"] != null)
        {
            string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psTest;Integrated Security=True";
            string cmdText = "UPDATE Roles SET RoleName = '" + radio1.SelectedValue + "'" +
                "WHERE Username = '" + Session["Username"].ToString() + "'";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    cmd.ExecuteScalar();
                    infoSpan.InnerText = String.Format("The users role has been updated to - {0}", radio1.SelectedValue);
                }
            }
        }
    }
</code></pre>

<p><strong>EDIT:</strong></p>

<p>I am not using ASP.NET Membership. And I came up with database design to make the database fully normalized. Also, because the company may ask me to give the user mare than one role at the same time. </p>
