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

    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



    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
    i'd recommend rethinking your where statement for other reasons than sql server not liking it.

    i think this page might help you


    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
    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?



  4. #4
    Join Date
    May 2002
    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
    Montréal, Canada
    I would call a stored proc for this:

    From your source code:

    sqlstr = "exec my_sp"

    And in the SP:

    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:
    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