Multiple update including ORDER BY and using objrs.update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple update including ORDER BY and using objrs.update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-03 : 09:02:29

Marwin writes "I need to perform a multiple update. I have included SOME of the script. Basically, it used to work beautifully in Access but now using SQL server 2000, I am getting an error that the Current Recordset does not support updating. If I remove the ORDER By clause then it works but once I leave it there, it gives me an error

sql = "SELECT * FROM TblTourDates WHERE (TblTourDates.TourID ='"&ID&"') AND (TblTourDates.OptionID ='"&OptionID&"') ORDER BY CounterMy"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3

rs.movefirst

For i=1 to TotalNo

if len(vardd) < 1 then
RS(varqty)="999"
else
RS(varqty)=vardd
end if

Next

RS.UPDATE

rs.movenext

Gurus, any clues?"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-03 : 09:26:22

Oh my.

First, this is an ADO question rather than a SQL Server question. Your error message regarding a non-updatable recordset has nothing to do with SQL Server. The options you specified to open the recordset are what is preventing you from updating the recordset.

Second, it is poor design practice to use the numeric forms of constants; no one here is going to know what the two '3' values mean in your call to rs.Open().

Third, you can perform this update with a single DML statement - less code and considerably greater performance.

You haven't posted any DDL but the update statement would look roughly like the following:

update tblTourDates
set varqty = case when len(vardd) < 1 then '999' else vardd end
where tourID = @tourID and optionID = @optionID

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources