OLEDB and Foxpro

[tbubbs]

Our team is having problems updating a FoxPro database through OLEDB.
Getting and displaying data has not been an issue.
We have a data adapter that has insert, update, delete and select statements.
The parameter collection satisfies the requirements for our statements.
All the logic sits in a DLL (including data access).
Calls are made from our aspx.vb page.
Data appears to be transferred fine to the DLL (due to where it crashes and as proved through debugging).
The error we continue to get is : "Object reference not set to an instance of an object".
If we add records the crash will occur on the added line.
If we modify records the crash will occur on the modify line. Same with delete.
The line looks like: oDA.Update(oTable.select(nothing,nothing,Dataviewrowstate.modifiedcurrent)).
If we take everything and translate it to SQL (which we have used for demo purposes) all is well.

Please help.

TBubbs

 

[link9]

Your call to the .update() method looks like it's the culprit, because it expects a dataset as an argument...

so what is oTable.select(...) ? Does this function call return a dataset?

It would help to see a bit more code... as the update call requires some things to be set before it will function properly.

 

[tbubbs]

Well, passing in a dataset [oDA.Update(oDS)] as an argument produces this for an error:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.

If we change the name of the table that is being used to 'Table' (then what happens when we have many datatables in the dataset?) then the original error comes up again:

An unhandled exception of type 'System.NullReferenceException' occurred in system.data.dll

Additional information: Object reference not set to an instance of an object.

If we trim down the selection in the tables, say by using oDS.Tables(oTable.TableName) [oDA.Update(oDS.Tables(oTable.TableName))] rather than just oDS in the call to the dataadapter, then still the same error comes up (object reference error).

The dataadapter - Dim oDA As New OleDb.OleDbDataAdapter(strSelectSQLSchool, oConn)
The table - Dim oTable As DataTable = oDS.Tables(strDTSchool) 'strDTSchool = "School"
Some commands - oDA.InsertCommand = insertSQL
oDA.UpdateCommand = updateSQL
oDA.DeleteCommand = deleteSQL
And then parameters are added. There is no primary key in FoxPro, and we've tried enforcing one (this gives a "column has null values for a primary key" type error), and we've also left out the primary key declarations from SQL, and that has worked completely fine.
The call to the oSLDA DLL (oSLDA is dimensioned beforehand) - oDS = oSLDA.SetData(intUserID, oDA, oDS, oTable)

I'm not sure what you would like to see in terms of more code, so if you (or someone else) could clarify that a little, it'd be appreciated...

As always, any assistance would be greatly appreciated!

Thank you!
TBubbs

 

[link9]

The thing is that the update method definitely expects a dataset as its argument. That's why you're getting an exception thrown when you try to pass in the array of datarow objects (which is what's returned on your .select), or a reference to a table object

It's wanting an entire dataset. The update is going to iterate through all of your table objects in your dataset and figure out what needs to be updated and what doesn't.

When it finds a row that needs an update (UPDATE, DELETE, INSERT, whatever), then it will execute the appropriate command that you have supplied.


How is the call to your setData setup? The dataAdapter, specifically, might need to be passed in byRef, so that it comes back out with state (i.e. knowing that it filled oDS when it was under the control of your assembly). Remember that by default, things are passed byVal now, and when objects are passed in like that, they are copied, and have no knowledge of what happened "on the other side" of that call.

So in summary, I would change the call to byRef on your dataAdapter to your component, and also be sure to pass in the entire dataset to the .update method call of the dataAdapter.

I might also make sure (although I think you probably have) that all the properties of the dataAdapter are set prior to the call to .setData.

lemme know-
paul

 

[tbubbs]

Well, out project manager just came by. It turns out that FoxPro is not very compatible with .NET. What has to be done is something along the lines of a FoxPro DLL has to be created that takes in an XML string (likely in the form of an UpdateGram) that will be used to update the database, and that a DataAdapter cannot access a FoxPro database directly. Thus, we're moving forward the plans to upgrade to a SQL Sever back-end, and after about 5 minutes of converting (only a conversion, no logic/procedural modifications), it is updating to the database. All we did was change the queries to use named parameters (rather than question marks), change the parameters to use SQL types (as opposed to OLEDB data types), and change the objects to SQL ones (rather than OLE ones). And now it all seems to work.

Oh, and by the way, if you were curious, we did pass in the entire dataset, and we had a different error, that it was looking for a table called 'Table', and if we made a table called 'Table' and restricted the dataset to one datatable, we had the original error come back up again. One thing we did notice this morning, however (which should really make people scratch their heads!), was that in a multi-table dataset, it always errord out on the first table, even if that table had not been changed. We also tried passing in all combinations of ByRef and ByVal for those three objects (7 in total), and we still get the same error. We tried that last night, right before we left for the evening (thus before we found out about needing the FoxPro DLL - which we're still not confident on, but nothing else seems to make any sense...). Maybe there was a property of the DataAdapter that wasn't set - but then why would it work for SQL? (We never added even a single line or modified one to something different, other than what the conversion called for - that is, oledb.oledbdataadapter to sqlclient.sqldataadapter and such. Also, note that neither method used a command builder, all of the commands have been typed out long-hand. We tried the Command Builder, but to no avail.)

Thank you for all your time,
TBubbs.

 

[link9]

You'll get better performance out of SQL Server, anyway. What a pain, though, eh?

So I'm curious... when you did get it working with SQL Server, you did wind up passing in the entire dataset, yes?

 

[tbubbs]

Actually, let me check, because I'm fairly certain that we did not, that it is using the original method of .select...

As I thought,

  oDA.Update(oTable.Select(Nothing, Nothing, DataViewRowState.Deleted))
  oDA.Update(oTable.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
  oDA.Update(oTable.Select(Nothing, Nothing, DataViewRowState.Added))

But you're making me think. I'm going to check on passing in the entire dataset and see what happens. [oDA.Update(oDS)]
Well, this is what happens:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.

Well, that's just great. (That's the same problem in OLEDB... moderately interesting....)
But what about oDA.Update(oDS.Tables(oTable.TableName))?
And the result (aren't you glad I'm not keeping you in suspense?? ) is...
No problem! The update works perfectly. The only reason why this method would work is that we are only trying to update one table at a time - after all, as far as we're aware, you can only update one table at a time. There's only one Update command per dataadapter... so then this raises a question: if you're supposed to be able to pass in an entire multi-table dataset (or can you?), the how does the dataadapter handle that?? Especially in scenarios where the command builder fails (on setup of the dataadapter, it sometimes says that all the commands cannot be made, usually this is the case because we're using a more complex statement with JOINs and displaying information from many tables, or the table in question does not have a primary key [a cross-link table, say between something simle like suppliers and customers, where each customer can request many items from each supplier, and each supplier can <I fell like typing in blah blah blah here...> supply things to many customers])??

Hope that out problem(s) help other people too!
TBubbs.

Post Script:
If you (or anyone) has any other questions, we'll be sure to try to help!