CODEWITHSUNDEEP
c#databasems-access
kartal
kartal

Reputation: 18086

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

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 ?

Upvotes: 5

Views: 8288

Answers (3)

Meisam Rasouli
Meisam Rasouli

Reputation: 321

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.

Upvotes: 0

Jon49
Jon49

Reputation: 4606

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.

Upvotes: 6

Johnny Bones
Johnny Bones

Reputation: 8404

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";

Upvotes: 1

Related Questions