Update with unpredictable results... please help!!!

[BrunoDelFrate]

Hello,
I'm using a piece of code like this one, with SQL Server 7.0:

<%
query = &quot;SELECT rownumber FROM database WHERE ordernumber = 'XXX'&quot;
rs.open query
do until rs.eof
   rs(&quot;rownumber&quot;) = newvalue
   rs.update
   rs.movenext
loop
%>

It works... BUT it also updates some other records OUTSIDE the ones in the recordset, apparently at random!
But if I change the first line into this:

<% query = &quot;SELECT ordernumber, rownumber FROM database WHERE ordernumber=... %>

...it works perfectly!
Why???????
I've also tried to put a response.write rs("rownumber&quot before updating, and it looks like it finds the right records... is it a known bug? My table has no indexes and no keys - I cannot put keys because in some cases I need to have duplicates and to change key fields.

Thanks in advance!

Bruno Del Frate

 

[FengShui1998]

BrunoDelFrate;

I believe you need to specify the specific table FROM your database or else it will update all tables that have those column names. To be more specific,

"SELECT tablename.rownumber, tablename.ordernumber FROM tablename WHERE ordernumber = 'XXX'"

Cheers,
Fengshui1998

 

[BrunoDelFrate]

Hi Fengshui1998, and thanks for your reply.
Unfortunately the record updated outside my recordset are on the same table, and not on different tables with the same column name...

Bruno Del Frate

 

[foxbox]

Maybe i'm wrong (and confused because of the local heatwave) but you are trying to update the field rownumber. So why not use:

query = "UPDATE database SET rownummer = " & newvalue &_
" WHERE ordernumber = 'XXX'"
???
br
Gerard

 

[FengShui1998]

Bruno Del Frate,

My suggestion to you is to output the recordsets to see what records were selected. This would give you a better understanding of the query. Hope this helps.

fengshui1998

 

[BrunoDelFrate]

Hi foxbox,
Yes, your syntax of course works perfectly... but I was just trying to understand why that Update method didn't work properly - and why it worked perfectly with one more field in the SELECT! :-(
I've also tried to put a "response.write rs("rownumber&quot " before and after the update, and only the right records were shown as updated - but this wasn't true!
I'll try to download the latest MDAC and the Service Pack 3 for SQL Server 7.0 and I'll let you know...

Bruno Del Frate