Differences between OleDb and Sql in ADO.Net
I usually develop applications that use SQL server so i never really had a chance to work with Access DB before. So yesterday i had to write some data to an access DB and i thought how different can it be. So i set up my queries and parameters as usual, althought it felt a little strange having to write SQL code in VS.Net :) , and i run the app and obviously it bombs. That was expected but the error was a little strange what is got was "Data type mismatch in criteria." hmm...so i look up my OleDbTypes that i have setup for my params seemed OK to me.
The best way to debug such problems is to see how VS.Net generated code is doing it, so i created a new solution and dragged the table from Server Explorer and Created a dataset and datagrid and had a up and running dataform in under 2 minutes (That's what i love about VS.Net, try doing that in eclipse). I looked at the code and i realized how mistaken i was about the similarities between Sql and Access.
Ok first of all the parameters, they don't have names, they're just '?' so instead of saying
select * from table where field1 = @var1 and field2 = @var2 you would write the query as
select * from table where field1 = ? and field2= ?
So you have to add params to the command depending on the order they appear in the query.
Ok so i fixed that problem but i was still getting the Data type mismatch error..well in my case it turned out that it was something quite simple, those pesky DateTime's again. I was using OleDbType.DBTimeStamp for my field whereas i should have been using OleDbType.Date. Also from the generated code i realized that Text field maps to OleDbType.VarWChar and Memo fields map to OleDbType.VarWChar with size zero rather than OleDbType.LongVarChar as i initially thought. Came across a good link that maps these DataTypes.