I have been working on trying to check if a record exists in a database table. I have the following code and it is not working.
MySqlConnection connection = new MySqlConnection("My connection string");
string check = string.Format("Select COUNT(*) FROM User_Class WHERE user_class_name = '{0}'", TextBox1.Text);
string query = string.Format("INSERT INTO User_Class (user_class_name) VALUE ('{0}');", TextBox1.Text);
MySqlCommand cmd = new MySqlCommand(check, connection);
MySqlCommand cmd2 = new MySqlCommand(query, connection);
//MySqlDataReader reader;
connection.Open();
if (cmd.BeginExecuteNonQuery().Equals(0))//record does not exist
{
cmd2.BeginExecuteNonQuery();
Label1.Text = "User Class Created!";
Label1.ForeColor = Color.Green;
}
else
{
Label1.Text = "User Class Already Exists";
Label1.ForeColor = Color.Red;
}
This code always goes to the else case. I have also tried doing this using MySqlDataReader
but that is also not working. Why is it not returning 0 (or false/null)? What is the best way to check for an empty return?
There are at least two issues here.
Firstly, you're calling BeginExecuteNonQuery
- that doesn't return an integer, it returns an IAsyncResult
. I strongly suspect that you don't want to be dealing with the asynchronous API at all.
Secondly, using ExecuteNonQuery
isn't appropriate for a query anyway. I suspect you actually want ExecuteScalar
:
if ((int) cmd.ExecuteScalar() == 0)
You may need to cast to long
instead of int
; I don't know offhand what type of value it will return. However, that's a far more sensible call for a "SELECT COUNT" query.
I'd then suggest you use ExecuteNonQuery
for the INSERT... after changing your code to use parameterized SQL instead of the SQL-injection-inviting approach you're using now.
See more on this question at Stackoverflow