Formatting SQL in string format

[barrykellett]

I am trying to use the following SQL code:

SELECT t.Title, DCount('[username]','[guestbook]','[threadid] = ' & gb.threadid) AS CountAllReplies, gb.username
FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid=t.threadid
WHERE (((gb.Original)=False))
ORDER BY gb.thedate DESC , t.Title, DCount("[username]","[guestbook]","[threadid] = " & gb.threadid), gb.username;

In my ASP.net page but when i code it as a string:

Const strOleDb as String = "SELECT t.Title, DCount(""[username]"",""[guestbook]"",""[threadid] = "" & gb.threadid) AS CountAllReplies, gb.username" & _
                "FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid=t.threadid" & _
                "WHERE (((gb.Original)=False))" & _
                "ORDER BY gb.thedate DESC , t.Title, DCount(""[username]"",""[guestbook]"",""[threadid]" = " & gb.threadid), gb.username;"

I get the error:
Conversion from 'Boolean' to 'String' cannot occur in a constant expression.

 

[DaZZleD]

assuming the command you wrote in the first paragraph is correct, in ASP.NET you need:

Const strOleDb as String = "SELECT t.Title, DCount('[username]','[guestbook]','[threadid] = ' & gb.threadid) AS CountAllReplies, gb.username" & _
                "FROM guestbook AS gb INNER JOIN thread AS t ON gb.threadid=t.threadid" & _
                "WHERE (((gb.Original)=False))" & _
                "ORDER BY gb.thedate DESC , t.Title, DCount('[username]','[guestbook]','[threadid]' = & gb.threadid), gb.username;"

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[chiph]

Take a look at using ADO.NET parameters. Why?

1) It protects you from someone using a username of O'Brian

2) It protects you from SQL Injection attacks

3) Your system will perform faster because the database can cache your query

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first