"Multiple-step operation generated errors. Check each status value."

Steve-0

I am getting the following error when trying to update an existing Foxpro database using the VFPOLEDB.1 provider and ADO: "Multiple-step operation generated errors. Check each status value."

I have tried about every combination of cursor types and locations but cannot get it to work. Here is my code:

Dim ADODBConnection As New ADODB.Connection()

ADODBConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=F:\xmgmt\data\client.dbc"

ADODBConnection.Mode = ADODB.ConnectModeEnum.adModeReadWrite

ADODBConnection.Open()

Recordset = New ADODB.Recordset

With Recordset

.ActiveConnection = ADODBConnection

.CursorType = ADODB.CursorTypeEnum.adOpenDynamic

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.LockType = ADODB.LockTypeEnum.adLockOptimistic

.Open("SELECT recordnumber FROM [events] WHERE recno <=0")

End With

Dim CurrentRecordNumber As Integer = 0

While Not Recordset.EOF

CurrentRecordNumber += 1

Recordset.Fields.Item("recordnumber").Value = CurrentRecordNumber 'The field 'recordnumber' is of type 'Numeric'

Recordset.Update() 'This is where I receive the error

Recordset.MoveNext()

End While

Any help would be greatly appreciated. I have searched all over the web for any answer but have come up with nothing.

Steve

Carl Warner

Before we start assuming things, what version of VFP data is in that client.dbc and are you in fact using the very latest OLE DB Provider for VFP Each of the versions of the OLE DB Provider for VFP have their own quirks and I would not want to assume the baseline for everything is the latest over something you may be using that is different.

The very latest OLE DB Provider for VFP is here for free download, in case you don't yet have it:

Microsoft OLE DB Provider for Visual FoxPro

http://www.microsoft.com/downloads/details.aspx FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en

CetinBasoz

As Carl said be sure you have latest OLEDB driver. However with the latest or older your operation is suspicious enough already.

Since you didn't give any details just guessing what might have happened:

1) where recno <= 0. A suspicious where clause. Probably you really have a field called recno, I don't know. If it's really there then this is likely to return a series of records and that would be your real intention.

2) You seem to be trying to set recordnumber 1...N sequentially in the retrieved set. So far so good. However there is no clue that recordnumber fields already contain any unique values outside the range of your update. ie: They might be already:

0,0,0....

or:

1,2,3,...20

In each case there is no primary key to follow the updates. Consider first case:

It goes and makes a call to update it to 1. Since all are 0 they all match the update situation and are set to 1,1,1,1,... Next step still expects to find a value of 0 to update but it's gone. IOW just after first update() all set is updated to 1,1,.... It should update a single record but it updated N records.

In the latter case situation is not much different. Your set is unique, however you can't be sure that other existing records collide with those values or not. Say existing data do not have 1,2,3 but has 4 (outside the set you selected to update). 1,2,3 updates and 4 causes the error (actually it causes error before that, as soon as 1 is updated to 2, there are now two 2 to update in next try).

In summary since ADO doesn't follow the positioning via some sort of physical location, it has to do that via key fields that it can use. Solution is to include your key field in select.

Steve-0

I am using the latest OLE DB Provider (1.1) but am unsure about the version of the data in the client.dbc. Let me give you some background: I am definately not that familiar with Foxpro Databases. I am trying to work with a database that has been provided by a vendor. When the vendor installed the program they also installed a version of Visual Foxpro so I am able to open the database using Visual Foxpro 5.0 (very old I know. Maybe this is the problem ) Anyway, I appreciate any help here.

Steve-0

Let me give some background: I am trying to work with a Foxpro database that is the backend to one of our providers programs. I am not that familiar with Foxpro databases. I noticed that none of the tables in the database seem to have any keys defined (maybe this was a limitation of Foxpro when the database was designed. It seems that this was developed with Foxpro 5.0) I am trying to work with the database using an ORM tool that requires the definition of a key (not necessarily on the database but at least some field that will distinguish it as a unique identifier) Many of the tables in the database have a field called 'recno' (which I would assume would be somewhat of a unique ID) but all of the values were '0'. Maybe this field was never being updated I tried to originally update this field but was receiving this error so I thought that maybe this was a 'read only' field or something (again, back to my in-experience with Foxpro databases). So I added a new column of type 'Numeric' called 'recordnumber' which I was going to use as the unique field. The only issue was to populate it. This is where I am receiving this error. Every time that my program accesses this table I want to first check it using ADO to make sure that each record has an incremented value and then use custom objects with my ORM tool to acually read/manipulate/display the data within the program. With that in mind let me reply to your comments:

1) The WHERE clause is so that I only get the records which do not have a unique ID yet. Upon opening of the database for the first time, none of the records have a unique ID so the value's will be 1, 2, 3....N. In subsequent openings, I will check for the Max 'recordnumber' and use that as my starting point for the RecordNumber field. I had edited this code out for simplicity but be assured that I am checking for a max value and incrementing accordingly.

2) Hmm... I guess I was un-aware that a recordset required a key to update the database. This is exactly why I was using ADO because the tables did not have any keys defined, with no column or group of columns that could be used to create a unique identifier. If this is the case then is there any way to update the database with some other technology by simply going row by row and changing a specific value

Thanks for your help!

Carl Warner

I can't read your whole post nor reply to everything right now since I have to leave in two minutes.

However, I see mention of RECNO() which is a reference to a FoxPro table record number. Just to clarify it (I hope) for you, that is a physical reference to the order of records in a table and is treated more like a virtual column/field. It does not exist as part of the structure/schema of a table. And because it is possible to remove deleted records from a table, a record that had one record number at one given time may have a different record number reference in another session. But, during an active sesssion where the data is shared, those record number references will stay the same. In order for those record numbers to change, every user would have to be out of at least that table, if not the entire database, and then a process could be run in exclusive mode (non-shared) where all records marked for deletion could be removed and hence the record number references would change. As I said, they stand form in an active session and can be cleverly used in a dynamic manner to help you to accomplish many tasks. That said, it generally isn't a great idea to use the record number as a key and better to use a SQL ALTER TABLE command to go ahead and create a key on a legitimate field/column within a table. Certainly, there must be some field/column with the table/dbf that meets the definition of a good candidate for a key. What kind of table would that be if it didn't have that

Later...

dni

May be the field called RECNO is a autoincrement field "made" with a trigger (in vfp 5); that is the reason why it is unique indexed. I think you need to "populate" the field with "unique" values for RECNO for each record.

Steve-0

Well.... what you described is pretty much exactly what I'm trying to do (populate the field with a unique value). When I try to set the value (see code from original post) of the field I am receiving this error. I am trying to do this using the VFPOLEDB provider because I am accessing the database within a VB app that I am developing. Thanks for any help!

Steve-0

That is exactly my though!! What kind of table is this that doesn't have a field that uniquely identifies each row Sad, but true. These tables do not have any field or even a combination of fields that could uniquely identify each row. This is exactly the reason I am trying to create my own field and populate it. Thanks for any help!!

dni

First you may check if you have enought nretwork "rights" to modify database...

dni

... Second you may check:

http://support.microsoft.com/kb/316910

Steve-0

I am confident that I do have the necessary rights to modify the database. I checked the link and although it has some merit I would prefer to do it without using a new table and stored procedure. Thanks.

dni

Then, may be the links will help you:

http://support.microsoft.com/kb/316910

http://support.microsoft.com/ id=300382

http://support.microsoft.com/default.aspx scid=kb;en-us;299820

Carl Warner

I'm a bit lost in all of the threads here. So excuse me for going over what may have already been covered.

We can assume that the table (and maybe the entire database) is not very well designed. And despite its shortcomings, you have this less than optimal design to work with.

That "recno" reference -- is it an actually field/column in the table Because I have seen a similar question to this on another site, I may have assumed too much and may have assumed you are in reality working with the RECNO() reference when that may not be the case here.

Steve-0

As far as I can tell the 'recno' is an actual column/field in the database. Again, I'm not very familiar with Foxpro database's so maybe you could help me out as to how I might determine this. When I open up the database in VFP 5.0 it shows it as a field in the list of fields for the table. The properties for it are: Type-Numeric, Width-10, Decimal-0. You are correct about the database not being very well designed (IMO). Anyway, the end result is that I am trying to create a key field (even if its not defined as a key in the database, but something that acts like one) and be able to populate it using the VFPOLEDB provider and VB. Any help is greatly appreciated!! Thanks!!