none
Delete query in C# for mysql not working RRS feed

  • 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.

    • Moved by Bob Beauchemin Saturday, September 1, 2012 5:56 AM Moving to a relevent forum (From:.NET Framework inside SQL Server)
    Friday, August 31, 2012 5:36 AM

Answers

  • 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

    Monday, September 10, 2012 5:21 AM

All replies

  • 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

    Friday, August 31, 2012 5:51 AM
  • 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.

    • Moved by Bob Beauchemin Saturday, September 1, 2012 5:57 AM Moving to a relevent forum (From:.NET Framework inside SQL Server)
    • Merged by Allen_MSDNModerator Monday, September 3, 2012 1:14 AM same question
    Friday, August 31, 2012 5:57 AM
  • 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

    Friday, August 31, 2012 5:58 AM
  • 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

    Friday, August 31, 2012 6:01 AM
  • 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

    Friday, August 31, 2012 6:03 AM
  • 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.

    Friday, August 31, 2012 6:18 AM
  • 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

    Friday, August 31, 2012 6:32 AM
  • 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.

    Friday, August 31, 2012 7:18 AM
  • 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

    Monday, September 3, 2012 1:13 AM
    Moderator
  • 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

    Wednesday, September 5, 2012 12:22 PM
    Moderator
  • 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

    Monday, September 10, 2012 1:52 AM
    Moderator
  • 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

    Monday, September 10, 2012 5:21 AM