Re: Passing Date Values - Beginner
From: Keith (kcomp1_at_hotmail.com)
Date: 10/20/04
- Next message: Joel Cade: "Re: how to get selected values ( vb.net )"
- Previous message: Merdaad: "Can not add to/modify dropdown list after datasource has been load"
- In reply to: Kevin Spencer: "Re: Passing Date Values - Beginner"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 20 Oct 2004 15:38:53 -0500
I will try your suggestion and keep that in advice in mind...thx
"Kevin Spencer" <kspencer@takempis.com> wrote in message
news:uyShQettEHA.3200@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" <kcomp1@hotmail.com> wrote in message
> news:ut7u#EttEHA.2536@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
>
>
>
>
- Next message: Joel Cade: "Re: how to get selected values ( vb.net )"
- Previous message: Merdaad: "Can not add to/modify dropdown list after datasource has been load"
- In reply to: Kevin Spencer: "Re: Passing Date Values - Beginner"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
