simon simon - 2 months ago 13
ASP.NET (C#) Question

Get a string value from SQL Server database

I am developing a login system using in asp.net and using a SQL Server database. I did not add yet the security part, this is just a login for testing, so this is not my concern for the moment.

I have an

Account
table in the database with columns
username
,
password
and
acc_type
. What I want during the login is if the credentials are correct, the account type needs to be checked then, and depending on the type of the account, the user will be redirected to its corresponding page.

Here is my code so far:

protected void LoginButton_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=AA-PC\\SQLSERVER2012;Initial Catalog=oncf;Integrated Security=True";

con.Open();

string query = "SELECT count(*) FROM Account where acc_username=@username and acc_password=@password";

SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);

int count= Convert.ToInt32(cmd.ExecuteScalar());

if(count==1)
{
string query2 = " SELECT acc_type FROM Account where acc_username=@username and acc_password=@password";

SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.Parameters.AddWithValue("@username", TextBox1.Text);
cmd2.Parameters.AddWithValue("@password", TextBox2.Text);

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd2);
da.Fill(dt);

string userType = dt.Rows[0]["acc_type"].ToString();

if (userType == "LandAsset")
{
Response.Redirect("ManageLines.aspx");
}
else
{
Response.Redirect("MainAdmin.aspx");
}
}
else {
Label2.Visible = true;
}

con.Close();
}


I have an outer if else condition to check if the credentials are correct, if it is the case, then we have the logic checking which type of account it is.

However, whenever I try to login, no matter what type the user is, I am always redirected to the same
MainAdmin.aspx
page..

Can anyone help with that please?

Answer

You're doing this way too complicated!

Basically, if your user and his password are in fact present in the table, your second query will return his account_type - and vice-versa, if that user doesn't exist, or if the password is wrong, then no value (or NULL) will be returned - just check that. Also: since you're returning exactly one row, one column, you can use the much simpler .ExecuteScalar() instead of filling a DataTable and then scouring around in it to find the information you need ..

So basically, this code should do the same:

protected void LoginButton_Click(object sender, EventArgs e)
{
    // set up your connection string and query as strings
    string connectionString = "Data Source=AA-PC\\SQLSERVER2012;Initial Catalog=oncf;Integrated Security=True";
    string query = "SELECT acc_type FROM dbo.Account WHERE acc_username = @username and acc_password = @password;"

    // set up your connection and command in "using" blocks
    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd2 = new SqlCommand(query, con))
    {
        // define and set parameters
        cmd2.Parameters.Add("@username", SqlDbType.VarChar, 100).Value = TextBox1.Text);
        cmd2.Parameters.Add("@password", SqlDbType.VarChar, 100).Value = TextBox2.Text);

        // open connection, execute command, close connection
        con.Open();

        object result = cmd.ExecuteScalar();

        con.Close();

        // if nothing was returned -> user/password are not valid
        if (result == null) {
            Label2.Visible = true;
        }
        else  
        {
            string accountType = result.ToString();

            if (accountType == "LandAsset")
            {
                 Response.Redirect("ManageLines.aspx");
            }
            else
            {
                 Response.Redirect("MainAdmin.aspx");
            }
        }
    }
}