By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Passing Date Values - Beginner

P: n/a
Good Afternoon,

New to .Net. I am trying to pass date/time values to a MS Access query depending on what value is selected from a dropdown list box (January, February, etc). I have declared those values as date datatypes and assigned their values with the # signs in front and back. When I click the submit I get the following error message:

Data type mismatch in criteria expression
cmdSelect1 = New OleDbCommand("Select Count(*) from [db] where [Submit Date/Time]>='CurrentStrMth' and [Submit Date/time]<='CurrentEndMth'", dbconn)

Note....I am trying to put this value in a label:
lblSumCurrent.text = cmdSelect1.ExecuteScalar()

Could someone tell me what I am missing. Searched the web and found nothing that has worked.

Thanks!!!
MKC

Nov 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Offhand, I'd say that 'CurrentStrMth" and "CurrentEndMth" are not dates.

SQL is a text-based language. Everything in a SQL Statement is text. Your
error message indicates that the 2 literals that are being passed in your
query can not be date values. And of course they are not.

I'm not sure how you constructed your query (you didn't say), but a SQL
Statement is often executed by concatenating strings together, and it looks
like you might have tried that approach. However, a variable name is not a
date.

A variable is a container for something, such as a date or a string, but it
isn't what it contains, any more than a hatbox is a hat. Now, when you
reference a variable by name in your code (e.g. Dim s As String) you will
note that you don't put quotes around it. That's because quotes indicate a
string literal, not a variable.

Now, when you're building a string to create your SQL Statement, you are
creating a string (note the quotes around it). If you simply concatenate the
variable name into the query, you end up with a query such as you've posted,
and it doesn't make sense because the string 'CurrentStrMth" is not a date.
The VARIABLE CurrentStrMth may CONTAIN a date, or even a string that
represents a date, but if you treat the variable name like a sting, that is
what it is.

Again, a SQL Statement is a string of text. SQL uses punctuation (just like
we do!) to identify certain types of things, particularly data types. In a
SQL Statement, the single quote is the delimiter for both string values and
date values. So, you do need the punctuation. but the other thing you need
is the value inside the single quotes.

Now I will show you 2 examples, the first of which is wrong and will end up
much like yours, but the second of which is correct, and should hopefully
illustrate what I'm trying to communicate:

<wrong>
Dim sql As String = ("Select Count(*) from [db] where [Submit
Date/Time]>='CurrentStrMth' and [Submit Date/time]<='CurrentEndMth'"
</wrong>

Note that the single quotes are inside the double quotes, thereby making
them part of the string.

<right>
Dim sql As String = ("Select Count(*) " & _
from [db] where [Submit Date/Time]>='" & _
CurrentStrMth.ToString() & "' and [Submit Date/time]<='" & _
CurrentEndMth.ToString() & "'"
</right>

This concatenates several string fragments together with the values of the
variables. The ToString() method is used to ensure that the variables are
converted to strings to concatenate. If you turn Option Strict ON, you'll
not only see why, but your app will run faster and be less prone to errors.

Of course, you still have to make sure that the values of your variables
translate into the correct string format of a date that can be recognized by
the database.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Keith" <kc****@hotmail.com> wrote in message
news:ut**************@TK2MSFTNGP11.phx.gbl...
Good Afternoon,

New to .Net. I am trying to pass date/time values to a MS Access query
depending on what value is selected from a dropdown list box (January,
February, etc). I have declared those values as date datatypes and assigned
their values with the # signs in front and back. When I click the submit I
get the following error message:

Data type mismatch in criteria expression
cmdSelect1 = New OleDbCommand("Select Count(*) from [db] where [Submit
Date/Time]>='CurrentStrMth' and [Submit Date/time]<='CurrentEndMth'",
dbconn)

Note....I am trying to put this value in a label:
lblSumCurrent.text = cmdSelect1.ExecuteScalar()

Could someone tell me what I am missing. Searched the web and found nothing
that has worked.

Thanks!!!
MKC


Nov 18 '05 #2

P: n/a
I will try your suggestion and keep that in advice in mind...thx
"Kevin Spencer" <ks******@takempis.com> wrote in message
news:uy**************@TK2MSFTNGP14.phx.gbl...
Offhand, I'd say that 'CurrentStrMth" and "CurrentEndMth" are not dates.

SQL is a text-based language. Everything in a SQL Statement is text. Your
error message indicates that the 2 literals that are being passed in your
query can not be date values. And of course they are not.

I'm not sure how you constructed your query (you didn't say), but a SQL
Statement is often executed by concatenating strings together, and it looks like you might have tried that approach. However, a variable name is not a
date.

A variable is a container for something, such as a date or a string, but it isn't what it contains, any more than a hatbox is a hat. Now, when you
reference a variable by name in your code (e.g. Dim s As String) you will
note that you don't put quotes around it. That's because quotes indicate a
string literal, not a variable.

Now, when you're building a string to create your SQL Statement, you are
creating a string (note the quotes around it). If you simply concatenate the variable name into the query, you end up with a query such as you've posted, and it doesn't make sense because the string 'CurrentStrMth" is not a date. The VARIABLE CurrentStrMth may CONTAIN a date, or even a string that
represents a date, but if you treat the variable name like a sting, that is what it is.

Again, a SQL Statement is a string of text. SQL uses punctuation (just like we do!) to identify certain types of things, particularly data types. In a
SQL Statement, the single quote is the delimiter for both string values and date values. So, you do need the punctuation. but the other thing you need
is the value inside the single quotes.

Now I will show you 2 examples, the first of which is wrong and will end up much like yours, but the second of which is correct, and should hopefully
illustrate what I'm trying to communicate:

<wrong>
Dim sql As String = ("Select Count(*) from [db] where [Submit
Date/Time]>='CurrentStrMth' and [Submit Date/time]<='CurrentEndMth'"
</wrong>

Note that the single quotes are inside the double quotes, thereby making
them part of the string.

<right>
Dim sql As String = ("Select Count(*) " & _
from [db] where [Submit Date/Time]>='" & _
CurrentStrMth.ToString() & "' and [Submit Date/time]<='" & _
CurrentEndMth.ToString() & "'"
</right>

This concatenates several string fragments together with the values of the
variables. The ToString() method is used to ensure that the variables are
converted to strings to concatenate. If you turn Option Strict ON, you'll
not only see why, but your app will run faster and be less prone to errors.
Of course, you still have to make sure that the values of your variables
translate into the correct string format of a date that can be recognized by the database.

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Keith" <kc****@hotmail.com> wrote in message
news:ut**************@TK2MSFTNGP11.phx.gbl...
Good Afternoon,

New to .Net. I am trying to pass date/time values to a MS Access query
depending on what value is selected from a dropdown list box (January,
February, etc). I have declared those values as date datatypes and assigned their values with the # signs in front and back. When I click the submit I get the following error message:

Data type mismatch in criteria expression
cmdSelect1 = New OleDbCommand("Select Count(*) from [db] where [Submit
Date/Time]>='CurrentStrMth' and [Submit Date/time]<='CurrentEndMth'",
dbconn)

Note....I am trying to put this value in a label:
lblSumCurrent.text = cmdSelect1.ExecuteScalar()

Could someone tell me what I am missing. Searched the web and found nothing that has worked.

Thanks!!!
MKC

Nov 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.