Query-based update failed... row to update not found

[WorkerBeeJ]

Hello,

I'm having a problem with a script that I'm using to correct some typos in a sql database. I'm able to retrieve fields, but cannot update them. Here's the error I'm getting:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Query-based update failed because the row to update could not be found.

''''''''''Begin Partial Code''''''''''
WHILE NOT recSet.EOF
newStreet = recSet.Fields("street&quot
newStreetName = recSet.Fields("streetName&quot

for each regExpMatch in regExpr.Execute(newStreet)
dirtyBit = false
for i= 0 to 103
typo = corrections(i,0)
correction = corrections(i,1)
if regExpMatch = Typo then
dirtyBit = true
newStreet = Replace(newStreet,typo,correction)
newStreetName = Replace(newStreetName,typo,correction)
end if
next
if dirtyBit then
recSet.Fields("street&quot = newStreet
recSet.Fields("streetName&quot = newStreetName
recSet.Update '***Problem, row not found***
dirtyBit = false
end if
next
recSet.MoveNext
WEND
''''''''''End Partial Code''''''''''

The pattern for the regular expression is "\b\w+\b" (i.e., it finds whole words). The streetName for each row is equal to the street without it's direction. E.g.,
street: S Broadway Ave
streetName: Broadway Ave

If there's not enough info here, please let me know.
Any help would be greatly appreciated! Thanks!

 

[Tarwn]

Are you opening the entire table or are you doing a select statment?
Show us where you are creating the recordset,
-Tarwn "The problem with a kludge is eventually you're going to have to back and do it right." - Programmers Saying (The Wiz Biz - Rick Cook)
"Your a geek!" - My Girlfriends saying

 

[WorkerBeeJ]

I'm using a select statement. I'm sure that it works because I'm able to iterate through the recordset and print the rows with typos with response.write statements. I just can't update them.

I can post the entire file if it would be helpful. I just wasn't sure that anyone would actually read my post if it were too long.

''''''''''Begin Partial Code''''''''''
...
<!--#include file=&quot;../SQLADOConnection.asp&quot;-->
<%

cmdTemp.CommandText = &quot;SELECT street, streetname FROM addressTable&quot;

set recSet = Server.createObject(&quot;ADODB.Recordset&quot
recSet.Open cmdTemp, , adOpenKeyset, adLockOptimistic

...

corrections(5,0) = &quot;ACHILLIS&quot;
corrections(5,1) = &quot;ACHILLES&quot;
corrections(6,0) = &quot;ADMAS&quot;
corrections(6,1) = &quot;ADAMS&quot;
corrections(7,0) = &quot;ALLAMO&quot;
corrections(7,1) = &quot;ALAMO&quot;
corrections(8,0) = &quot;AMONS&quot;
corrections(8,1) = &quot;AMMONS&quot;
corrections(9,0) = &quot;ANTAIRES&quot;
corrections(9,1) = &quot;ANTARES&quot;
corrections(10,0) = &quot;ALTAIRE&quot;
corrections(10,1) = &quot;ALTAIR&quot;
corrections(11,0) = &quot;APPOLO&quot;
corrections(11,1) = &quot;APOLLO&quot;
corrections(12,0) = &quot;APOLO&quot;
corrections(12,1) = &quot;APOLLO&quot;
corrections(13,0) = &quot;APPOLLO&quot;
corrections(13,1) = &quot;APOLLO&quot;

...


''''''''''End Partial Code''''''''''

Again, THanks!

 

[Tarwn]

If I remember correctly you can't update a record that is a subset of a table. If you were to select * from the table than you may be able to run your update on the recordset.
-Tarwn &quot;The problem with a kludge is eventually you're going to have to back and do it right.&quot; - Programmers Saying (The Wiz Biz - Rick Cook)
&quot;Your a geek!&quot; - My Girlfriends saying

 

[WorkerBeeJ]

Turns out that my problem was that my record set was too large, and the db connection was timing out before I was updating.

Thanks for your help.