CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008
user2758785
user2758785

Reputation: 25

how do i get column value from sql server 2008?

i have table where have 5 columns :

i wrote the code like this :

String SQLQuery = "SELECT count(*) FROM aspnet_Users where Username=@uname AND Password = @pwd";
    using(SqlConnection sqlConnection = new SqlConnection(strConnection))
    using(SqlCommand command = new SqlCommand(SQLQuery, sqlConnection))
    {
        sqlConnection.Open();
        command.Parameters.AddWithValue("@uname", Username);
        command.Parameters.AddWithValue("@pwd", Password);
        int result = Convert.ToInt32(command.ExecuteScalar());
        boolReturnValue = (result > 0);
    }

here few more extra information i needed,if above Username and password is correct, what i need is : userid, and role column data

Upvotes: 0

Views: 4217

Answers (4)

senthilkumar2185
senthilkumar2185

Reputation: 2566

Try this Code

SELECT count(*),userid,role FROM aspnet_Users where Username=@uname AND Password = @pwd Group by userid,role

Upvotes: 0

sudhansu63
sudhansu63

Reputation: 6180

String SQLQuery = "SELECT Top 1 UserId, role  FROM aspnet_Users where Username=@uname AND Password = @pwd";
    using(SqlConnection sqlConnection = new SqlConnection(strConnection))
    using(SqlCommand command = new SqlCommand(SQLQuery, sqlConnection))
    {
        sqlConnection.Open();
        command.Parameters.AddWithValue("@uname", Username);
        command.Parameters.AddWithValue("@pwd", Password);

         SqlDataReader Reader = null;
         if (sqlConnection.State == ConnectionState.Closed || sqlConnection.State == ConnectionState.Broken)
                    sqlConnection.Open();
            Reader = command.ExecuteReader();
           if (Reader.Read())
          {
            int UserId = Convert.ToInt32(Reader["UserId"]);
            string Role = Convert.ToString(Reader["role"]);

          }

   }

Upvotes: 1

Falydoor
Falydoor

Reputation: 462

Why you aren't doing that instead ?

string SQLQuery = "SELECT UserId FROM aspnet_Users where Username=@uname AND Password = @pwd";
[...]
object result = command.ExecuteScalar();
if (result == null)
{
    boolReturnValue = false;
}
else
{
    long userId = Convert.ToInt64(result);
    boolReturnValue = true;
}

Upvotes: 2

FloChanz
FloChanz

Reputation: 3429

Why don't you just get the UserId instead of the Count(*) so your query should look like this : 

SELECT UserId FROM aspnet_Users where Username=@uname AND Password = @pwd

Username should be unique so you shouldn't retrieve more than one row...you can add a Top 1 in case you have multiple same username with same password.

Upvotes: 0

Related Questions