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.
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
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.
'Create a database connection
Set db = Server.CreateObject("adodb.connection")
'Execute the INSERT statement and the SELECT @@IDENTITY
Set rcs = db.execute("insert into tablename (fields,..) " & _
"values (values,...);" & _
'Retrieve the @@IDENTITY value
new_identity = rcs(0)
So not clear on how to update the record in question with the record value?