How to get value of DataTable Row in C# asp.net

Question

i am learning asp.net with c# by myself, and i have a problem with DataRows, in db i have users table and there is isadmin column which value is int, i want to redirect users to different page and admins to admin page, but the problem is all users redirects to admin page.

Here is my code;

 protected void btnLogin_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(conString);
        conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT username, pass FROM users 
                                where username = '"+txtUser.Text+"'
                                and pass='"+txtPass.Text+"'"
                                , conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        SqlCommand cmd1 = new SqlCommand("Select username, isadmin From users", conn);
        SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
        DataTable dt1 = new DataTable();
        da1.Fill(dt1);

        conn.Close();
        if (dt.Rows.Count > 0)
        {
            Session["id"] = txtUser.Text;
            if (dt1.Rows[0]["isadmin"].ToString() == "1")
            {
                Response.Redirect("~/admin.aspx");
            }
            else
            {
                Response.Redirect("~/default.aspx");
            }


            //Response.Redirect("~/default.aspx");

            Session.RemoveAll();
        }
        else
        {
            lblMsg.ForeColor = System.Drawing.Color.Red;
            //lblMsg.Text= msg ;

                /*Response.Write("<script>
                alert('Please enter valid Username and Password')
                </script>"); */
        }

Can you please tell me what is wrong?

why do you have 2 different query that queries to same table? — Badiparmagi
– Badiparmagi, Commented Jan 19, 2017 at 8:29
seems like all users which you are fetching have isadmin column value 1. — Afnan Ahmad
– Afnan Ahmad, Commented Jan 19, 2017 at 8:29
hi have you tried have you looked into data of the database . Also can you print the value of dt1.Rows[0]["Isadmin"] to see what is output ? — Yashveer Singh
– Yashveer Singh, Commented Jan 19, 2017 at 8:29
Always use parametrized queries not string concatenation to build your sql qeueries. Otherwise you are open for sql injection(among other issues). — Tim Schmelter
– Tim Schmelter, Commented Jan 19, 2017 at 8:32
dt is filled via "SELECT username, pass" and with this does NOT contain IsAdmin. The query for dt has to be extended by the IsAdmin column. — Tyron78
– Tyron78, Commented Jan 19, 2017 at 8:36

Jeremy Thompson · Accepted Answer · 2017-01-19 08:36:59Z

3

Use the first query with dt as it's based on a single user. The problem is dt1 gets all users and the first record in that datatable is an admin

if (dt.Rows[0]["isadmin"].ToString() == "1") {

Remove the second query with dt1 and make sure you add isadmin to the first SQL query.

SqlCommand cmd = new SqlCommand("SELECT username, pass, isadmin FROM users where username = @UserName and pass= @Pass", conn);

See how I use parameterized username and password, that is to protect against SQL injection, definitely read up on that!!!

edited Jan 19, 2017 at 8:36

answered Jan 19, 2017 at 8:31

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

washaq Over a year ago

how can i try to get isadmin value from dt?

Jeremy Thompson Over a year ago

Add the IsAdmin field to the first SQL query, I gave you all the reasons this doesn't work to try and teach you so you can learn to work this out yourself in future, Zohar gave you a copy/paste code answer that gives it to you without any effort. Give a man a fish, feed him for a day, teach a man how to fish and feed him for life. When debugging you have to go after the bug like a dog fetching a bone, good luck mate

Jignesh Hirpara · Accepted Answer · 2017-01-19 08:33:41Z

Please Try this

protected void btnLogin_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(conString);
    conn.Open();
    SqlCommand cmd =
        new SqlCommand(
            "SELECT username, pass, isadmin FROM users where username = '" + txtUser.Text + "' and pass='" + txtPass.Text +
            "'", conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);

    conn.Close();
    if (dt.Rows.Count > 0)
    {
        Session["id"] = txtUser.Text;
        if (dt.Rows[0]["isadmin"].ToString() == "1")
        {
            Response.Redirect("~/admin.aspx");
        }
        else
        {
            Response.Redirect("~/default.aspx");
        }


        //Response.Redirect("~/default.aspx");

        Session.RemoveAll();
    }
    else
    {
        lblMsg.ForeColor = System.Drawing.Color.Red;
        //lblMsg.Text= msg ;

        //Response.Write("<script>alert('Please enter valid Username and Password')</script>");
    }
}

Afnan Ahmad · Accepted Answer · 2017-01-19 08:34:21Z

In your first query you need to get isadmin also and on the base of that result you can check either it is 1 or not and can redirect to what ever page you like. So it will be as follow:

protected void btnLogin_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(conString);
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT username, pass, isadmin FROM users where username = '"+txtUser.Text+"' and pass='"+txtPass.Text+"'", conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    conn.Close();
    if (dt.Rows.Count > 0)
    {
        Session["id"] = txtUser.Text;
        if (dt.Rows[0]["isadmin"].ToString() == "1")
        {
            Response.Redirect("~/admin.aspx");
        }
        else
        {
            Response.Redirect("~/default.aspx");
        }
        //Response.Redirect("~/default.aspx");
        Session.RemoveAll();
    }
    else
    {
        lblMsg.ForeColor = System.Drawing.Color.Red;
        //lblMsg.Text= msg ;
        //Response.Write("<script>alert('Please enter valid Username and Password')</script>");
    }
}

Zohar Peled · Accepted Answer · 2017-01-19 08:55:39Z

0

There are several things wrong with your code:

All users are redirected to the admin page since you are checking the isAdmin in the wrong query. Your second query has no where clause which means it will return all the users in the table. The first user it returns has the isAdmin value of 1.
You don't actually need two queries, just one.
You must use parameterized queries, otherwise you are leaving an open door to SQL injection attacks.
wrap all IDisposable instances in a using statement.

Your code should look more like this:

protected void btnLogin_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    using(SqlConnection conn = new SqlConnection(conString))
    {
        using(SqlCommand cmd = new SqlCommand("SELECT username, pass, isadmin FROM users where username = @UserName and pass=@Pass", conn))
        {
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = txtUser.Text;
            cmd.Parameters.Add("@Pass", SqlDbType.VarChar).Value = txtPass.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
        }   

    }
    if (dt.Rows.Count > 0)
    {
        Session["id"] = txtUser.Text;
        if (dt1.Rows[0]["isadmin"].ToString() == "1")
        {
            Response.Redirect("~/admin.aspx");
        }
        else
        {
            Response.Redirect("~/default.aspx");
        }


        //Response.Redirect("~/default.aspx");

        Session.RemoveAll();
    }
    else
    {
        lblMsg.ForeColor = System.Drawing.Color.Red;
        //lblMsg.Text= msg ;

        //Response.Write("<script>alert('Please enter valid Username and Password')</script>");
    }

}

edited Jan 19, 2017 at 8:55

answered Jan 19, 2017 at 8:32

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

4 Comments

Simon Price Over a year ago

this should be a comment not an answer

washaq Over a year ago

You are right value is always 1, i was trying many things to solve the problem, this is my last try, i will try to parametrize the query to avoid sql injection.

washaq Over a year ago

your coding is complicated for me, no doubt you are professional, can you give me a link which explains "wrap all IDisposable instances in a using statement.". thank you for your help

Zohar Peled Over a year ago

The using in my answer is also a link to the relevant page in MSDN...

GuidoG · Accepted Answer · 2017-01-19 08:59:03Z

-1

Your second query lacks the filter on a user name:

Select username, isadmin From users

So whatever it fetches - if the first row contains 1 as IsAdmin, all users will be redirected to the admin page.

edited Jan 19, 2017 at 8:59

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

answered Jan 19, 2017 at 8:32

Tyron78

4,1973 gold badges21 silver badges32 bronze badges

3 Comments

Tyron78 Over a year ago

dt does not contain the column IsAdmin

washaq Over a year ago

i have tried like this; Select isadmin From users but no hope!

Tyron78 Over a year ago

You should try SELECT username, pass, isadmin FROM users in the first cmd

Collectives™ on Stack Overflow

How to get value of DataTable Row in C# asp.net

5 Answers 5

2 Comments

Comments

Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related