Commandobject.executenonquery() and Commandobject.executereader() give different results for count(*)

Question

I'm trying to create a login page and when i give executereader it works and when i give executenonquery it returns a -1 value instead of 1.

This return -1 on cmd.executenonquery()

SqlCommand cmd = new SqlCommand("select Count(*) from userDb where username= '"+txtusername.Text+"' and password= '"+txtpassword.Text+"'", con);

Below code with executereader()

    SqlCommand cmd = new SqlCommand("select Count(*) from userDb where username= @p1 and password= @p2", con);

**Complete Code**
SqlCommand cmd = new SqlCommand("select Count(*) from userDb where username= @p1 and password= @p2", con);
        cmd.Parameters.AddWithValue("@p1", txtusername.Text);
        cmd.Parameters.AddWithValue("@p2", txtpassword.Text);
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read()==true)
        {
            FormsAuthentication.RedirectFromLoginPage(txtusername.Text, CheckBox1.Checked);
        }
        else
        {
            lbldisplay.Text = "Username and Password Do not Match";
        }
        con.Close();

Code with executenonquery

SqlCommand cmd = new SqlCommand("select Count(*) from userDb where username= '"+txtusername.Text+"' and password= '"+txtpassword.Text+"'", con);
            con.Open();
            int i = executenonquery();
            if (i == 1)
            {
                FormsAuthentication.RedirectFromLoginPage(txtusername.Text, CheckBox1.Checked);
            }
            else
            {
                lbldisplay.Text = "Username and Password Do not Match";
            }
            con.Close();

Can you show how you are parsing the results from the command? — Leo, Dec 15 '13 at 07:35
The code you posted only shows the SqlCommand declaration, it doesn't show what your code does to retrieve the results after the calls to ExcuteNonQuery. It'd be helpful to know how you parse the results received from the call to the database. Obviously there doesn't seem to be anything wrong with the sql queries itself apart from the fact that you are much better off using SqlParameters rather than inline sql because a malicious user could inject malicious sql queries to your database very easily. — Leo, Dec 15 '13 at 07:49
@leo let me explain the complete code First i get the username and password from textbox.Wrote the sql query where the username and password are checked using executenonquery and then when int i =cmd.executenonquery returns 1 ie there is a user with that name and password i forward him to the required page.I totally agree with the sql injection but wanted to know why this is not working. — Sainath, Dec 15 '13 at 07:53
y should you call execute nonquery?? because you are just selecting the values and not inseting into the database. so there is no need of executenonquery here — Amarnath Balasubramanian, Dec 15 '13 at 07:59
Mate, you are a bit confused. In your question you mentioned ExecuteNonQuery (please re-read) I asked you to post your complete code because you can still call ExecuteNonQuery with OUTPUT parameters. At this point, your question is getting extremely confusing. — Leo, Dec 15 '13 at 08:03
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery%28v=vs.110%29.aspx — Amarnath Balasubramanian, Dec 15 '13 at 08:04
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executereader%28v=vs.110%29.aspx — Amarnath Balasubramanian, Dec 15 '13 at 08:04

score 1 · Answer 1 · answered Dec 15 '13 at 08:18

Your ExecuteReader doesn't work either. You don't check whether select returned 1 but whether select returned any rows. And it always does. If no match is found it will return 1 row containing 0 as result.

ExecuteNonQuery is not appropriate because you are querying!

You should use ExecuteScalar instead.

Also you should be using 'using' construct or try finally to properly dispose of SqlConnection and SqlCommand.

Amarnath Balasubramanian · Answer 2 · 2013-12-15T08:07:54.013

0

ExecuteNonQuery

ExecuteNonQuery method will return number of rows effected with INSERT, DELETE or UPDATE operations. This ExecuteNonQuery method will be used only for insert, update and delete, Create, and SET statements. (Read More about ExecuteNonQuery)

SqlCommand.ExecuteNonQuery MSDN Documentation

ExecuteReader

Execute Reader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object. This one is forward only retrieval of records and it is used to read the table values from first to last.(Read More about ExecuteReader)

SqlCommand.ExecuteReader MSDN Documentation

edited Dec 15 '13 at 08:07

answered Dec 15 '13 at 07:34

Amarnath Balasubramanian

9,300
8
34
62

but when i type in sql server i get the value as 1 and when i write the command it gave the result as -1.I do not want to read any data i want to just check if password and username are a match with the db and felt this was the right one – Sainath Dec 15 '13 at 07:39
then the password and username doesn't match so it may be returning like that this is my guess, without seeing the code i cant say anything buddy.. – Amarnath Balasubramanian Dec 15 '13 at 07:45
1

pasted the Complete Code – Sainath Dec 15 '13 at 07:49
see i have cleary mention that for select statements usually we will be using executer reader whereas for insert, edit update we will be using executenonquery. – Amarnath Balasubramanian Dec 15 '13 at 07:55

Commandobject.executenonquery() and Commandobject.executereader() give different results for count(*)

2 Answers2