MySql.Data.MySqlClient.MySqlException: in C# but works fine in MySQL Workbench

Question

Goal:

I am trying to put a query into C# to update MySQL record for specific id. But it is coming with an error message whenever I run it.

This is the Error message:

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\', ''))), '\Archive\', SUBSTRING_INDEX(file_attachment, '\', -1)) where id = 2' at line 1'

C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string query = "UPDATE document_control SET newIssueNo = 2, file_attachment = CONCAT(SUBSTRING_INDEX(file_attachment, '\\', LENGTH(file_attachment) - LENGTH(REPLACE(file_attachment, '\\', ''))), '\\Archive\\', SUBSTRING_INDEX(file_attachment, '\\', -1)) where id = 2";
            MySqlConnection conn = new MySqlConnection("credentials");
            conn.Open(); //Open the connection
            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.ExecuteNonQuery();

            conn.Close(); //don't forget to close it after you're done



        }
    }
}

But when I run it in MySQL workbench:

UPDATE document_control SET newIssueNo = 2, file_attachment = CONCAT(SUBSTRING_INDEX(file_attachment, '\\',  LENGTH(file_attachment) - LENGTH(REPLACE(file_attachment, '\\', ''))), '\\Archive\\', SUBSTRING_INDEX(file_attachment, '\\', -1)) where id = 2


1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0

It appears that you have posted sensitive/private information. Please reset your passwords and/or revoke API keys and tokens, as they are considered compromised when posted on the internet. If personally-identifiable information was posted, please edit out the info then flag your post for a moderator to redact the prior revisions. — Samuel Liew
– Samuel Liew, Commented Feb 21, 2020 at 5:37

aepot · Accepted Answer · 2020-02-19 16:16:16Z

4

Use @ before string value because in regular declaration \ is escape character.

string query = @"UPDATE document_control SET newIssueNo = 2, file_attachment = CONCAT(SUBSTRING_INDEX(file_attachment, '\\', LENGTH(file_attachment) - LENGTH(REPLACE(file_attachment, '\\', ''))), '\\Archive\\', SUBSTRING_INDEX(file_attachment, '\\', -1)) where id = 2";

answered Feb 19, 2020 at 16:16

aepot

4,8642 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql.Data.MySqlClient.MySqlException: in C# but works fine in MySQL Workbench

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related