Can I get a value from ExecuteNonQuery()? RSS

• • Reply
  

  riak

  Member

  268 Points

  90 Posts

  Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 06:00 AM|LINK

  HI I'm trying to check my database for an existing email for when someone trys to sign up with the same email address.

  Here is part of my code:

  SqlCommand checkEmail = new SqlCommand("SELECT email FROM users WHERE email= '@email'", db);
  
   query.Parameters.Add("@email", System.Data.SqlDbType.VarChar, 50);
              query.Parameters["@email"].Value = email.Text;
  
    try
          {
              db.Open();
              checkEmail.ExecuteNonQuery();
  
              if (checkEmail != null)
              {
                  emailExistsVal.Visible = true;
                  test.Text = checkEmail.ToString();
              }
              else
              {
                  query.ExecuteNonQuery();
                  test.Text = "Submitted!";
              }
          }
          finally
          {
              db.Close();
          }

  At the moment the if statement always fires because checkEmail contains "System.Data.SqlClient.SqlCommand" but i want 0 or 1 if there is an email or not already in the database. what am I missing?

  PosterJob
• • Reply
  

  anuj_koundal

  Contributor

  2269 Points

  541 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 06:03 AM|LINK

  use 

  SqlCommand checkEmail = new SqlCommand("SELECT Count(email) FROM users WHERE email= '@email'", db);

   query.ExecuteScalar();

  //==== This will return single values

  Example :http://csharpdotnetfreak.blogspot.com/2012/05/executescalar-example-in-aspnet-c-vb.html

  Regards
  Anuj Koundal
  My Blog
  Mark as Answer on the post that helps you.
  • Edited by anuj_koundal on Jan 24, 2013 06:04 AM
• • Reply
  

  riak

  Member

  268 Points

  90 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 06:06 AM|LINK

  Thanks for the quick replay I tried that but my test string still says System.Data.SqlClient.SqlCommand

  PosterJob
• • Reply
  

  anuj_koundal

  Contributor

  2269 Points

  541 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 06:19 AM|LINK

  SqlCommand checkEmail = new SqlCommand("SELECT Count(email) FROM users WHERE email= '@email'", db);
  
   query.Parameters.Add("@email", System.Data.SqlDbType.VarChar, 50);
              query.Parameters["@email"].Value = email.Text;
  
    try
          {
              db.Open();
             int emailCount= Convert.ToInt32(checkEmail.ExecuteScalar());
  
              if (emailCount>0)
              {
                  //Means their are user having the provided email write your code here
              }
              else
              {
                  // No user found code here
              }
          }
          finally
          {
              db.Close();
          }

  Regards
  Anuj Koundal
  My Blog
  Mark as Answer on the post that helps you.
• • Reply
  

  fayaz_3e

  Star

  9386 Points

  1751 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 06:22 AM|LINK

  Best practice is to return 1 instead of anything else. Change query to 

  SELECT 1 FROM users WHERE email= '@email'

  Then change your NonQuery to Scalar

  db.Open();
              object result = checkEmail.ExecuteScalar();
  
              if (result == DBNull.Value)
              {
  		query.ExecuteNonQuery();
                  test.Text = "Submitted!";
              }
              else
              {
                  emailExistsVal.Visible = true;
                  test.Text = checkEmail.ToString();
              }

  
  Observe the if and else. I altered them

  this.Fayaz();
  • Edited by fayaz_3e on Jan 24, 2013 06:23 AM
• • Reply
  

  riak

  Member

  268 Points

  90 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 09:12 AM|LINK

  Thanks guys I'm at work now so wont be able to try these till I get home. I'll let you know how I get on
  PosterJob
• • Reply
  

  riak

  Member

  268 Points

  90 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 24, 2013 09:36 PM|LINK

  I tried both of these and none of them worked. I keep getting back the value 0 for a duplicate email and a unique email

  PosterJob
• • Reply
  

  fayaz_3e

  Star

  9386 Points

  1751 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 25, 2013 02:04 AM|LINK

  Can you execute same query in Sql Server Management studio and see whats the output?

  SELECT 1 FROM users WHERE email= 'emailID' --replace email id with actual one

  this.Fayaz();
• • Reply
  

  riak

  Member

  268 Points

  90 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 25, 2013 05:41 AM|LINK

  Hi I got a 1 back when there is an email but got nothing at all when i used a new email

  PosterJob
• • Reply
  

  anuj_koundal

  Contributor

  2269 Points

  541 Posts

  Re: Can I get a value from ExecuteNonQuery()?

  Jan 25, 2013 05:54 AM|LINK

  SELECT ISNULL(count(email),0) FROM users WHERE email= '@email'

  //=-=-=-= Whenever User's Email Match it will return you value more than 1

  Else it will returns 0

  Regards
  Anuj Koundal
  My Blog
  Mark as Answer on the post that helps you.