problem for a simple asp.net query

[tishr]

could any one plz help me for this query. i have tried a lot but only the first one is working. see my code:

(1)

CommandText = _
"SELECT sr.ResponseAnswerID, count(sr.RespondantID) from SURVEY_RESPONSE sr INNER JOIN ANSWER a on " _
& "sr.ResponseAnswerID=a.AnswerID where a.QuestionID= 284 " _
& "group by sr.ResponseAnswerID"

(2)
CommandText = _
"SELECT a.AnswerDescription, count(sr.RespondantID) from SURVEY_RESPONSE sr INNER JOIN ANSWER a on " _
& "sr.ResponseAnswerID=a.AnswerID where a.QuestionID= 284 " _
& "group by a.AnswerDescription"

------
for the first one it is working fine but displaying only its id. what i want is AnswerDescription instead of ResponseAnswerID.

the error for (2) is
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

i have tried a lot, using select in another select statement, having into, like.... but none is working

hope to have a quick response. thanks

antish

[pallab]

could any one plz help me for this query. i have tried a lot but only the first one is working. see my code:

(1)

CommandText = _ "SELECT sr.ResponseAnswerID, count(sr.RespondantID) from SURVEY_RESPONSE sr INNER JOIN ANSWER a on " _ & "sr.ResponseAnswerID=a.AnswerID where a.QuestionID= 284 " _ & "group by sr.ResponseAnswerID"

(2) CommandText = _ "SELECT a.AnswerDescription, count(sr.RespondantID) from SURVEY_RESPONSE sr INNER JOIN ANSWER a on " _ & "sr.ResponseAnswerID=a.AnswerID where a.QuestionID= 284 " _ & "group by a.AnswerDescription"

------ for the first one it is working fine but displaying only its id. what i want is AnswerDescription instead of ResponseAnswerID.

the error for (2) is The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

i have tried a lot, using select in another select statement, having into, like.... but none is working

hope to have a quick response. thanks

antish

Hello,

You have any field in either table which is having a data type of text or ntext or image. If you used text or ntext field, please change the datatype to varchar and then try the query again.

All the best

Pallab

[lordspace]

varchar has a maximum of 8000 bytes.

Another possible hack is

SELECT some_field FROM some_table
 WHERE (some_text_field LIKE 'VALUE')

[hollystyles]

Do the aggregation in a subquery, then join on to the answer table from there to pull the description (Sql Datatypes text ntext and image can't be used in GROUP BY clauses)

select 
    oa.AnswerDescription, 
    srCount 
from 
    (
        SELECT 
            sr.ResponseAnswerID, 
            count(sr.RespondantID) as srCount  
        from 
            SURVEY_RESPONSE sr 
            INNER JOIN ANSWER a on sr.ResponseAnswerID=a.AnswerID            
            where a.QuestionID= 284 
       group by 
            sr.ResponseAnswerID
    ) sub 
    join  ANSWER oa on sub.ResponseAnswerID = oa.AnswerID