How to send SQL query from C# with backslashes?

Question

I want to execute a SQL query from my C# project. My problem is that I use in the query the connection string, which includes \. I have to escape it but the SQL can't get \\. I tried use @, but it still didn't work.

My SQL Query looks like:

INSERT INTO [SQL_TABLE] 
    SELECT * 
    FROM [Provider = Microsoft.Jet.OLEDB.4.0; Data Source 
            =Y:\\MyAccess\\MyDB\\myAccessFile.mdb; Jet OLEDB:Database Password = 
            1234;].[ACCESS_TABLE] 
    WHERE [ID] = 1234

The SQL get it with \\, but accept \.

I have tried even:

for(int i = 0;i < dt.Rows.Count; i++)
{
   try
   {
       string selectSQL = "SELECT * 
                           FROM [" + @"Provider = Microsoft.Jet.OLEDB.4.0; 
                              Data Source 
                              =Y:\MyAccess\MyDB\myAccessFile.md; Jet 
                              OLEDB:Database Password = 1234;]. 
                              [VTblASMCustomersDocumentsAndGroupCodes] 
                           WHERE [ID] = 1234"
       string sql = "INSERT INTO [SQL_TABLE] " + selectSQL;
       executeSQLQuery(sql, connectionStringSQL);
    }
    catch (Exception ex)
    {
         MessageBox.Show(ex.ToString());
    }
 }

What should I do?

Wouldn't it be worth tagging this with ms-access? Seems to be specific to that non-standard dialect of SQL. — JGFMK
– JGFMK, Commented Sep 8, 2019 at 9:21
@JGFMK. It use SQL DB in the INSERT, and ACCESS DB in the SELECT. — ut or
– ut or, Commented Sep 8, 2019 at 9:22
It should work either with \\ (first \ escapes second ) or with @, which makes \ not reuqire to be escaped. You surely have problem somewhere else. — Michał Turczyn
– Michał Turczyn, Commented Sep 8, 2019 at 9:25
@MichałTurczyn. Thanks. My problem is that in the C# it requires 2 slashes, but the SQL - just one, and it gets the escape slash as 2 slashes. My second problem is that I get the path with the slashes as aparameter, so how do I use @? — ut or
– ut or, Commented Sep 8, 2019 at 9:29

Michał Turczyn · Accepted Answer · 2019-09-08 09:41:06Z

3

Just try:

var query = @"INSERT INTO [SQL_TABLE] 
            SELECT * 
            FROM [Provider = Microsoft.Jet.OLEDB.4.0; Data Source 
                    =Y:\MyAccess\MyDB\myAccessFile.mdb; Jet OLEDB:Database Password = 
                    1234;].[ACCESS_TABLE] 
            WHERE [ID] = 1234"

Refer to System.String:

literal backslashes in a string must be escaped or the entire string must be @-quoted.

Above means that if you use @ before the string you don't need to escape \ and it's treated as it is.

edited Sep 8, 2019 at 9:41

answered Sep 8, 2019 at 9:35

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

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

6 Comments

ut or Over a year ago

But the C# doesn't accept it. It required Y:\\MyAccess\\MyDB\\myAccessFile.mdb

Fabio Over a year ago

@utor, c# "accept", notice @ prefix on string value. This will tell c# to accept string as it is.

Fabio Over a year ago

@utor, can you display how you are sending query to the sql server?

Michał Turczyn Over a year ago

@utor How do you know it? Did you try executing the code?

ut or Over a year ago

@Fabio and Michał Turczyn, I edited my question and added my C# code. Thank you!

|

feras · Accepted Answer · 2019-09-08 09:22:15Z

1

checkescape character its need \ \ to have '\' as the '\' considered escape character.

however i suggested for you to use the linked server for multi connection with sql server

answered Sep 8, 2019 at 9:22

feras

113 bronze badges

Collectives™ on Stack Overflow

How to send SQL query from C# with backslashes?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related