Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: Can any SQL Guru's help?

    Hi, I've moved from an access db to SQL server 2000.

    I have these two sql commands which work fine in access, but are giving me an error in SQL server.

    sql statements:

    sqltext = "UPDATE SQLPeople SET Answer='"& Request.Form("Answer")&"', Comments='"& Request.Form("Comments")&"', DateUpdated=" & FormatDateTime(Now, 2) & " WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "

    sqltext1 = "SELECT RID FROM SQLPeople WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "
    rsRecord.Open sqltext1, objConn, adOpenDynamic, adLockOptimistic

    objConn.Execute(sqltext)

    Error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [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

    Im a bit stumped =( Again, works fine in Access, but not SQL Server 2000

  2. #2
    Join Date
    May 2002
    Posts
    25
    i'd recommend rethinking your where statement for other reasons than sql server not liking it.

    i think this page might help you
    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

    or
    http://www.4guysfromrolla.com/webtec...122600-1.shtml

    or passing the id in a field in the <FORM>

    anyway, if you only want to change your sql, i think you just replace WHERE with HAVING and add GROUP BY and top

    select top 1 MIN(RID) as minRID from People GROUP BY RID having Answer IS NULL and ReadingType = 1

    Last edited by helpimstuck; 06-03-02 at 13:13.

  3. #3
    Join Date
    Jun 2002
    Posts
    2
    thanks for the help. I've checked those articles which both seem to create or capture a value for each record using identity@@. However, Im still not clear on how to update the record with that value in an ID column

    i'm doing my initial insert, then a select for identity@@ and i assume an update on the record with that value.

    not too clear on how this is done, the update portion.

    this is the example that is given on 4guys.
    **********
    Dim db,rcs,new_identity

    'Create a database connection
    Set db = Server.CreateObject("adodb.connection")
    db.Open "DSN=MyDSN"

    'Execute the INSERT statement and the SELECT @@IDENTITY
    Set rcs = db.execute("insert into tablename (fields,..) " & _
    "values (values,...);" & _
    "select @@identity").nextrecordset

    'Retrieve the @@IDENTITY value
    new_identity = rcs(0)
    ***********

    So not clear on how to update the record in question with the record value?

    thanks,

    j

  4. #4
    Join Date
    May 2002
    Posts
    25
    sorry, idont have time to look much into it right now, but i didnt use ADO for my updates..i did it in a stored procedure

  5. #5
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    I would call a stored proc for this:

    From your source code:

    sqlstr = "exec my_sp"

    And in the SP:
    ___
    INSERT...

    UPDATE ... WHERE idField = @@IDENTITY
    ___

    This makes you be sure you update the last created id in your table.

    If you need to make more transactions between INSERT and UPDATE:
    __
    INSERT...
    SET @mycurrentID = @@IDENTITY

    ...

    UPDATE... WHERE idField = @mycurrentID
    __
    Data Climber

Posting Permissions

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