quotation marks in sql statement

[Adam Right]

Hi !

In my sql statement, i have to use field names and table name with quotation
marks.
like that :
-----------------------------------------------------------------------
string sqlStr = "Select \"Person Name Field\", \"Age Field" from \"Old
Table\" ";
cmd.CommandText = sqlStr;
-----------------------------------------------------------------------
But when i assign this string to CommandText of OracleCommand object, it
gets an error ... because of backslashes.

How can i use quotation marks without backslash in a sql statement ?

I have been stuck on this, any help will be appreciated.

Thank you,

Adam

 

[Peter Duniho]

In my sql statement, i have to use field names and table name with
quotation
marks.
like that :

What backslashes? If you have properly constructed the string, no
backslashes would actually be present. The backslash-quoting is handled
by the compiler, and by the time the string is assigned to the variable,
no backslashes would remain.

If you get an error specifically complaining about backslashes (is it
actually true that you are?), then you didn't construct the string
correctly.

Of course, there is the additional issue that you didn't actually post the
code you're using. How do I know? The code you posted won't even
compile, since you're missing a backslash. Since we know you didn't just
copy-and-paste the code causing a problem, we also know that all sorts of
things could be missing from the example, including whatever it is that's
actually causing the problem.

How can i use quotation marks without backslash in a sql statement ?

Normally, you would do it just as you've tried to do in the example you
posted. So for your question to be answered, you need to do at least two
things:

1) post the _exact_ code you are using
2) describe the _exact_ error you are getting (in particular, unless
the error specifically says something about the backslashes, you have no
reason to believe that the backslashes are definitely the problem)

Pete

 

[Ben Voigt [C++ MVP]]

Hi !

In my sql statement, i have to use field names and table name with
quotation marks.
like that :
-----------------------------------------------------------------------
string sqlStr = "Select \"Person Name Field\", \"Age Field" from \"Old
Table\" ";
cmd.CommandText = sqlStr;
-----------------------------------------------------------------------
But when i assign this string to CommandText of OracleCommand object, it
gets an error ... because of backslashes.

How can i use quotation marks without backslash in a sql statement ?

The other options are:

Use char(34), 34 being the ASCII code for the quote character:

char dblquote = 34;
string sqlStr = "SELECT " + dblquote + "Person Name" + dblquote + ......

Use a verbatim string literal:

string sqlStr = "SELECT ""Person Name"", ""Age Field"" FROM ""Old Table"";";

 

[Jon Skeet [C# MVP]]

Use a verbatim string literal:

string sqlStr = "SELECT ""Person Name"", ""Age Field"" FROM ""Old Table"";";

Slight typo correction (I'm absolutely sure Ben knows how to use a
verbatim string literal - it needs the @ prefix:

string sqlStr =
@"SELECT ""Person Name"", ""Age Field"" FROM ""Old Table"";";

 

[Ben Voigt [C++ MVP]]

The other options are:

Use char(34), 34 being the ASCII code for the quote character:

char dblquote = 34;
string sqlStr = "SELECT " + dblquote + "Person Name" + dblquote + ......

Use a verbatim string literal:

string sqlStr = "SELECT ""Person Name"", ""Age Field"" FROM ""Old
Table"";";

and of course I forgot the all-important @.

string sqlStr = @"SELECT ""Person Name"", ""Age Field"" FROM ""Old
Table"";";

 

[AA2e72E]

You might find that

string sql = @"SELECT [Person Name], [Age Field] FROM [Old Table];";

resolves the problems: (?) depends on the SQL dialect of your data source.

 

[Jon Skeet [C# MVP]]

You might find that

string sql = @"SELECT [Person Name], [Age Field] FROM [Old Table];";

resolves the problems: (?) depends on the SQL dialect of your data source.

If it does, the @ is unnecessary, as there are no backslashes in the
string anyway.

 

[Peter Duniho]

You might find that

string sql = @"SELECT [Person Name], [Age Field] FROM [Old Table];";

resolves the problems: (?) depends on the SQL dialect of your data
source.

If it does, the @ is unnecessary, as there are no backslashes in the
string anyway.

True. But there weren't any backslashes in the originally posted string
either (even though there were in the literal in the code, the string
itself wouldn't have had any).

I wish I knew more about SQL (hardly use it myself), because I find myself
thinking crazy things like "maybe the field and table names themselves
have double-quotes in them" and "maybe his problem is that he _needs_ the
backslash characters in the SQL string, with the code he's writing failing
to put them there".

Just goes to show: writing a good question isn't trivial, but it is very
important in getting an answer. So far I'd say the question hasn't been
presented very well.

Pete

 

[Jon Skeet [C# MVP]]

True. But there weren't any backslashes in the originally posted string
either (even though there were in the literal in the code, the string
itself wouldn't have had any).

Indeed. I suspect actually this is another case of the OP looking at
the string in the debugger, seeing the backslashes in the Locals
window, and thinking that the string itself contains backslashes when
it doesn't.

I wish I knew more about SQL (hardly use it myself), because I find myself
thinking crazy things like "maybe the field and table names themselves
have double-quotes in them" and "maybe his problem is that he _needs_ the
backslash characters in the SQL string, with the code he's writing failing
to put them there".

Yes, I know the feeling. I know the "happy path" SQL where there aren't
extra issues like encoding etc, but not depths of it.

Just goes to show: writing a good question isn't trivial, but it is very
important in getting an answer. So far I'd say the question hasn't been
presented very well.

Perhaps. I suspect the OP is just confused about what the debugger's
telling him, really.

 

[DeveloperX]

Hi !

In my sql statement, i have to use field names and table name with quotation
marks.
like that :
-----------------------------------------------------------------------
string sqlStr = "Select \"Person Name Field\", \"Age Field" from \"Old
Table\" ";
cmd.CommandText = sqlStr;
-----------------------------------------------------------------------
But when i assign this string to CommandText of OracleCommand object, it
gets an error ... because of backslashes.

How can i use quotation marks without backslash in a sql statement ?

I have been stuck on this, any help will be appreciated.

Thank you,

Adam

Can you do a DESC on the table in question and post that. I'm
suspicious of your sql above. Did someone really name all the fields
"something Field" and the tables "something Table"???


In general you should NOT use spaces in field or table names. Either
do it LikeThis or Like_This. That way you don't have to worry about
this sort of issue.

 

[Ben Voigt [C++ MVP]]

In general you should NOT use spaces in field or table names. Either

do it LikeThis or Like_This. That way you don't have to worry about
this sort of issue.

In postgresql, and probably some others, you need quoting not only for
spaces, but also to use uppercase letters.

 

[Wingot]

From: Ben Voigt [C++ MVP] [mailto:[email protected]]

Posted At: Friday, 7 December 2007 8:38 AM
Posted To: microsoft.public.dotnet.languages.csharp
Conversation: quotation marks in sql statement
Subject: Re: quotation marks in sql statement


In postgresql, and probably some others, you need quoting not only for
spaces, but also to use uppercase letters.

Postgresql needs quoting for uppercase letters? That seems pretty
primitive.

I would have to agree with DeveloperX on this one, never use spaces in
your table or field names, and especially never use double quotes in
them (I've seen it done).

Regards,
Wingot

 

[Ben Voigt [C++ MVP]]

From: Ben Voigt [C++ MVP] [mailto:[email protected]]
Posted At: Friday, 7 December 2007 8:38 AM
Posted To: microsoft.public.dotnet.languages.csharp
Conversation: quotation marks in sql statement
Subject: Re: quotation marks in sql statement

In general you should NOT use spaces in field or table names. Either
do it LikeThis or Like_This. That way you don't have to worry about
this sort of issue.

In postgresql, and probably some others, you need quoting not only for
spaces, but also to use uppercase letters.

Postgresql needs quoting for uppercase letters? That seems pretty
primitive.

Maybe I didn't explain that clearly enough -- without quoting postgresql
converts all identifiers to lower case, as a way of being case-insensitive.
As a result, you cannot match a field name that isn't lowercase without
quotes. Of course, the field name would have been converted to lowercase at
creation unless quotes were originally used.

I find that it's "a good thing" to quote all references to database objects
anyway, to distinguish them from keywords and built-in functions.