simple sql quote problem!

[Guest]

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

 

[Guest]

//Replace embedded ' with '' (two single quotes)
string sql = sql.Replace(@"'", @"''");


---

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

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

 

[Guest]

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

//Replace embedded ' with '' (two single quotes)
string sql = sql.Replace(@"'", @"''");


---

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

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

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

 

[Kevin Spencer]

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.

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

//Replace embedded ' with '' (two single quotes)
string sql = sql.Replace(@"'", @"''");


---

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

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

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

 

[Vaibhav]

try using a stringbuilder class.

 

[Guest]

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