SqlDataReader returns null unexpectedly

Question

I have a simple connection to a SQL Server that is not working. I'm trying to read data from it using a SqlDataReader in C#.

Here is the code:

bool ok = false;

SqlConnection con = new SqlConnection();
con.ConnectionString = @"********";

SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@a", uname);
cmd.Parameters.AddWithValue("@b", pass);
cmd.CommandText = @"SELECT username FROM admins WHERE pass='@b'";
cmd.Connection = con;

SqlDataReader r;

con.Open();
r = cmd.ExecuteReader();
r.Read();

string n;
n = r.GetString(0);

if (n != null)
{
    ok = true;
}

con.Close();

if (ok)
{
    Session["admin"] = uname;
    Response.Redirect("admin_page.aspx");
}
else
{
    eror.Text = "An eror occured";
    Response.Redirect("index.aspx#work");
}

Note: that in the above code string "uname" and "pass" are definitely not null.

Note #2 : I did try running the r.read() in a while loop (even though it's not possible to have more then one row) ---> same result.

I tried running this code in step mode, and it appears that it breaks on this line:

n = r.GetString(0);

With this exception:

An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code

Additional information: Invalid attempt to read when no data is present.

I'm kinda lost here. I know that it's probably a simple thing I missed here, I just can't find it. Any ideas?

@a is not even used in your query, and your problem is that now rows are returned so you get Invalid attempt to read when no data is present — Amit
– Amit, Commented Jul 19, 2015 at 19:14
Couple of things. Why do you have parameter @a when you don't use it? Also, if you add parameters you shouldn't add the ' characters manually to your query. It will be taken care of automatically. You should test if r.Read succeeds (use the return value), if it doesn't your data isn't found in the database. I suspect that this is the problem here. — Saragis
– Saragis, Commented Jul 19, 2015 at 19:16

Stan · Accepted Answer · 2015-07-19 19:16:58Z

2

In addition to Amit's observation about an unused parameter, you are misusing the parameter

Where you have

cmd.CommandText = @"SELECT username FROM admins WHERE pass='@b'";

you should not have quotes around the value, so:

cmd.CommandText = @"SELECT username FROM admins WHERE pass=@b";

The parameter will know it is a VARCHAR

answered Jul 19, 2015 at 19:16

Stan

9831 gold badge7 silver badges13 bronze badges

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

3 Comments

user3107990 Over a year ago

Removing the quotes around the value solved it. thanks alot.

Stan Over a year ago

Thanks for the upvote. Note that Amit's answer goes further (gives you information for which you didn't even ask), explaining how to better use the query result in your use case. I suggest you accept his answer so the question is marked answered!

marc_s Over a year ago

@user3107990: if this answer helped you solve your problem, then please accept this answer. This will show your appreciation for the people who spent their own time to help you.

Amit · Accepted Answer · 2015-07-19 19:23:02Z

0

Your not using SqlDataReader correctly.

If you want to know whether there exists a user with the correct username and password, your query should probably be: SELECT 1 FROM admins WHERE username=@a AND pass=@b

Once that's done, and since you don't care what the selected value is (you only care that there was a returned row...) Use the command like this:

r = cmd.ExecuteReader();
ok = r.HasRows;

After this, continue as you did.

answered Jul 19, 2015 at 19:23

Amit

46.4k9 gold badges83 silver badges113 bronze badges

Collectives™ on Stack Overflow

SqlDataReader returns null unexpectedly

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related