The provider could not determine the Object value exception with Access database

Question

I am trying to apply this query on an Access database :

OleDbConnection acessConn = database.con_accessa_m(acessdbs[i]);

string acessquery = "SELECT LTRIM(MID([Owner], 1, INSTR( [Owner],'/')-1)) AS 'lastname',IIf( INSTR( LTRIM(MID([Owner], INSTR( [Owner], '/')+1)), '/')>0,LTRIM(MID(LTRIM(MID([Owner], INSTR( [Owner], '/')+1)),1,INSTR( LTRIM(MID([Owner], INSTR( [Owner], '/')+1)), '/')-1)),LTRIM(MID([Owner], INSTR( [Owner], '/')+1))) AS 'firstname',City FROM Policies";

DataTable Policies = new DataTable();

acessConn.Open();
OleDbCommand myAccessCommand = new OleDbCommand(acessquery, acessConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
myDataAdapter.Fill(Policies);
acessConn.Close();

The connection is working perfect but I got an exception in myDataAdapter.Fill(Policies); say :

"The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value."

Note :The query is working on Microsoft access.

The connection string is:

string accconnstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + folderserverpath;` 

where folderserverpath is the database path on the server.

Why did I get this error and what is the solution ?

Shive

I would suggest trying to simplify the query to see at what point the error goes away, or at what point it begins. Queries like this with all these functions in them are scary to me. I usually take steps during design time to avoid queries like this becoming necessary. I don't know for sure but the provider you're using might process SQL differently from how it is processed inside the MS Access environment. – Metzler

I added 'where' condition and it worked. Actually I tried to simplify it but it was hard to simplify it more than that :) – Shive

So you added a WHERE that excludes some kind of data that wasn't working in your other functions? In other words "bad data"? – Metzler

@kartal: If you've fixed your own problem then please post and accept a solution here so that others with similar issues may benefit. – Dwarfism

I have faced the same problem with OLE object which is column for image. It used to work but later this error started to come and I dont know why? – Zamindar

Answer 1

I had this problem when converting from Access to SQL Server using SQL Server Migration Assistant for Access. To fix this problem I opened the offending Access database. Then I File > "Compact & Repair". I then saved and closed the Access database. I then re-imported the database and it worked fine.

Mccarter

The reference to SQL Server Migration Wizard seems at first to be a bit off-topic and yet this probably saved me at least an hour of fruitless experimenting and binging. – Reverence

This answer just saved me hours of research. Thank you kind sir. May your house be filled with fruits and your heart with love. – Gervais

Same, thank you, this saved me hours. Much appreciated. – Neglectful

Answer 2

Within your query, it is likely there is some "junk" or corrupted data, perhaps a character which your characterset can't justify. I would suggest trying to locate any bad data in your dataset, or use some type of "WHERE" clause in this line:

string acessquery = "SELECT LTRIM(MID([Owner], 1, INSTR( [Owner],'/')-1)) AS 'lastname',
IIf( INSTR( LTRIM(MID([Owner], INSTR( [Owner], '/')+1)), '/')>0,
LTRIM(MID(LTRIM(MID([Owner], INSTR( [Owner], '/')+1)),1,
INSTR( LTRIM(MID([Owner], INSTR( [Owner], '/')+1)), '/')-1)),
LTRIM(MID([Owner], INSTR( [Owner], '/')+1))) AS 'firstname', 
City FROM Policies";

Rabi

I detected the cells in the table that were causing the problem. MS Access just displayed "#Error". When clicking on it or trying to run an update on the respective records I got a message "... you and another user are attempting to change the same data at the same time". So I ended up running a SELECT INTO FROM WHERE NOT & a INSERT INTO FROM WHERE to save all the rest of the table, giving up the corrupt cells. – Chanda

Answer 3

I had the same problem with an Access database. I was trying to excecute the following query:

SELECT * FROM [Suppliers] WHERE ([CompanyID] = @Pm01)

But I was encountering the same error. I tried to figure out where the problem lied by only retrieving certain data columns instead of retrieving all the data columns from the data table. In order to do so, instead of using *, I used specific data column names, such as [CompanyName], [CompanyID]. I converted the above query into the following:

SELECT [CompanyName], [CompanyID] FROM [Suppliers] WHERE ([CompanyID] = @Pm01)

I didn't see the error any more, and the problem was solved. Something must be wrong with the Microsoft Access Extension Library, or the Access database. The data in one of the data columns may not be as it is expected to be. I don't know why such error may occur.

Camion