C# SqlDataReader = null?

Question

            String sqlCheckPass = 
"Select * from Login where Username like @Username and Password like @Password";
        SqlCommand SqlCom = new SqlCommand(sqlCheckPass, myConnection);
        SqlCom.Parameters.Add(new SqlParameter("@Username", sUserName));
        SqlCom.Parameters.Add(new SqlParameter("@Password", sPassword));

        myConnection.Open();
        SqlDataReader myreader;
        myreader = SqlCom.ExecuteReader();
        int id = -1;

ErrorBox.InnerHtml = "Username:" + sUserName + ":" + sPassword + ":<br/>";
while (myreader.HasRows)
{
    id = (int)myreader["id"];
    String sUser = (String)myreader["Username"];
    String sPass = (String)myreader["Password"];
    ErrorBox.InnerHtml += "UserId is <b>" + id + "</b> " + sUser + ":" + sPass + ":<br >";
    Session["LoginID"] = id;
    Server.Transfer(ReturnPage);

}
if (id == -1)
{
    ErrorBox.InnerHtml = "Incorrect Password";
}
myConnection.Close();
catch (Exception err)
{
    ErrorBox.InnerHtml = "Error Getting  Option ID" + err.Message;
}

I added a breakpoint at myreader = SqlCom.ExecuteReader(); and it keeps returning myreader as null and HasRows = False, but it does have rows. So, it keeps validating my login as incorrect since id = -1,

Help?

secondly... if myreader is null then "HasRows" can't be false. — Robin Day
– Robin Day, Commented Mar 23, 2010 at 14:45
Don't store your passwords in plain text on the database - what happens when someone gets access to that table? Store them as a hash instead. — Martin Milan
– Martin Milan, Commented Mar 23, 2010 at 15:06
Also, you should really specify the type of the parameters on the command object as you create them... — Martin Milan
– Martin Milan, Commented Mar 23, 2010 at 15:07

Bryan Denny · Accepted Answer · 2010-03-23 14:44:27Z

4

You didn't connect your reader to your SQL connection/command?

SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read()) 
{
   Console.WriteLine(myReader.GetString(0));
}
myReader.Close();

answered Mar 23, 2010 at 14:44

Bryan Denny

27.6k33 gold badges111 silver badges125 bronze badges

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

5 Comments

ChrisBD Over a year ago

+1 - its obviously not initialised hence being NULL. Other values are just defaults.

tom Over a year ago

See the original posts I've edited it, I;m sure it's connected right. @Chris - What's not initialised?

Bryan Denny Over a year ago

Is your connection string setup properly? No exceptions thrown in a try/catch or similar?

tom Over a year ago

yeah definately right, because it can get the names from the database into a dropdown list, that I've temporarily setup

Bryan Denny Over a year ago

And @David makes a good recommendation, it might also be your query format

David · Accepted Answer · 2010-03-23 15:02:13Z

1

The problem might be the LIKE in your query with the SqlParameters. Try

String sqlCheckPass =  
"Select * from Login where Username like '%' + @Username + '%' and Password like '%' + @Password + '%'";

answered Mar 23, 2010 at 15:02

David

5,5062 gold badges29 silver badges39 bronze badges

4 Comments

David Over a year ago

By the way, why are you using LIKE instead of "=" ?

David Over a year ago

Cool. However, it's dangerous to use LIKE %% for authentication. You should use "="

ZippyV Over a year ago

Never use LIKE for checking a name and password.

Ricardo Sanchez Over a year ago

+1 for using "=" instead of "like". I am also curious... what are the values on both parameters that no data was being returned before adding the wildcards...

Ricardo Sanchez · Accepted Answer · 2010-03-23 15:32:23Z

Bryan Denny's answer above is correct, however, I'll enclose all of the code inside using statements as shown below:

using (SqlConnection dataConnection = new SqlConnection(connectionString))
{
    using (SqlCommand SqlCom = dataConnection.CreateCommand())
    {
        SqlCom.CommandText = "Select * from Login where Username like @Username and Password like @Password";
        SqlCom.Parameters.Add(new SqlParameter("@Username", sUserName)); 
        SqlCom.Parameters.Add(new SqlParameter("@Password", sPassword)); 

        dataConnection.Open();
        SqlDataReader myreader; 
        myreader = SqlCom.ExecuteReader(); 
        dataConnection.Close();
    }
}

I didn't add all of your code to this snippet, I figured you get the idea.

Also, you could try modifying the select statement to return a count of records since this is all you need anyways, a number:

SELECT COUNT(*) FROM Login WHERE Username like @Username AND Password like @Password

Good luck!

Collectives™ on Stack Overflow

C# SqlDataReader = null?

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related