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

Date Condition in command text

P: n/a
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot
Nov 21 '05 #1
Share this Question
Share on Google+
24 Replies


P: n/a
Hi,

Two things. First enclose the date in # and second i believe the
date has to be in mm/dd/yyyy format.

Dim strDate As String = Me.txtStartDate.text

Dim dt As Date = Date.ParseExact(strDate, "yyyy-mm-dd", New
Globalization.CultureInfo("en-US"))

select * from myTable where issuedate >= #" &
dt.toshortdatestring & "# and issuedate <= #" & dt.toshortdatestring& "# "
Ken
---------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:u7**************@TK2MSFTNGP12.phx.gbl...
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot

Nov 21 '05 #2

P: n/a
Hi,

Two things. First enclose the date in # and second i believe the
date has to be in mm/dd/yyyy format.

Dim strDate As String = Me.txtStartDate.text

Dim dt As Date = Date.ParseExact(strDate, "yyyy-mm-dd", New
Globalization.CultureInfo("en-US"))

select * from myTable where issuedate >= #" &
dt.toshortdatestring & "# and issuedate <= #" & dt.toshortdatestring& "# "
Ken
---------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:u7**************@TK2MSFTNGP12.phx.gbl...
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot

Nov 21 '05 #3

P: n/a
"Agnes" <ag***@dynamictech.com.hk> schrieb:
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "


Use parameters instead of putting together the SQL command string by hand to
prevent SQL injection:

<URL:http://groups.google.de/groups?selm=eb11vcS0DHA.2604%40TK2MSFTNGP09.phx.gb l>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #4

P: n/a
"Agnes" <ag***@dynamictech.com.hk> schrieb:
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "


Use parameters instead of putting together the SQL command string by hand to
prevent SQL injection:

<URL:http://groups.google.de/groups?selm=eb11vcS0DHA.2604%40TK2MSFTNGP09.phx.gb l>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #5

P: n/a
Command text , can use parameter ?
Thanks a lot.
"Herfried K. Wagner [MVP]" <hi***************@gmx.at> 级糶秎ン穝籇:OO**************@TK2MSFTNGP14.phx.g bl...
"Agnes" <ag***@dynamictech.com.hk> schrieb:
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "


Use parameters instead of putting together the SQL command string by hand
to prevent SQL injection:

<URL:http://groups.google.de/groups?selm=eb11vcS0DHA.2604%40TK2MSFTNGP09.phx.gb l>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Nov 21 '05 #6

P: n/a
Command text , can use parameter ?
Thanks a lot.
"Herfried K. Wagner [MVP]" <hi***************@gmx.at> 级糶秎ン穝籇:OO**************@TK2MSFTNGP14.phx.g bl...
"Agnes" <ag***@dynamictech.com.hk> schrieb:
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "


Use parameters instead of putting together the SQL command string by hand
to prevent SQL injection:

<URL:http://groups.google.de/groups?selm=eb11vcS0DHA.2604%40TK2MSFTNGP09.phx.gb l>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Nov 21 '05 #7

P: n/a
"Agnes" <ag***@dynamictech.com.hk> schrieb:
Command text , can use parameter ?


Yes, you can/"should" use parameters in your 'CommandText'. Take a look at
the documentation of 'SqlCommand.CommandText' for details in how to use the
parameters.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #8

P: n/a
"Agnes" <ag***@dynamictech.com.hk> schrieb:
Command text , can use parameter ?


Yes, you can/"should" use parameters in your 'CommandText'. Take a look at
the documentation of 'SqlCommand.CommandText' for details in how to use the
parameters.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #9

P: n/a
Agnes,

Some questions before this problem in my opinion can be answered.

Are you using in your database "DateTime" fields or other fields.
Are you using OleDb or SQLClient (the only difference in using that are the
parameters)
In what culture (or country) are you living that your date time format is
yyyy-mm-dd I never heard of that one, so as it this exist than I am real
curious about that.

Maybe (most probably) I can answer your problem after answers on this

:-)

Cor
Nov 21 '05 #10

P: n/a
Agnes,

Some questions before this problem in my opinion can be answered.

Are you using in your database "DateTime" fields or other fields.
Are you using OleDb or SQLClient (the only difference in using that are the
parameters)
In what culture (or country) are you living that your date time format is
yyyy-mm-dd I never heard of that one, so as it this exist than I am real
curious about that.

Maybe (most probably) I can answer your problem after answers on this

:-)

Cor
Nov 21 '05 #11

P: n/a
I try your code, It works BUT there is sth strange
e.g me.txtstartdate and txtenddate is the same e.g 2004-10-06 I sure there
is 10records in this date period
However, it returns one 1record for me
If my startdate is 2004-10-06 with end date 2004-10-07, it will return the
10 records. BUT it didn't include the records which belongs 2004-10-7
"Ken Tucker [MVP]" <vb***@bellsouth.net> 级糶秎ン穝籇:uU**************@TK2MSFTNGP15.phx.g bl...
Hi,

Two things. First enclose the date in # and second i believe the
date has to be in mm/dd/yyyy format.

Dim strDate As String = Me.txtStartDate.text

Dim dt As Date = Date.ParseExact(strDate, "yyyy-mm-dd", New
Globalization.CultureInfo("en-US"))

select * from myTable where issuedate >= #" &
dt.toshortdatestring & "# and issuedate <= #" & dt.toshortdatestring& "# "
Ken
---------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:u7**************@TK2MSFTNGP12.phx.gbl...
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot

Nov 21 '05 #12

P: n/a
I try your code, It works BUT there is sth strange
e.g me.txtstartdate and txtenddate is the same e.g 2004-10-06 I sure there
is 10records in this date period
However, it returns one 1record for me
If my startdate is 2004-10-06 with end date 2004-10-07, it will return the
10 records. BUT it didn't include the records which belongs 2004-10-7
"Ken Tucker [MVP]" <vb***@bellsouth.net> 级糶秎ン穝籇:uU**************@TK2MSFTNGP15.phx.g bl...
Hi,

Two things. First enclose the date in # and second i believe the
date has to be in mm/dd/yyyy format.

Dim strDate As String = Me.txtStartDate.text

Dim dt As Date = Date.ParseExact(strDate, "yyyy-mm-dd", New
Globalization.CultureInfo("en-US"))

select * from myTable where issuedate >= #" &
dt.toshortdatestring & "# and issuedate <= #" & dt.toshortdatestring& "# "
Ken
---------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:u7**************@TK2MSFTNGP12.phx.gbl...
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot

Nov 21 '05 #13

P: n/a
Well, In Hong Kong, most people use dd-mm-yyyy
However, I try that .it is really difficult to set date or check the date in
the format dd-mm-yyyy
So, I try to set the date in yyyy-mm-dd, so It is easier for the user to
input the date.
THEY are reject to set the date format in mm-dd-yyyy.
that's why I am so confused to set the date.
In our VFP applicaiton , I can easier to set the date , but the .net (I am
so upset)
"Cor Ligthert" <no************@planet.nl> 级糶秎ン穝籇:%2****************@TK2MSFTNGP11.phx .gbl...
Agnes,

Some questions before this problem in my opinion can be answered.

Are you using in your database "DateTime" fields or other fields.
Are you using OleDb or SQLClient (the only difference in using that are
the parameters)
In what culture (or country) are you living that your date time format is
yyyy-mm-dd I never heard of that one, so as it this exist than I am real
curious about that.

Maybe (most probably) I can answer your problem after answers on this

:-)

Cor

Nov 21 '05 #14

P: n/a
Well, In Hong Kong, most people use dd-mm-yyyy
However, I try that .it is really difficult to set date or check the date in
the format dd-mm-yyyy
So, I try to set the date in yyyy-mm-dd, so It is easier for the user to
input the date.
THEY are reject to set the date format in mm-dd-yyyy.
that's why I am so confused to set the date.
In our VFP applicaiton , I can easier to set the date , but the .net (I am
so upset)
"Cor Ligthert" <no************@planet.nl> 级糶秎ン穝籇:%2****************@TK2MSFTNGP11.phx .gbl...
Agnes,

Some questions before this problem in my opinion can be answered.

Are you using in your database "DateTime" fields or other fields.
Are you using OleDb or SQLClient (the only difference in using that are
the parameters)
In what culture (or country) are you living that your date time format is
yyyy-mm-dd I never heard of that one, so as it this exist than I am real
curious about that.

Maybe (most probably) I can answer your problem after answers on this

:-)

Cor

Nov 21 '05 #15

P: n/a
Not sure about VFP, but in SQL Server a datetime column includes both date
and TIME. If you don't specify the time it defaults to midnight(!) If you
want your query to be inclusive you can do either

WHERE issuedate BETEEN '2004/10/06' AND '2004/10/07 23:59:59.999'

or

WHERE issuedate > '2004/10/06' AND issuedate < '2004/10/08'

Greg

"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:em**************@TK2MSFTNGP14.phx.gbl...
I try your code, It works BUT there is sth strange
e.g me.txtstartdate and txtenddate is the same e.g 2004-10-06 I sure there
is 10records in this date period
However, it returns one 1record for me
If my startdate is 2004-10-06 with end date 2004-10-07, it will return the
10 records. BUT it didn't include the records which belongs 2004-10-7
"Ken Tucker [MVP]" <vb***@bellsouth.net>
级糶秎ン穝籇:uU**************@TK2MSFTNGP15.phx.g bl...
Hi,

Two things. First enclose the date in # and second i believe the
date has to be in mm/dd/yyyy format.

Dim strDate As String = Me.txtStartDate.text

Dim dt As Date = Date.ParseExact(strDate, "yyyy-mm-dd", New
Globalization.CultureInfo("en-US"))

select * from myTable where issuedate >= #" &
dt.toshortdatestring & "# and issuedate <= #" & dt.toshortdatestring& "#
"
Ken
---------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:u7**************@TK2MSFTNGP12.phx.gbl...
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot


Nov 21 '05 #16

P: n/a
Not sure about VFP, but in SQL Server a datetime column includes both date
and TIME. If you don't specify the time it defaults to midnight(!) If you
want your query to be inclusive you can do either

WHERE issuedate BETEEN '2004/10/06' AND '2004/10/07 23:59:59.999'

or

WHERE issuedate > '2004/10/06' AND issuedate < '2004/10/08'

Greg

"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:em**************@TK2MSFTNGP14.phx.gbl...
I try your code, It works BUT there is sth strange
e.g me.txtstartdate and txtenddate is the same e.g 2004-10-06 I sure there
is 10records in this date period
However, it returns one 1record for me
If my startdate is 2004-10-06 with end date 2004-10-07, it will return the
10 records. BUT it didn't include the records which belongs 2004-10-7
"Ken Tucker [MVP]" <vb***@bellsouth.net>
级糶秎ン穝籇:uU**************@TK2MSFTNGP15.phx.g bl...
Hi,

Two things. First enclose the date in # and second i believe the
date has to be in mm/dd/yyyy format.

Dim strDate As String = Me.txtStartDate.text

Dim dt As Date = Date.ParseExact(strDate, "yyyy-mm-dd", New
Globalization.CultureInfo("en-US"))

select * from myTable where issuedate >= #" &
dt.toshortdatestring & "# and issuedate <= #" & dt.toshortdatestring& "#
"
Ken
---------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:u7**************@TK2MSFTNGP12.phx.gbl...
my date format is in yyyy-mm-dd
mycommandtext is 'select * from myTable where issuedate >='" &
Me.txtStartDate.text & "' and issuedate <= '" & Me.txtEndDate.text & "' "

It return zero records, Is that the commandtext got errors ??
Thanks a lot


Nov 21 '05 #17

P: n/a
"Agnes" <ag***@dynamictech.com.hk> schrieb:
Well, In Hong Kong, most people use dd-mm-yyyy
However, I try that .it is really difficult to set date or check the date
in the format dd-mm-yyyy
So, I try to set the date in yyyy-mm-dd, so It is easier for the user to
input the date.
THEY are reject to set the date format in mm-dd-yyyy.
that's why I am so confused to set the date.


The recommended way is to use 'Date.Parse'/'Date.ParseExact' to get a
'DateTime', and then use a parameterized command object that takes the
'DateTime' in one parameter. By doing it this way, you don't need to worry
about the date format you use to build the command string, because it will
be done automatically, and you reduce/remove the risk of SQL injection.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #18

P: n/a
"Agnes" <ag***@dynamictech.com.hk> schrieb:
Well, In Hong Kong, most people use dd-mm-yyyy
However, I try that .it is really difficult to set date or check the date
in the format dd-mm-yyyy
So, I try to set the date in yyyy-mm-dd, so It is easier for the user to
input the date.
THEY are reject to set the date format in mm-dd-yyyy.
that's why I am so confused to set the date.


The recommended way is to use 'Date.Parse'/'Date.ParseExact' to get a
'DateTime', and then use a parameterized command object that takes the
'DateTime' in one parameter. By doing it this way, you don't need to worry
about the date format you use to build the command string, because it will
be done automatically, and you reduce/remove the risk of SQL injection.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #19

P: n/a
Agnes,

I have made a sample using a NorthWind mdb samples
You need for the sample only a datagrid and two textboxes on the form and
than just to run.
\\\
TextBox1.Text = "20-07-1996"
TextBox2.Text = "31 juli 1996"
'Used is Dutch settings
Dim conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test1\northwind.mdb")
Dim da As New OleDb.OleDbDataAdapter _
("SELECT * FROM Orders WHERE (OrderDate > ?) AND (OrderDate < ?)", conn)
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters(0).Value = CDate(TextBox1.Text)
da.SelectCommand.Parameters(1).Value = CDate(TextBox2.Text)
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
///
I hope this helps a little bit?

Cor
Nov 21 '05 #20

P: n/a
Agnes,

I have made a sample using a NorthWind mdb samples
You need for the sample only a datagrid and two textboxes on the form and
than just to run.
\\\
TextBox1.Text = "20-07-1996"
TextBox2.Text = "31 juli 1996"
'Used is Dutch settings
Dim conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test1\northwind.mdb")
Dim da As New OleDb.OleDbDataAdapter _
("SELECT * FROM Orders WHERE (OrderDate > ?) AND (OrderDate < ?)", conn)
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters(0).Value = CDate(TextBox1.Text)
da.SelectCommand.Parameters(1).Value = CDate(TextBox2.Text)
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
///
I hope this helps a little bit?

Cor
Nov 21 '05 #21

P: n/a
Agnes,

I saw I connected my sample to the wrong message so again.

It needs only a form with a datagrid, and two textboxes (the do nothing) and
the nortwind sample mdb (access) database, change "31 juli 1996" in your
own written language date and than run.

\\\
TextBox1.Text = "20-07-1996"
TextBox2.Text = "31 juli 1996"
'Used is Dutch settings should be the same as Honkong as you wrote
Dim conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test1\northwind.mdb")
Dim cm As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter _
("SELECT * FROM Orders WHERE (OrderDate > ?) AND (OrderDate < ?)", conn)
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters(0).Value = CDate(TextBox1.Text)
da.SelectCommand.Parameters(1).Value = CDate(TextBox2.Text)
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
///

I hope this helps a little bit?

Cor
Nov 21 '05 #22

P: n/a
Agnes,

I saw I connected my sample to the wrong message so again.

It needs only a form with a datagrid, and two textboxes (the do nothing) and
the nortwind sample mdb (access) database, change "31 juli 1996" in your
own written language date and than run.

\\\
TextBox1.Text = "20-07-1996"
TextBox2.Text = "31 juli 1996"
'Used is Dutch settings should be the same as Honkong as you wrote
Dim conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test1\northwind.mdb")
Dim cm As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter _
("SELECT * FROM Orders WHERE (OrderDate > ?) AND (OrderDate < ?)", conn)
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.Date))
da.SelectCommand.Parameters(0).Value = CDate(TextBox1.Text)
da.SelectCommand.Parameters(1).Value = CDate(TextBox2.Text)
Dim ds As New DataSet
da.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
///

I hope this helps a little bit?

Cor
Nov 21 '05 #23

P: n/a
See for the "31 juli 1996" the other message I sent later that should be in
the local language.

Cor
Nov 21 '05 #24

P: n/a
See for the "31 juli 1996" the other message I sent later that should be in
the local language.

Cor
Nov 21 '05 #25

This discussion thread is closed

Replies have been disabled for this discussion.