Checking if MS Access database in ASP.NET C# returns any rows

Question

I have this code:

cmd.Parameters.AddWithValue("@userName", txtLoginUserName.Text.Trim());
cmd.Parameters.AddWithValue("@password", txtLoginPassword.Text.Trim());

string sqllogin = "SELECT [username],[password] FROM users WHERE ([username] = @userName AND [password] = @password) ";

try
{
    con.Open();
    cmd.CommandText = sqllogin;
    int store = cmd.ExecuteNonQuery();

    con.Close();
    Label4.Text = store.ToString();
}
catch (Exception ex)
{
    con.Close();
}

Whenever it executes, it prints out 0 in label4. Is that the correct way to check how many rows are returned?

Uhm, why are you using `ExecuteNonQuery`? If you want to count the data sets that would be returned, use `SELECT COUNT(*) FROM ...` and `ExecuteQuery`. Then look at the result set for row/column 1/1 it will have the value of `COUNT(*)`. Otherwise execute the query as is (but with `ExecuteQuery` and count the number of rows returned in the C# code - depends on what you are trying to achieve. — Christian.K, Apr 26 '17 at 10:36
Or best way is to create a stored procedure and get return table, then you can do whatever you want to do.. — Bharat, Apr 26 '17 at 10:37

score 2 · Accepted Answer · edited Apr 26 '17 at 10:55

2

cmd.Parameters.AddWithValue("@userName", txtLoginUserName.Text.Trim());
cmd.Parameters.AddWithValue("@password", txtLoginPassword.Text.Trim());

string sqllogin = "SELECT count(*) FROM users WHERE ([username] = @userName AND [password] = @password) ";

try
{
    con.Open();
    cmd.CommandText = sqllogin;
    var store = cmd.ExecuteScalar();

    con.Close();
    Label4.Text = store.ToString();
}
catch (Exception ex)
{
    con.Close();
}

edited Apr 26 '17 at 10:55

Bharat

5,869
4
38
58

answered Apr 26 '17 at 10:51

Narek Arzumanyan

616
3
11

what does execute scalar does if you could please explain – Craftx398 Apr 26 '17 at 10:56
1

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx – Narek Arzumanyan Apr 26 '17 at 11:02
1

http://stackoverflow.com/questions/2974154/what-is-the-difference-between-executescalar-executereader-and-executenonquery – Narek Arzumanyan Apr 26 '17 at 11:03

score 1 · Answer 2 · answered Apr 26 '17 at 10:47

To answer your specific question ExecuteNonQuery() should return the number of rows affected. However that query is not doing a great deal for you there.

If you just want to know how many data rows match that un / pwd combination, then I would change it to

"SELECT COUNT([username]) FROM users WHERE ([username] = @userName AND [password] = @password) ";

I still don't see how much use that would be to you, but you know what you are trying to do :-)

Checking if MS Access database in ASP.NET C# returns any rows

2 Answers2