Can any SQL Gurus 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 2000i'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/webtech/tips/t122600-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|||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|||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|||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
__