469,602 Members | 1,741 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

simple sql quote problem!

Can someone tell me how I convert this simple SQL statement so I can use it
in ASP.NET???

I have an issue with the quotation marks and wondered if there is a simple
rule for converting the sql statement so if can be used in ASP.NEt!

Thanks

... SQL String

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE Display = 1
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder
Nov 19 '05 #1
5 1577
//Replace embedded ' with '' (two single quotes)
string sql = sql.Replace(@"'", @"''");
---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"Tim::.." wrote:
Can someone tell me how I convert this simple SQL statement so I can use it
in ASP.NET???

I have an issue with the quotation marks and wondered if there is a simple
rule for converting the sql statement so if can be used in ASP.NEt!

Thanks

.. SQL String

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE Display = 1
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder

Nov 19 '05 #2
That wasn't exactly my problem!

The problem is I can't get the sql into a string!

This is the closest I got to it!

"SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src='" & Chr(34) &
"ImgLeft" & Chr(34) & "' /> ' END) + [Text] + (CASE WHEN ImgRight IS NULL
THEN '' ELSE ' <img src='" & Chr(34) & "ImgRight" & Chr(34) & "' />' END) AS
Text, ISNULL(ToolTip, '') AS ToolTip, ISNULL(Url, '') AS Url, [ID], (SELECT
COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS SubMenuItemsCount() FROM
APP_Menu A WHERE(Display = 1) AND ParentId = @ParentID ORDER BY ParentID,
DisplayOrder"

Thanks

"Cowboy (Gregory A. Beamer) - MVP" wrote:
//Replace embedded ' with '' (two single quotes)
string sql = sql.Replace(@"'", @"''");
---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"Tim::.." wrote:
Can someone tell me how I convert this simple SQL statement so I can use it
in ASP.NET???

I have an issue with the quotation marks and wondered if there is a simple
rule for converting the sql statement so if can be used in ASP.NEt!

Thanks

.. SQL String

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE Display = 1
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder

Nov 19 '05 #3
> The problem is I can't get the sql into a string!

Looks like a string to me. A String is an array of characters. A String
literal is a sequence of characters enclosed with double quotes. When you
replace the single quotes in it with doubled single quotes, you make it
readable to the database. You should also (as it seems you have) replace
double quotes with CHR(34). When you enclose the whole mess inside a pair of
double quotes, you have a string.

As a side note: You'd be better off leaving all that logic and
string-building out of your SQL Statement. You can replace nulls in your
application with whatever you need. All that logic in a SQL Statement is
going to slow things down.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

"Tim::.." <myatix_at_hotmail.com> wrote in message
news:7A**********************************@microsof t.com...
That wasn't exactly my problem!

The problem is I can't get the sql into a string!

This is the closest I got to it!

"SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src='" & Chr(34) &
"ImgLeft" & Chr(34) & "' /> ' END) + [Text] + (CASE WHEN ImgRight IS NULL
THEN '' ELSE ' <img src='" & Chr(34) & "ImgRight" & Chr(34) & "' />' END)
AS
Text, ISNULL(ToolTip, '') AS ToolTip, ISNULL(Url, '') AS Url, [ID],
(SELECT
COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS SubMenuItemsCount()
FROM
APP_Menu A WHERE(Display = 1) AND ParentId = @ParentID ORDER BY ParentID,
DisplayOrder"

Thanks

"Cowboy (Gregory A. Beamer) - MVP" wrote:
//Replace embedded ' with '' (two single quotes)
string sql = sql.Replace(@"'", @"''");
---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"Tim::.." wrote:
> Can someone tell me how I convert this simple SQL statement so I can
> use it
> in ASP.NET???
>
> I have an issue with the quotation marks and wondered if there is a
> simple
> rule for converting the sql statement so if can be used in ASP.NEt!
>
> Thanks
>
> .. SQL String
>
> SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft +
> '"
> /> ' END) + [Text] +
> (CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '"
> />'
> END) AS [Text],
> ISNULL(ToolTip, '') AS ToolTip,
> ISNULL(Url, '') AS Url,
> [ID],
> (SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
> SubMenuItemsCount
> FROM APP_Menu A
> WHERE Display = 1
> AND ParentId = @ParentID
> ORDER BY ParentID, DisplayOrder

Nov 19 '05 #4
try using a stringbuilder class.

"Tim::.." <myatix_at_hotmail.com> wrote in message
news:A3**********************************@microsof t.com...
Can someone tell me how I convert this simple SQL statement so I can use
it
in ASP.NET???

I have an issue with the quotation marks and wondered if there is a simple
rule for converting the sql statement so if can be used in ASP.NEt!

Thanks

.. SQL String

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE Display = 1
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder

Nov 19 '05 #5
Try this:

string sql = "SELECT (CASE WHEN ImgLeft IS NULL THEN '''' ELSE ''<img
src=\"'' + ImgLeft + ''\" /> '' END) + [Text] + (CASE WHEN ImgRight IS NULL
THEN '''' ELSE '' <img src=\"'' + ImgRight + ''\" />'' END) AS
[Text], ISNULL(ToolTip, '''') AS ToolTip, ISNULL(Url, '''') AS
Url, [ID], (SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount FROM APP_Menu A WHERE Display = 1 AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder)";

or this

System.Text.StringBuilder sql = new System.Text.StringBuilder();

sql.Append("SELECT (CASE WHEN ImgLeft IS NULL THEN '''' ELSE ''<img src=\"''
+ ImgLeft + ''\" /> '' END) + [Text] + (CASE WHEN ImgRight IS NULL THEN ''''
ELSE '' <img src=\"'' + ImgRight + ''\" />'' END) AS [Text], ISNULL(ToolTip,
'''') AS ToolTip, ISNULL(Url, '''') AS Url, [ID], (SELECT COUNT(*) FROM
APP_Menu B WHERE B.ParentID = A.ID) AS SubMenuItemsCount FROM APP_Menu A
WHERE Display = 1 AND ParentId = @ParentID ORDER BY ParentID, DisplayOrder)");
Basically what I did was use the \ (string literal escape) and used '' in
place of any ' as Greg suggested. I do agree with Kevin's statement
regarding the slowness of the sql statement, but that's beyond the scope of
your question.

HTH

Jeff Davis

"Tim::.." wrote:
Can someone tell me how I convert this simple SQL statement so I can use it
in ASP.NET???

I have an issue with the quotation marks and wondered if there is a simple
rule for converting the sql statement so if can be used in ASP.NEt!

Thanks

.. SQL String

SELECT (CASE WHEN ImgLeft IS NULL THEN '' ELSE '<img src="' + ImgLeft + '"
/> ' END) + [Text] +
(CASE WHEN ImgRight IS NULL THEN '' ELSE ' <img src="' + ImgRight + '" />'
END) AS [Text],
ISNULL(ToolTip, '') AS ToolTip,
ISNULL(Url, '') AS Url,
[ID],
(SELECT COUNT(*) FROM APP_Menu B WHERE B.ParentID = A.ID) AS
SubMenuItemsCount
FROM APP_Menu A
WHERE Display = 1
AND ParentId = @ParentID
ORDER BY ParentID, DisplayOrder

Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

27 posts views Thread by one man army | last post: by
8 posts views Thread by rdrink | last post: by
5 posts views Thread by Chelong | last post: by
7 posts views Thread by Dustin MacDonald | last post: by
5 posts views Thread by Nightfall | last post: by
30 posts views Thread by galiorenye | last post: by
10 posts views Thread by Phillip Taylor | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.