Any SQL Guru's want to take a shot at this? H

Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Any SQL Guru's want to take a shot at this? H

  1. #1
    Join Date
    Dec 1969
    Posts
    36

    Default Any SQL Guru's want to take a shot at this? H

    Hi, I&#039;ve moved from an access db to SQL server 2000.<BR><BR>I have these two sql commands which work fine in access, but are giving me an error in SQL server.<BR><BR>sql statements:<BR>sqltext = "UPDATE SQLPeople SET Answer=&#039;"& Request.Form("Answer")&"&#039;, Comments=&#039;"& Request.Form("Comments")&"&#039;, DateUpdated=" & FormatDateTime(Now, 2) & " WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "<BR><BR>sqltext1 = "SELECT RID FROM SQLPeople WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "<BR>rsRecord.Open sqltext1, objConn, adOpenDynamic, adLockOptimistic<BR><BR>Error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e14&#039; <BR><BR>[Microsoft][ODBC SQL Server Driver][SQL Server]An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference<BR><BR>Im a bit stumped =(

  2. #2
    Join Date
    Dec 1969
    Posts
    1,765

    Default Did you remember to change your conn string

    ?<BR><BR>Did you search google?

  3. #3
    Join Date
    Dec 1969
    Posts
    1,765

    Default Take a look at this site:

    See if this helps any...<BR><BR>http://dbforums.com/archive/7/2002/01/4/265928

  4. #4
    Join Date
    Dec 1969
    Posts
    36

    Default RE: Did you remember to change your conn string

    Thanks for taking a look, yes i had already changed the connection string. I&#039;ve taken a look at the site you listed, but not sure what i&#039;m doing wrong here...


  5. #5
    Join Date
    Dec 1969
    Posts
    73

    Default how about a work-around?

    Write this into a stored procedure and see if it works...can&#039;t see why it wouldn&#039;t.<BR><BR>If it still doesn&#039;t, write that (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) into a local variable (in the sproc) and update based on that.<BR><BR>May as well take advantage of stored procedures anyway!

  6. #6
    Join Date
    Dec 1969
    Posts
    1,765

    Default Hmmmm.....

    I tried...but now I&#039;m stumped! <BR><BR>Sorry I wasn&#039;t of better assistance

  7. #7
    Join Date
    Dec 1969
    Posts
    39

    Default RE: Any SQL Guru's want to take a shot at thi

    Not sure, but try changing the &#039;=&#039; in your WHERE clause to &#039;IN&#039;.<BR><BR>sqltext1 = "SELECT RID FROM SQLPeople WHERE RID IN (SELECT MIN(RID) FROM People WHERE Answer IS NULL AND ReadingType=1)"<BR><BR>hope this helps! good luck.

  8. #8
    Join Date
    Dec 1969
    Posts
    480

    Default only thing I see is......

    The date needs &#039; around it in SQL

  9. #9
    Join Date
    Dec 1969
    Posts
    36

    Default RE: Any SQL Guru's want to take a shot at thi

    Tried that but no luck =( baffled...

  10. #10
    Join Date
    Dec 1969
    Posts
    3,195

    Default Ok I have a really silly question

    ;)<BR><BR>Have taken ASP/ADO out of the equation here and tested your SQL right from Query Analyzer or other isql tool.<BR><BR>In other words, I&#039;m assuming you have done a response.write on your SQL and have cut and pasted this in to Query analyzer:<BR><BR>example:<BR><BR>response.write(sq ltext)<BR>response.end<BR><BR>If you haven&#039;t, I strongly suggest that you do and work out your SQL problems there first.<BR><BR>Good luck<BR>Pete

Posting Permissions

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