Ad:

ASP Discussion Thread
Unsolved
Views: 25125

Feb 8th, 2005

problem for a simple asp.net query

Expand Post »

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

Similar Threads

Simple ASP.Net Login Page using C# in C#
Updated : Simple ASP.Net Login Page in ASP.NET
Simple ASP.Net Login Page (Using VB.Net) in ASP.NET

tishr

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2005

Permalink

Mar 8th, 2005

Re: problem for a simple asp.net query

Quote 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

pallab

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2005

Permalink

Mar 26th, 2007

Re: problem for a simple asp.net query

varchar has a maximum of 8000 bytes.

Another possible hack is

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')

lordspace

Reputation Points: 18

Solved Threads: 6

Junior Poster in Training

Offline

90 posts
since May 2006

Permalink

Mar 26th, 2007

Re: problem for a simple asp.net query

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)

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

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP Forum Timeline: how to get network login user name

Next Thread in ASP Forum Timeline: How can i construct a database?

DaniWeb Message

Cancel Changes

User Name
Password