Delete query in C# for mysql not working

Question

Hi,

I have written a delete query to delete a specific row in mysql database using .net connector.

cmd.CommandText =

"DELETE FROM "+ tablename + " WHERE TestID = "+ testid + " AND TestRequestID = "+ testreqid + ";";

when i say cmd.ExecuteNonQuery(), there is no exception thrown. But the row is not being deleted from the database.

Answer 1

Hello,

First, this is a forum for .NET inside SQL Server, not for external .NET application and it's for MS SQL Server, not for MySQL; you should post your question to a more related forum.

And: If you don't get an exception and the data won't be deleted, then you may pass wrong values for the IDs; if MySQL can't find any record for the IDs, then it don't deleted anything, of course.

---

Olaf Helper
Blog Xing

Answer 2

Hi,

I have written a delete query to delete a specific row in mysql databaseusing .net connector.

cmd.CommandText =

"DELETE FROM "+ tablename +" WHERE TestID = "+ testid + " AND TestRequestID = "+ testreqid + ";";

when i say cmd.ExecuteNonQuery(), there is no exception thrown. But the row is not being deleted from the database.

Answer 3

See your double post: http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/6eeee784-3f17-4a37-bb35-9ca37260c21f

And it's still the wrong forum for your question.

---

Olaf Helper
Blog Xing

Answer 4

Hi,

I am not getting to know what forum i can post it to.

And: I am not passing the wrong values in the query. But still it does not work

Answer 5

When you query the data with

cmd.CommandText =
 
"SELECT * FROM "+ tablename + " WHERE TestID = "+ testid + " AND TestRequestID = "+ testreqid + ";";

do you get any result; or the same using any query tool?

---

Olaf Helper
Blog Xing

Answer 6

Hi,

I use Visual Studio 2010; for the above select query u have mentioned the ExecuteNonQuery returns -1.

But the testid and testrequestid are present in the mysql db.

I tried even in the MYSQL command client; it returns an Empty Set.

Answer 7

But the testid and testrequestid are present in the mysql db.

I tried even in the MYSQL command client; it returns an Empty Set.

If they would be preset, you wouldn't get an empty result set; so they are not preset, right? Are you sure you are connected to the right database?

See also ExecuteNonQuery Method, it (those for SQL Server) returns only -1 if a rollback occurs. Is there maybe a trigger on that table preventing deletions?

---

Olaf Helper
Blog Xing

Answer 8

Hi,

I did some R&D on this. My test condition contains a hyphen in its value, so the command was returning empty. Instead of using = i used like operator, it gave me the result.

But now i want to get the result using = opeartor only and there are hyphens present in the value of the condition. M not able to achieve this yet.

Answer 9

Hi,

The code you posted is no problem. I suggest you to add a break point at the line of ExecuteNonQuery method, retrieving the exact command, and execute it directly in MySQL side, please check if it could delete the record.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 10

If the value contain hyphen then I assume it is string value, not the numeric ID, right? If this is the case, you should use single quotes around your value, like (assuming TestID and TestRequestID are both strings)

"SELECT * FROM "+ tablename + " WHERE TestID = '"+ testid + "' AND TestRequestID = '"+ testreqid + "';"

I also recoomend to use parameterized quesry to pass values to avoid SQL injection vulnerability.

---

Val Mazur Microsoft MVP
http://www.xporttools.net

Answer 11

Hi,

Have you solved the issue? I look forward to hearing from you. If you need further help, please feel free to let me know. I will be more than happy to be of assistance.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 12

Hi,

Sorry for the delay in response. I used the rlike operator and i could achieve the results.

I used the single quotes as u said, but that does not work as well (either from the code nor from MYSQL command line). My testid is not numeric, but it is a mixture of alpha numeric characters, but the data type of testid is string. The same with testrequestid as well..

Thanks & Regards,

Sheetal