Question Data type mismatch in criteria expression Error

[stanbuggy]

Hi Guys,
Working on an application using MS Access s my DB. when i try to query my database using the datetime picker i get this error "Data type mismatch in criteria expression."

Below is my code

       cmd = New OleDbCommand("Select * from members where date_reg between '" + Format(dtpfrom.Value, "yyyy/MM/dd") + "' and '" + Format(dtpto.Value, "yyyy/MM/dd") + "'", cn)

Pls what could be wrong with my code and why the error.


Thanks

 

[jmcilhinney]

Don't ever use string concatenation to insert variables into SQL code. There is absolutely no reason to worry about date format because your dates should be kept in binary form at all times. Follow the Blog link in my signature and check out my post on ADO.NET Parameters to find out how to do it properly.

Also, note that the Value property of a DateTimePicker is type DateTime. If you want just the date portion then you can get the Date property of that value, which will return another DateTime with the same date and the time portion zeroed. As an example, run this code:

Dim n As Date = Date.Now

MessageBox.Show(n.ToString())

Dim t As Date = n.Date

MessageBox.Show(t.ToString())

 

[stanbuggy]

i said i was using MS Access. and am sorry i really did not get ur explanation above....

Help Pls !!!!!!!!!!!!!!

 

[stanbuggy]

hi jmcilhinney,
Here is a snap shot of what i mean

Like i said earlier am using MS Access not SQL

so what code would i use in getting my results.

Thanks

 

[JohnH]

SQL means "Structured Query Language", your SELECT statement is a SQL query that you execute through the OleDbCommand against the Access database.

 

[jmcilhinney]

As JohnH says, SQL is a language, not a database. What far too many people lazily call "SQL" is actually called "SQL Server". It is Microsoft's enterprise-grade database system. There are many, many databases out there, including SQL Server, MySQL, SQLite, Oracle, Access and others. Pretty much every database on the market these days uses the SQL language to define and manipulate data, Access included. The String that you are passing to your OleDbCommand contains SQL code, which gets executed against the database to retrieve data.

I repeat, follow the Blog link in my signature and check out my post on ADO.NET parameters. It will show you the proper way to insert variables into SQL code. It even addresses the subject of DateTimePicker controls and Access directly. You already have all the information you need to solve your problem. You now just have to read it and put the principles it describes into practice.

 

[stanbuggy]

ok guys, i really know the diffrent btwn a database and a query language...i just misunderstood SQL with SQL Server...So Pls can anyone help me with my code. And no comment on the snap shot i posted...wow

 

[jmcilhinney]

Someone has already tried to help you with your code but you are apparently unwilling to make an effort on your own behalf. I've already told you twice where you can find information on how to use parameters with ADO.NET and why you should do it. Have you read that information? If you have read the information and you don't understand something about it or you have tried to use it and it didn't work, by all means explain that and we can help you. If you're just waiting for someone to write your code for you though, I'm afraid that you may be waiting a while. I spent some time writing that blog post so that people like you could solve problems like this and learn something useful that would help them avoid other problems in the future, and also so that I, and others, wouldn;t have to keep answering basically the same question over and over. If you want to solve your problem then I suggest, for the third time, that you read it.

 

[stanbuggy]

hi Jm, i have read your blog from page to page from your 2009 archive to 2010...but this is very urgent for me ..i know i'll need time to practice all that was stated in your well written blog...but if i you think requesting for a line or two of code is been lazy, then no problem i'll take my problem somewhere else.

Thanks for your help Thus far.