Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Question Unanswered: Newbie with ASP and SQL

    Can someone help me I need help with coding this correctly as I continue to get error messages with my query?

    Code:
    strSQL = "SELECT Sum(Abs([MgrNo]=[MgrNo])) AS num_ppl, Sum(Abs([WorkQ1]="Strongly Agree"))+Sum(Abs([WorkQ1]="Agree")) AS Q1Favorable, Sum(Abs([WorkQ1]="Neither Agree nor Disagree")) AS Q1Neutral, Sum(Abs([WorkQ1]="Disagree"))+Sum(Abs([workq1]="Strongly Disagree")) AS Q1UnFavourable, Sum(Abs([WorkQ2]="Strongly Agree"))+Sum(Abs([WorkQ2]="Agree")) AS Q2Favorable, Sum(Abs([WorkQ2]="Neither Agree nor Disagree")) AS Q2Neutral, Sum(Abs([WorkQ2]="Disagree"))+Sum(Abs([workq2]="Strongly Disagree")) AS Q2UnFavourable, Sum(Abs([WorkQ3]="Strongly Agree"))+Sum(Abs([WorkQ3]="Agree")) AS Q3Favorable, Sum(Abs([WorkQ3]="Neither Agree nor Disagree")) AS Q3Neutral, Sum(Abs([WorkQ3]="Disagree"))+Sum(Abs([workq3]="Strongly Disagree")) AS Q3UnFavourable, Sum(Abs([WorkQ4]="Strongly Agree"))+Sum(Abs([WorkQ4]="Agree")) AS Q4Favorable, Sum(Abs([WorkQ4]="Neither Agree nor Disagree")) AS Q4Neutral, Sum(Abs([WorkQ4]="Disagree"))+Sum(Abs([workq4]="Strongly Disagree")) AS Q4UnFavourable, Sum(Abs([WorkQ5]="Strongly Agree"))+Sum(Abs([WorkQ5]="Agree")) AS Q5Favorable, Sum(Abs([WorkQ5]="Neither Agree nor Disagree")) AS Q5Neutral, Sum(Abs([WorkQ5]="Disagree"))+Sum(Abs([workq5]="Strongly Disagree")) AS Q5UnFavourable, Sum(Abs([WorkQ6]="Strongly Agree"))+Sum(Abs([WorkQ6]="Agree")) AS Q6Favorable, Sum(Abs([WorkQ6]="Neither Agree nor Disagree")) AS Q6Neutral, Sum(Abs([WorkQ6]="Disagree"))+Sum(Abs([workq6]="Strongly Disagree")) AS Q6UnFavourable, Sum(Abs([WorkQ7]="Strongly Agree"))+Sum(Abs([WorkQ7]="Agree")) AS Q7Favorable, Sum(Abs([WorkQ7]="Neither Agree nor Disagree")) AS Q7Neutral, Sum(Abs([WorkQ7]="Disagree"))+Sum(Abs([workq7]="Strongly Disagree")) AS Q7UnFavourable, Sum(Abs([WorkQ8]="Strongly Agree"))+Sum(Abs([WorkQ8]="Agree")) AS Q8Favorable, Sum(Abs([WorkQ8]="Neither Agree nor Disagree")) AS Q8Neutral, Sum(Abs([WorkQ8]="Disagree"))+Sum(Abs([workq8]="Strongly Disagree")) AS Q8UnFavourable, Sum(Abs([MgrQ1]="Strongly Agree"))+Sum(Abs([MgrQ1]="Agree")) AS Q1MgrFavorable, Sum(Abs([MgrQ1]="Neither Agree nor Disagree")) AS Q1MgrNeutral, Sum(Abs([MgrQ1]="Disagree"))+Sum(Abs([Mgrq1]="Strongly Disagree")) AS Q1MgrUnFavourable, Sum(Abs([MgrQ2]="Strongly Agree"))+Sum(Abs([MgrQ2]="Agree")) AS Q2MgrFavorable, Sum(Abs([MgrQ2]="Neither Agree nor Disagree")) AS Q2MgrNeutral, Sum(Abs([MgrQ2]="Disagree"))+Sum(Abs([Mgrq2]="Strongly Disagree")) AS Q2MgrUnFavourable, Sum(Abs([MgrQ3]="Strongly Agree"))+Sum(Abs([MgrQ3]="Agree")) AS Q3MgrFavorable, Sum(Abs([MgrQ3]="Neither Agree nor Disagree")) AS Q3MgrNeutral, Sum(Abs([MgrQ3]="Disagree"))+Sum(Abs([Mgrq3]="Strongly Disagree")) AS Q3MgrUnFavourable, Sum(Abs([MgrQ4]="Strongly Agree"))+Sum(Abs([MgrQ4]="Agree")) AS Q4MgrFavorable, Sum(Abs([MgrQ4]="Neither Agree nor Disagree")) AS Q4MgrNeutral, Sum(Abs([MgrQ4]="Disagree"))+Sum(Abs([Mgrq4]="Strongly Disagree")) AS Q4MgrUnFavourable, Sum(Abs([MgrQ5]="Strongly Agree"))+Sum(Abs([MgrQ5]="Agree")) AS Q5MgrFavorable, Sum(Abs([MgrQ5]="Neither Agree nor Disagree")) AS Q5MgrNeutral, Sum(Abs([MgrQ5]="Disagree"))+Sum(Abs([Mgrq5]="Strongly Disagree")) AS Q5MgrUnFavourable, Sum(Abs([MgrQ6]="Strongly Agree"))+Sum(Abs([MgrQ6]="Agree")) AS Q6MgrFavorable, Sum(Abs([MgrQ6]="Neither Agree nor Disagree")) AS Q6MgrNeutral, Sum(Abs([MgrQ6]="Disagree"))+Sum(Abs([Mgrq6]="Strongly Disagree")) AS Q6MgrUnFavourable, Sum(Abs([MgrQ7]="Strongly Agree"))+Sum(Abs([MgrQ7]="Agree")) AS Q7MgrFavorable, Sum(Abs([MgrQ7]="Neither Agree nor Disagree")) AS Q7MgrNeutral, Sum(Abs([MgrQ7]="Disagree"))+Sum(Abs([Mgrq7]="Strongly Disagree")) AS Q7MgrUnFavourable, Sum(Abs([MgrQ8]="Strongly Agree"))+Sum(Abs([MgrQ8]="Agree")) AS Q8MgrFavorable, Sum(Abs([MgrQ8]="Neither Agree nor Disagree")) AS Q8MgrNeutral, Sum(Abs([MgrQ8]="Disagree"))+Sum(Abs([Mgrq8]="Strongly Disagree")) AS Q8MgrUnFavourable, Sum(Abs([MgrQ9]="Strongly Agree"))+Sum(Abs([MgrQ9]="Agree")) AS Q9MgrFavorable, Sum(Abs([MgrQ9]="Neither Agree nor Disagree")) AS Q9MgrNeutral, Sum(Abs([MgrQ9]="Disagree"))+Sum(Abs([Mgrq9]="Strongly Disagree")) AS Q9MgrUnFavourable, Sum(Abs([MgrQ10]="Strongly Agree"))+Sum(Abs([MgrQ10]="Agree")) AS Q10MgrFavorable, Sum(Abs([MgrQ10]="Neither Agree nor Disagree")) AS Q10MgrNeutral, Sum(Abs([MgrQ10]="Disagree"))+Sum(Abs([Mgrq10]="Strongly Disagree")) AS Q10MgrUnFavourable, Sum(Abs([MgrQ11]="Strongly Agree"))+Sum(Abs([MgrQ11]="Agree")) AS Q11MgrFavorable, Sum(Abs([MgrQ11]="Neither Agree nor Disagree")) AS Q11MgrNeutral, Sum(Abs([MgrQ11]="Disagree"))+Sum(Abs([Mgrq11]="Strongly Disagree")) AS Q11MgrUnFavourable, Sum(Abs([MgrQ12]="Strongly Agree"))+Sum(Abs([MgrQ12]="Agree")) AS Q12MgrFavorable, Sum(Abs([MgrQ12]="Neither Agree nor Disagree")) AS Q12MgrNeutral, Sum(Abs([MgrQ12]="Disagree"))+Sum(Abs([Mgrq12]="Strongly Disagree")) AS Q12MgrUnFavourable, Sum(Abs([MgrQ13]="Strongly Agree"))+Sum(Abs([MgrQ13]="Agree")) AS Q13MgrFavorable, Sum(Abs([MgrQ13]="Neither Agree nor Disagree")) AS Q13MgrNeutral, Sum(Abs([MgrQ13]="Disagree"))+Sum(Abs([Mgrq13]="Strongly Disagree")) AS Q13MgrUnFavourable, Sum(Abs([MgrQ14]="Strongly Agree"))+Sum(Abs([MgrQ14]="Agree")) AS Q14MgrFavorable, Sum(Abs([MgrQ14]="Neither Agree nor Disagree")) AS Q14MgrNeutral, Sum(Abs([MgrQ14]="Disagree"))+Sum(Abs([Mgrq14]="Strongly Disagree")) AS Q14MgrUnFavourable, Sum(Abs([MgrQ15]="Strongly Agree"))+Sum(Abs([MgrQ15]="Agree")) AS Q15MgrFavorable, Sum(Abs([MgrQ15]="Neither Agree nor Disagree")) AS Q15MgrNeutral, Sum(Abs([MgrQ15]="Disagree"))+Sum(Abs([Mgrq15]="Strongly Disagree")) AS Q15MgrUnFavourable " _
    		& "FROM ws1 " _
    		& "WHERE MgrNo  LIKE '" & Replace(Request.ServerVariables("HTTP_EMPLOYEENUMBER"), "'", "''") & "' " _
    		& "ORDER BY MgrNo;"
    I continue to keep getting this error message come up
    Error Type:
    Microsoft VBScript compilation (0x800A0401)
    Expected end of statement

    If anyone can help me with this I would greatly appreciate it.. I'm pretty sure it has to do with the way I have coded my SQL statment but I don't know what I am doing wrong as I am a complete Newbie to VBscript.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    I think you get this error because of the double quotes " that you put in the middle of your query around string values : the first one is interpreted as the end of your query. You can maybe try simple quotes. For example :

    Sum(Abs([WorkQ1]='Strongly Agree')

    instead of

    Sum(Abs([WorkQ1]="Strongly Agree")

    The cause is most probably here, but I'm not sure about the solution.

    Regards,

    RBARAER

  3. #3
    Join Date
    Sep 2004
    Posts
    2

    changed the double quotations but still get error message?

    I changed all the double quotes to singles and now i get this error message?

    You tried to execute a query that does not include the specified expression 'Mgrno' as part of an aggregate function.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    That error has something to do with the way you are summing things in your sql statement and the ordering I think...

    I could try and figure it out but I think you'd have a lot more luck if you posted the sql string and the error message in the MS SQL board rather then here.

    Normally I would help but I am flat out atm...

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Maybe it comes from the first sum :

    Sum(Abs([MgrNo]=[MgrNo]))

    What are you exactly doing here ? Why "[MgrNo]=[MgrNo]" ?

    Regards,

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •