When Not every query works with my Click event

Question

My "problem" table contains categories that are used in a treeview.

The categories are listed in a hierarchical view as in this example

* Test 1
    * Test 2
    * Test 3
        * Test 4
        * Test 5
    * Test 6

This is stored in the table as

category_Id	category_Descrepription	category_Fullpath	category_ParentId
1	Test 1	Test 1
2	Test 2	Test 1\Test2	1
3	Test 3	Test 1\Test3	1
4	Test 4	Test 1\Test3\Test 4	3
5	Test 5	Test 1\Test3\Test 4	3
6	Test 6	Test 1\Test6	1

I want to be able to delete Category 3 and all its children so I use DELETE FROM categories WHERE category_Fullpath LIKE '%Test1\\Test 3%';

In MySQL (WorkBench and HeidiSQL this works as expected, but if I use my click event that works when I pass the record ID, but it seems to do nothing when I pass the above query.

I checked the output and it produces the correct query, I even copy pasted it and executed it in KeidiSQL and it works, so why not when executed from the code.

My Button_Click event is:

private void ButtonDeleteCategorie(object sender, RoutedEventArgs e)
{
    Database dbConnection = new Database();
    dbConnection.Connect();

    int ID = int.Parse(valueId.Text);
    dbConnection.SqlCommand = "DELETE FROM ";
    // This works: dbConnection.SqlCommandString = " WHERE category_Id = " + ID + ";";
    dbConnection.SqlCommandString = " WHERE category_Fullpath LIKE '%" + valueFullpath.Text.Replace("\\", "\\\\") + dialogCategory.diaLogCategoryValue + "%';";
    dbConnection.TableName = DatabaseTable;
    dbConnection.UpdateMySqlDataRecord();
    _ = dbConnection.LoadMySqlData();
}

My FUnction in my database class

public int UpdateMySqlDataRecord()
{

    MySqlCommand SqlCmdUpdate = new MySqlCommand
    {
        Connection = Database.myConnection,
        CommandText = SqlCommand + Connection_Query.database + "." + TableName + SqlCommandString
    };

    MySqlConnection myConnection = new MySqlConnection(Connection_Query.connectionString);

    myConnection.Open();

    SqlCmdUpdate.ExecuteNonQuery();
    long ID = SqlCmdUpdate.LastInsertedId;

    myConnection.Close();

    return (int)ID;
}

Can anyone give my a clue where i miss something

What is this class `Database` where does it come from? And why are you not using parameterized queries? — Charlieface, Feb 14 '21 at 14:08
I think recursive function is the best of solutions in this matter. Use it and delete all Children. — Atabai Fekri, Feb 14 '21 at 14:32
Why is your data stored as `Test 1\Test3` and the query shows `LIKE '%Test1\\Test 3%';`? (It has an extra space before the '3' !) — Luuk, Feb 14 '21 at 14:46
@charlieface, I created the database class in the same namespace as the application. I am calling the click event from a button on WPF page. I removed the parameters because I was not sure that perhaps that was causing my problem. I will put them in place when it works again. — Herbert Nijkamp, Feb 14 '21 at 14:46
@AtabaiFekri You mean get the Id's of the main category and its children and iterate through that list to delete the categories one by one? — Herbert Nijkamp, Feb 14 '21 at 14:47
Is `MySqlCommand SqlCmdUpdate = new MySqlCommand{}` the correct syntax?? Been a long time since I C#'d — RiggsFolly, Feb 14 '21 at 14:48
@Luuk The missing spaces as typo's in this post. I So read Test1\Test3 or Test 1\Test 3 Because i can use spaces in my categories. — Herbert Nijkamp, Feb 14 '21 at 14:51
@RiggsFolly: yes, see: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/proposals/csharp-9.0/init — Luuk, Feb 14 '21 at 14:52
No, Not at all. Check the link please : https://stackoverflow.com/a/49531614/5871154 — Atabai Fekri, Feb 14 '21 at 14:53
@RiggsFolly I would say it is the correct syntax, it works also on other functions (adding and renaming records) although that does not always mean it is correct ;-) sometimes it is correct enough to let it work, but in this case, It is the correct syntax. — Herbert Nijkamp, Feb 14 '21 at 14:54
`Connection = Database.myConnection,` This doesn't make sense as you are not opening that connection, you use a different one on the next line. You need to flip it round: create the connection, add it to a new command, then open it. And you should use `using` on your connection and command objects — Charlieface, Feb 14 '21 at 14:55
Not sure what the point of `Replace("\\", "\\\\")` is, can't work out why you would need it — Charlieface, Feb 14 '21 at 14:58
@charlieface If I pass Test1\\Test2 t o SQL my backslashes are stripped, the i read in other posts that you have to passe 4 backslasches instead of two. — Herbert Nijkamp, Feb 14 '21 at 15:33
Not aware of that being the case. Even if it is true, **this is why you use parameters**, keep your code and data far away from each other — Charlieface, Feb 14 '21 at 15:35
@Charlieface You are right my connection = Database.MyConnection does not make sense I removed it, and I will put the parameters back in place because that is not the reason why I don't work. I don't know how to use sing for the connection, I only know using for adding other namespaces, but how can I use this for establishing a connection to the database table? — Herbert Nijkamp, Feb 14 '21 at 18:22
@AtabaiFekri thank you for that link, another approach, where I did not think of, could be the key to be able to delete my categories. — Herbert Nijkamp, Feb 14 '21 at 18:24
See https://stackoverflow.com/a/17553377/14868997 although that is for SQL Server the `using` syntax is the same — Charlieface, Feb 14 '21 at 20:08

score 1 · Answer 1 · answered Feb 14 '21 at 22:16

with the help of @kazi-mohammad-ali-nur I found the error in my code.

Origianly I added extra backslahes to my string because in my Fullpath dbConnection.SqlCommandString = " WHERE category_Fullpath LIKE '%" + valueFullpath.Text.Replace("\\", "\\\\") + dialogCategory.diaLogCategoryValue + "%';";

Some people questioned the need of 4 backslashes, but I even needed 8 backslashes to make it work.

dbConnection.SqlCommandString = " WHERE category_Fullpath LIKE '%" + valueFullpath.Text.Replace("\\", "\\\\\\\\") + dialogCategory.diaLogCategoryValue + "%';";

Debugging is the best option. Your effort deserves a upvote. — Kazi Mohammad Ali Nur Romel, Feb 15 '21 at 06:07

score 0 · Answer 2 · answered Feb 14 '21 at 16:25

0

Please debug and check what's the value of SqlCommandString after executing below line.

dbConnection.SqlCommandString = " WHERE category_Fullpath LIKE '%" + valueFullpath.Text.Replace("\\", "\\\\") + dialogCategory.diaLogCategoryValue + "%';";

Please also check category_fullpath for test 1 and test 2. instead of "Test 1\Test 2" it's showing "Test 1\Test2".

answered Feb 14 '21 at 16:25

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

I debugged the result and it was displaying the expected Result, I even copied the result and executed it in HeidiSQL, and my categories were removed. – Herbert Nijkamp Feb 14 '21 at 18:17
Please debug value of SqlCmdUpdate. and paste here – Kazi Mohammad Ali Nur Romel Feb 14 '21 at 19:29
The result CommandText is `DELETE FROM modelbuilder.categories WHERE category_Fullpath LIKE '%Gereedschap\\Elektrisch\\Test1%';` These are of course the strings I use in the real categories so therefore Gereedschap and Electrisch instead of Test1 and Test2 – Herbert Nijkamp Feb 14 '21 at 21:48
I think I see the problem here. In my MainWindow I replace the '\\' with '\\\\' But here two of the four backslashes are stripped already even before I pass the string to my database connection. I replaced the two backslashes in the Mainwindows to 8 instead of 4 and it works. – Herbert Nijkamp Feb 14 '21 at 22:07
That’s great. Best of luck. – Kazi Mohammad Ali Nur Romel Feb 15 '21 at 02:51

When Not every query works with my Click event

2 Answers2