0

I want to check if username already exist in the database.

SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DBCOnn"].ToString());
try
{
    con.Open();

   SqlCommand cmd = new SqlCommand("select client_id from tbl_client where client_name=@cname", con);
   cmd.CommandType = CommandType.Text;

   cmd.Parameters.Add("@cname", DbType.String).Value = usernm;

   int i = cmd.ExecuteNonQuery();
   if (i > 0)
       return true;
   else
       return false;
}
catch (Exception ex)
{
    throw new Exception("CheckExistingClient:" + ex.Message, ex.InnerException);
}
finally
{
    con.Close();
}

But here, i always giving as -1

What is the problem.?

Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105

3 Answers3

3

ExecuteNonQuery returns the number of affected rows and is used normally for insert-, update- or delete-statements. Use ExecuteScalar with COUNT instead.

using (var con = new SqlConnection(ConfigurationManager.AppSettings["DBCOnn"].ToString()))
using(var cmd = new SqlCommand("select COUNT(client_id) from tbl_client where client_name=@cname", con))
{
    cmd.Parameters.Add("@cname", DbType.String).Value = usernm;
    con.Open();
    int i = (int)cmd.ExecuteScalar();
    return i > 0;
}

Here is a related answer on SO: https://stackoverflow.com/a/4269651/284240

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
1

ExecuteNonQuery returns the number of rows affected by an INSERT, UPDATE or DELETE statement. You're running a SELECT.

I find it's easier to "SELECT FROM Users where UserName=@cName" and run a standard Select.

Barracoder
  • 3,696
  • 2
  • 28
  • 31
1

ExecuteNonQuery is meant for DML queries. You are not modifying and rows. So you get the result as -1.

You could use select count(client_id) from tbl_client where client_name=@cname

And get the count with cmd.ExecuteScalar

nunespascal
  • 17,584
  • 2
  • 43
  • 46