System.Data.OleDB 'Data type mismatch in criteria expression.'

Question

I've been trying to get different columns from different tables (Access database) into one DataTable. The query / command is correct, but the adapter causes an error

System.Data.OleDb.OleDbException:'Data type mismatch in criteria expression.'

The Command is

SELECT tab_agend.ID, tab_teams.Descricao, tab_agend.idtask 
FROM tab_teams 
INNER JOIN tab_agend ON tab_teams.ID = tab_agend.idequipa;

It does work on Access but Visual Studio throws an error on the line:

adapter.Fill(dset, "table");

Full code:

string query;

if (Tabela == "tab_agend") 
{  
    query = "SELECT tab_agend.ID, tab_teams.Descricao, tab_agend.idtask " + 
            " FROM tab_teams " + 
            " INNER JOIN tab_agend ON tab_teams.ID = tab_agend.idequipa;";
} 
else 
{  
     query = "SELECT * FROM " + Tabela;
}

dset.Reset();

adapter = new OleDbDataAdapter(query, connection);
adapter.Fill(dset, "table");

Can you share some sample data from each of the datasets? What datatype is tab_agend.idequipa and tab_teams.ID? — Aron, May 31 '22 at 15:14
tab_agend.idequipa is a string, same thing with tab_teams.ID — AcisSys, May 31 '22 at 15:53
thanks. Please try explicitly casting those columns as a specific data type and re-run the query. The error message you received is not super helpful, but it's failing on a conversion and MSaccess datatypes are NOT the same as OLEDB datatypes. ` query = "SELECT tab_agend.ID, tab_teams.Descricao, tab_agend.idtask " + " FROM tab_teams " + " INNER JOIN tab_agend ON cast(tab_teams.ID as varchar(250) = cast(tab_agend.idequipa as varchar(250);";` — Aron, May 31 '22 at 17:06
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ole-db-data-type-mappings https://support.microsoft.com/en-us/office/data-types-for-access-desktop-databases-df2b83ba-cef6-436d-b679-3418f622e482 — Aron, May 31 '22 at 17:06
Sorry I misstyped, they are both Integer. It's now giving the error System.Data.OleDb.OleDbException: 'IErrorInfo.GetDescription failed with E_FAIL(0x80004005).' when I cast both as int — AcisSys, May 31 '22 at 17:33
throw some brackets around the columns and table names... just troubleshooting at the moment; once it executes successfully, we can narrow it down. https://stackoverflow.com/questions/15534284/what-is-causing-my-oledbexception-ierrorinfo-getdescription-failed-with-e-fail — Aron, May 31 '22 at 17:39
querry = "SELECT [tab_agend].ID, [tab_teams].Descricao, [tab_agend].idtask " + " FROM [tab_teams] " + " INNER JOIN [tab_agend] ON cast([tab_teams].ID as int) = cast([tab_agend].idequipa as int); "; It's still giving the same error, I'm checking again if everything is writen correctly before doing anything else again — AcisSys, May 31 '22 at 17:44
Do any of the answers on this post work/help? https://stackoverflow.com/questions/16364316/c-sharp-system-data-oledb-oledbexception — Aron, Jun 01 '22 at 11:05

score 0 · Accepted Answer · edited Jun 01 '22 at 16:40

I got the right query by adding a DataSet File on the project and doing everything "visually"

Image of DataSet1.xsd (DataSet File)

SELECT 
    tab_places.Localizacao, 
    tab_subtasks.[Desc], tab_subtasks.Type, 
    tab_tasks.*, tab_subtasks.IDTask
FROM 
    ((tab_tasks 
LEFT JOIN 
    tab_places ON tab_tasks.IDPlace = tab_places.ID) 
LEFT JOIN 
    tab_subtasks ON tab_tasks.ID = tab_subtasks.IDTask)

The code to call the adapter and fill the Datagrid is:

DataSet1TableAdapters.DataTable1TableAdapter dadapter = new DataSet1TableAdapters.DataTable1TableAdapter();

DataSet1.DataTable1DataTable dt = new DataSet1.DataTable1DataTable();

dadapter.Fill(dt);

sfDataGrid1.DataSource = dt;

System.Data.OleDB 'Data type mismatch in criteria expression.'

1 Answers1