SQL/MYSQL question 1

[Henryas]

Hi,

How do you programmatically check the existence of a data in a SQL/MYSQL database? I have tried to do the followings, but it does not seem to work:

string command = "SELECT COUNT(*) FROM table WHERE username = 'DoesNotExist'; 
MySqlCommand cmd = new MySqlCommand(command,connection); 

if(cmd.ExecuteScalar()==null)[green]//does not work[/green] 
{ 
   [green]//Data not found[/green] 
}else 
{ 
   [green]//Data found[/green] 
}

I have also tried casting but my compiler complains about it ...

if(((int)cmd.ExecuteScalar())==0) 
{ 
  [green]//Data not found[/green] 
}else 
{ 
  [green]//Data found[/green] 
}

So what does "SELECT COUNT(*)" return if data not found?

Thanks.

Henry

 

[obislavu]

<<
The Command object provides the capability to return single values using the ExecuteScalar method. The ExecuteScalar method returns as a scalar value the value of the first column of the first row of the result set.
>>
I think you didn't open the Connection.
Here is the code that works with Sql:

SqlConnection connection =null;
SqlCommand cmd = null;
try
{
SqlConnection connection = new SqlConnection(strConnection);
string command = "SELECT COUNT(*) FROM table WHERE username = 'DoesNotExist'"; 
SqlCommand cmd = new SqlCommand(command,connection); 
cmd.Connection.Open();
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
 {
  // no records with above critaeria
 }
 else{
  // found records
 }

}
catch(Exception ex)
{
  string sErr = ex.GetType() + ex.Message;
}
obislavu

 

[Henryas]

Hello Obislavu,

Thank you for the reply. I did open the connection. I just didn't include it in the my code snipet.

When I tried your suggestion, the following error occurs: "Specified cast is not valid." right at the point where "Int32 count = (Int32)cmd.ExecuteScalar();" is. I am not sure whether it is MySql-specific quirkiness.

My current workaround is by using

if(cmd.ExecuteScalar().toString().Equals("0"))
{
  [green]//Data not found[/green]
}else
{
  [green]//Data found[/green]
}

It works fine now, but I just don't understand the need to convert it to string.

Henry