[Solved] ASP.Net System.IndexOutOfRangeException: RoleId

RichardSa
 
on Jan 18, 2023 04:10 AM
884 Views

Server Error in '/' Application

System.IndexOutOfRangeException: RoleId

protected void ValidateUser(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(textUser.Text) & !string.IsNullOrEmpty(txtPassword.Text))
    {
        string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT Id FROM Users WHERE email = @email AND pass = @pass", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@email", textUser.Text.Trim());
                cmd.Parameters.AddWithValue("@pass", txtPassword.Text.Trim());
                string Id = Convert.ToString(cmd.ExecuteScalar());
                con.Close();

                if (!string.IsNullOrEmpty(Id))
                {
                    string users = "";
                    using (SqlCommand cmd1 = new SqlCommand("SELECT Id FROM UserActivation WHERE Id = @Id"))
                    {
                        cmd1.CommandType = CommandType.Text;
                        cmd1.Parameters.AddWithValue("@Id", Id);
                        cmd1.Connection = con;
                        con.Open();
                        users = Convert.ToString(cmd1.ExecuteScalar());
                        con.Close();
                    }
                    if (string.IsNullOrEmpty(users))
                    {
                        int user = 0;
                        string roles = string.Empty;
                        using (SqlCommand cmd2 = new SqlCommand("SELECT Id, RoleId FROM Users WHERE pass = @pass COLLATE SQL_Latin1_General_CP1_CS_AS AND email = @email AND pass = @pass"))
                        {
                            cmd2.CommandType = CommandType.Text;
                            cmd2.Parameters.AddWithValue("@email", textUser.Text.Trim());
                            cmd2.Parameters.AddWithValue("@pass", Encrypt(txtPassword.Text.Trim()));
                            cmd2.Connection = con;
                            con.Open();
                            //  user = Convert.ToInt32(cmd2.ExecuteScalar());
                            SqlDataReader sdr = cmd.ExecuteReader();
                            if (sdr.Read())
                            {
                                user = Convert.ToInt32(sdr["Id"]);
                                roles = Convert.ToString(sdr["RoleId"]);
                            }
                            con.Close();
                        }
                        if (user > 0)
                        {
                            Session["user"] = Id;
                            con.Open();
                            string query = "SELECT LastLogin, IsActive from Users WHERE Id = @Id";
                            using (SqlCommand cmd3 = new SqlCommand(query, con))
                            {
                                cmd3.Parameters.AddWithValue("@Id", Session["user"]);
                                Session["LastLogin"] = Convert.ToDateTime(cmd3.ExecuteScalar());

                            }
                            string UpdateLog = @"UPDATE Users SET LastLogin=@dateandtime, IsActive=@IsActive WHERE Id = @Id";
                            using (SqlCommand cmd4 = new SqlCommand(UpdateLog, con))
                            {
                                cmd4.Parameters.AddWithValue("@dateandtime", DateTime.UtcNow);
                                cmd4.Parameters.AddWithValue("@IsActive", "1");
                                cmd4.Parameters.AddWithValue("@Id", Session["user"]);
                                cmd4.ExecuteNonQuery();
                            }
                            con.Close();
                        }
                        SqlCommand cmd5 = new SqlCommand("SELECT RoleName From [RoleTable] WHERE RoleId = @RoleId", con);
                        cmd5.Parameters.AddWithValue("@RoleId", roles);
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd5);
                        sda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            // string roles = dt.Rows[0]["RoleName"].ToString().Trim().ToLower();
                            if (roles == "SuperAdmin")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/AdminFolder/AdminPage.aspx");
                            }
                            else if (roles == "Admin")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/AdminFolder/AdminPage.aspx");
                            }
                            else if (roles == "SuperUser")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/UserPage.aspx");
                            }
                            else if (roles == "user")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/UserPage.aspx");
                            }
                            else
                            {
                                Response.Redirect("~/Login.aspx");
                            }
                        }
                    }
                    else
                    {
                        dvMessage.Visible = true;
                        lblMessage.Visible = true;
                        lblMessage.ForeColor = System.Drawing.Color.Red;
                        lblMessage.Text = "Account has not been activated";
                        txtPassword.Text = "";
                        txtPassword.Focus();
                    }
                }
                else
                {
                    dvMessage.Visible = true;
                    lblMessage.Visible = true;
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Invalid Login Details";
                    txtPassword.Text = "";
                    txtPassword.Focus();
                }
            }
        }
    }
    else
    {
        dvMessage.Visible = true;
        lblMessage.Visible = true;
        lblMessage.ForeColor = System.Drawing.Color.Red;
        lblMessage.Text = "All Fields are Required";
    }
}

in below line

roles = Convert.ToString(sdr["RoleId"]);

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Jan 20, 2023 06:06 AM
on Jan 20, 2023 06:13 AM

Hi RichardSa,

Please refer below sample.I have make sample using your DataTable Data.

HTML

Login

<div class="row">
    <div class="col-sm-5">
        <div class="container-fluid">
            <br />
            <h2 class="form-signin-heading">LOGIN</h2>
            <div id="dvMessage" runat="server" visible="false" class="alert alert-danger">
                <strong><i class="fad fa-exclamation-square" aria-hidden="true"></i>&nbsp;</strong>
                <asp:Label ID="lblMessage" runat="server" />
            </div>
            <label for="txtUsername">UserName</label>
            <asp:TextBox ID="txtUsername" runat="server" CssClass="form-control" Font-Size="11pt" placeholder="UserName" Width="30%" /><br />
            <br />
            <label for="txtPassword">Password</label>
            <asp:TextBox ID="txtPassword" runat="server" TextMode="Password" CssClass="form-control" Font-Size="11pt" placeholder="Password" /><br />
            <a href="#">Forgotten Password?</a>
            <br />
            <br />
            <asp:Button ID="Button1" runat="server" CssClass="btn btn-primary" BackColor="#32657c" Text="Login" OnClick="ValidateUser" />
            <br />
            <br />
        </div>
        <br />
    </div>
</div>

AdminPage

<h1>Admin View Only</h1>

Home

<h1>Home</h1>
<asp:Label ID="lblMessage" runat="server"></asp:Label>
<hr />
<asp:LinkButton ID="lnkLogout" runat="server" Text="Logout" Font-Size="11pt" ForeColor="red" OnCommand="Logout_Command"></asp:LinkButton>

Web.Config

Write this inside system.web tag.

<authentication mode="Forms">
	<forms cookieless="UseCookies" defaultUrl="~/Home.aspx" loginUrl="~/Login.aspx" slidingExpiration="true" timeout="2880"></forms>
</authentication>
<authorization>
	<allow roles="SuperAdmin, Admin"/>
	<deny users="?"/>
</authorization>

Namespaces

Login

using System.Data;
using System.Web.Security;
using System.Configuration;
using System.Data.SqlClient;

Home

using System.Web.Security;

Code

Login

protected void Page_Load(object sender, EventArgs e)
{
    if (User.Identity.IsAuthenticated)
    {

        // if they came to the page directly, ReturnUrl will be null.
        if (String.IsNullOrEmpty(Request["ReturnUrl"]))
        {
            /* in that case, instead of redirecting, I hide the login
                controls and instead display a message saying that are
                already logged in. */
        }
        else
        {
            Response.Redirect("~/Login.aspx");
        }
    }
}

protected void ValidateUser(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(txtUsername.Text) & !string.IsNullOrEmpty(txtPassword.Text))
    {
        string connectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT UserId FROM User_Login WHERE Email = @Email AND Password = @Password", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@Email", txtUsername.Text.Trim());
                cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
                string Id = Convert.ToString(cmd.ExecuteScalar());
                con.Close();

                if (!string.IsNullOrEmpty(Id))
                {
                    string User_Login = "";
                    using (SqlCommand cmd1 = new SqlCommand("SELECT UserId FROM User_Login WHERE UserId = @UserId"))
                    {
                        cmd1.CommandType = CommandType.Text;
                        cmd1.Parameters.AddWithValue("@UserId", Id);
                        cmd1.Connection = con;
                        con.Open();
                        User_Login = Convert.ToString(cmd1.ExecuteScalar());
                        con.Close();
                    }
                    if (!string.IsNullOrEmpty(User_Login))
                    {
                        int user = 0;
                        using (SqlCommand cmd2 = new SqlCommand("SELECT UserId FROM User_Login WHERE Password = @Password AND Email = @Email AND Password = @Password"))
                        {
                            cmd2.CommandType = CommandType.Text;
                            cmd2.Parameters.AddWithValue("@Email", txtUsername.Text.Trim());
                            cmd2.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
                            cmd2.Connection = con;
                            con.Open();
                            user = Convert.ToInt32(cmd2.ExecuteScalar());
                            con.Close();
                        }
                        if (user > 0)
                        {
                            Session["user"] = Id;
                            con.Open();
                            string query = "SELECT LastLogin, IsActive from User_Login WHERE UserId = @UserId";
                            using (SqlCommand cmd3 = new SqlCommand(query, con))
                            {
                                cmd3.Parameters.AddWithValue("@UserId", Session["user"]);
                                Session["LastLogin"] = Convert.ToDateTime(cmd3.ExecuteScalar());
                            }
                            string UpdateLog = @"UPDATE User_Login SET LastLogin=@dateandtime, IsActive=@IsActive WHERE UserId = @UserId";
                            using (SqlCommand cmd4 = new SqlCommand(UpdateLog, con))
                            {
                                cmd4.Parameters.AddWithValue("@dateandtime", DateTime.UtcNow);
                                cmd4.Parameters.AddWithValue("@IsActive", "1");
                                cmd4.Parameters.AddWithValue("@UserId", Session["user"]);
                                cmd4.ExecuteNonQuery();
                            }
                            con.Close();
                        }

                        SqlCommand cmd5 = new SqlCommand("SELECT RoleName From [RoleTable] WHERE RoleId = @RoleId",con);
                        cmd5.Parameters.AddWithValue("@RoleId", Id);
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd5);
                        sda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            string role = dt.Rows[0]["RoleName"].ToString().Trim().ToLower();
                            if (role == "superadmin")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                    Response.Redirect("~/AdminFolder/AdminPage.aspx");
                            }
                            else if (role == "admin")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/AdminFolder/AdminPage.aspx");
                            }
                            else if (role == "superuser")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/Home.aspx");
                            }
                            else if (role == "user")
                            {
                                Session["user"] = Id;
                                FormsAuthentication.RedirectFromLoginPage(Id, true);
                                Response.Redirect("~/Home.aspx");
                            }
                            else
                            {
                                Response.Redirect("~/Login.aspx");
                            }
                        }
                    }
                    else
                    {
                        dvMessage.Visible = true;
                        lblMessage.Visible = true;
                        lblMessage.ForeColor = System.Drawing.Color.Red;
                        lblMessage.Text = "Account has not been activated";
                        txtPassword.Text = "";
                        txtPassword.Focus();
                    }
                }
                else
                {
                    dvMessage.Visible = true;
                    lblMessage.Visible = true;
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Invalid Login Details";
                    txtPassword.Text = "";
                    txtPassword.Focus();
                }
            }
        }
    }
    else
    {
        dvMessage.Visible = true;
        lblMessage.Visible = true;
        lblMessage.ForeColor = System.Drawing.Color.Red;
        lblMessage.Text = "All Fields are Required";
    }
}

Home

protected void Page_Load(object sender, EventArgs e)
{
    if (this.Page.User.Identity.IsAuthenticated)
    {
        lblMessage.Text = "Welcome:" + Session["user"].ToString();
    }
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.Cache.SetExpires(DateTime.Now.AddSeconds(-1));
    Response.Cache.SetNoStore();
    Response.AppendHeader("Pragma", "no-cache");
}

protected void Logout_Command(Object sender, CommandEventArgs e)
{
    if (this.Page.User.Identity.IsAuthenticated)
    {
        FormsAuthentication.SignOut();
        Session.Abandon();
        FormsAuthentication.RedirectToLoginPage();
    }
}

Screenshot

dharmendr
 
on Jan 23, 2023 07:46 AM

Refer modified code.

using (SqlCommand cmd = new SqlCommand("SELECT Id, RoleId FROM Users WHERE email = @email AND pass = @pass", con))
{
    cmd.Parameters.AddWithValue("@email", textUser.Text.Trim());
    cmd.Parameters.AddWithValue("@pass", txtPassword.Text.Trim());
    con.Open();
    SqlDataReader sdr = cmd.ExecuteReader();
    string Id = string.Empty, RoleId = string.Empty;
    if (sdr.Read())
    {
        Id = Convert.ToString(sdr["Id"]),
        RoleId = Convert.ToString(sdr["RoleId"]);
    }
    con.Close();
}