DaniWeb Forum Index > Web Development > ASP

problem for a simple asp.net query

Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Feb 2005

Posts: 2

Reputation: tishr is an unknown quantity at this point

Solved Threads: 0

tishr

Offline

Newbie Poster

problem for a simple asp.net query

Feb 8th, 2005

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

•

Join Date: Mar 2005

Posts: 1

Reputation: pallab is an unknown quantity at this point

Solved Threads: 0

pallab

Offline

Newbie Poster

Re: problem for a simple asp.net query

Mar 8th, 2005

•

Originally Posted by 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

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

•

Join Date: May 2006

Posts: 66

Reputation: lordspace is an unknown quantity at this point

Solved Threads: 4

lordspace

Offline

Junior Poster in Training

Re: problem for a simple asp.net query

Mar 26th, 2007

varchar has a maximum of 8000 bytes.

Another possible hack is

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
SELECT some_field FROM some_table
 WHERE (some_text_field LIKE 'VALUE')
SELECT some_field FROM some_table
 WHERE (some_text_field LIKE 'VALUE')

SEO Software for forums and CMS - SEO Filter | My Web Development Blog

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: problem for a simple asp.net query

Mar 26th, 2007

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)

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
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