 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / June 2007Error message on assignment line before rs.update
' I am getting an error when I try to update the value of Posted to 1. Set rsTenders = New ADODB.Recordset Set rsTenders.ActiveConnection = Cnxn rsTenders.CursorLocation = adUseClient rsTenders.CursorType = adOpenKeyset rsTenders.LockType = adLockBatchOptimistic rsTenders.Open queryTenders, Cnxn If Not rsTenders.EOF Then rsTenders.MoveFirst While Not rsTenders.EOF ID = rsTenders!ID Posted = rsTenders!Posted PostTenderID = rsTenders!PostTenderID RECID = rsTenders!RECID OrderID = rsTenders!OrderID TenderType = rsTenders!TenderType TenderAmount = rsTenders!TenderAmount CCNum = rsTenders!CCNum CCExp = rsTenders!CCExp 'Process goes here 'This command errors out. If I comment it out, the update statement works. 'What am I doing wrong? rsTenders!Posted = 1 rsTenders.Update rsTenders.MoveNext Wend End if Thanks for any help on this. All I want to do is read through a table, process each record and then update a field showing that it was processed. Jean > ' I am getting an error when I try to update the value of Posted to 1. > [quoted text clipped - 41 lines] > > Jean What's the actual error? Are you enumerating the Cnxn.Errors Collection? What are the data types for Posted? The field "Posted"? -ralph > > ' I am getting an error when I try to update the value of Posted to 1. > > [quoted text clipped - 50 lines] > >The data types are as follows: ID as Integer Posted as Integer PostTenderID as Integer RECID as Integer OrderID as Integer TenderType as string TenderAmount as currency CCNum as string CCExp as string The actual message is: Run-time error '-2147217887 (80040e21)': Multiple-step operation generated errors. Check each status value. I am not enumerating the errors collection. Thanks for the reply. Jean > > > ' I am getting an error when I try to update the value of Posted to 1. > > > [quoted text clipped - 71 lines] > > Jean First. Always enumerate the Errors Collection. Subsequent messages may provide more information. https://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx I assume queryTenders is a SQL Statement? (I should have asked earlier). The .Update method works by ADO creating (behind the scenes) its best guess as to what an Update statement would look like based on the Query. This error commonly occurs if due to database/table constraints or query criteria an unambigous update statement can not be constructed. It can also occur if one of the fields you are updating doesn't contain the proper datatype. But it doesn't look like this is a problem in your case as Strings and Integers are hard to mismatch. Are you sure Posted is an "Integer" in the Table? As a quick check write your own Update Query based on the new values and see if it works as expected. Remember you are only changing one field - but ADO is going to be using the entire query to build its criteria for updating THAT record. If the above doesn't help, post your query, the DDL for the table/s involved, and the specific database. hth -ralph > > > > ' I am getting an error when I try to update the value of Posted to 1. > > > > [quoted text clipped - 100 lines] > hth > -ralph Hi Ralph, Thanks for your help. I went ahead and wrote a second ADODB.Command to do the update independently of that record set and used the record ID to single out the record in question. The error was probably some kind of ambiguity and I had read about using a simple SQL update and it seems to be working. Undoubtedly I will go back to this at some time and try tomake the .update work but I spent all day on this and have to get my project done. I'll take your suggestions and see what i come up with. Thanks again. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.