Update error...

[adamsoderqvist]

Sometimes when I try to UPDATE an already existing record in my Access DB I get this error msg. The row is there , and it hasn't been a problem updating before. Why is this happening?

[Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Query-based update failed because the row to update could not be found.

And I use this: (this is just ONE example, I have several other update scripts where this happens, NOT ALL THE TIME though, but just on some records and on some occasions...)

objRS("LastLogin&quot = Date()
objRS("LoginTime&quot = Time()
objRS.Update
]

Adding and deleting records is not a problem.

Pls, hlp!

//Adam

 

[harmmeijer]

Did you check if there is a current record to update?
thread333-288783 red]if objRS.bof and objRS.eof then 'check if the query returned a record[/color]
objRS("LastLogin&quot = Date()
objRS("LoginTime&quot = Time()
objRS.Update
else
response.write "Oh my query is wrong again, empty recordset returned"
end if

 

[harmmeijer]

I think it could have something todo whith your mdac or with the value that you are trying to insert into the lastlogin or logintime fields.
I would print the sql statement to a file or to the browser when an error occures:
on error resume next
objRS.update
if err.number then
' wite to file with the filesystem object (Scripting.FileSystemObject)
' or just response.write the sql query
' if this query runs in your access database than your
' MDAC is probebly due for an update.



I tested my other post on a recordset that comes from sql server and the error wil allready be raised when I try to set the value for lastlogin.

ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 

[adamsoderqvist]

That's just it! The record is there! And I've also had errors when trying to update text etc. And as I said: this is not a persistant error, but it occurs now and then. Sometimes, I try to update records based on an ID numer, and sometimes it works, and somtimes it don't. There's no pattern what I can see...

 

[harmmeijer]

Ok your record is there or you would have another error.
I searched google on this error and ended up with a lot of sites (so you are not the only one).
I gues when google's search robot made an inventory of the sites these sites raised the same error.

Searched Microsoft on this error and found nothing (as usual).

The only thing I can think of is updating your MDAC.
Or update records by executing SQL update/delete/insert commands on a command object.

 

[adamsoderqvist]

OK, that helped! thanx everyone!

 

[adamsoderqvist]

NOP! I was yoo fast.. The problem persists.

How would an "Execute"-command be constucted to match my code examples?

 

[harmmeijer]

You have an open connection called conn.

to update you need the ID of the row you want to update or some where statement that wil return one row. If the where statement returns more than one row then all rows will be updated.

Dim comm As ADODB.Command
comm.ActiveConnection = conn
comm.CommandText = "UPDATE table set field1 ='" & request("field1&quot & "', field2 = '" & request("field2&quot & "' WHERE ID = " & request("ID&quot
comm.Execute
comm.CommandText = "UPDATE table set field1 ='" & request("field1&quot & "', field2 = '" & request("field2&quot & "' WHERE ID = " & request("otherID&quot
comm.Execute
' Insert goes like so:
comm.CommandText = "INSERT INTO table (field1, field2) VALUES('" & request("field1&quot & "','" & request("field2&quot & "')"
comm.Execute