CODEWITHSUNDEEP
c#mysql
C-Sharpsz
C-Sharpsz

Reputation: 45

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

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

Upvotes: 1

Views: 312

Answers (1)

aepot
aepot

Reputation: 4824

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";

Upvotes: 4

Related Questions