Date Condition in command text

[Agnes]

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

 

[Ken Tucker [MVP]]

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
---------------------
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

 

[Herfried K. Wagner [MVP]]

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/[email protected]>

 

[Agnes]

Command text , can use parameter ?
Thanks a lot.

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

<URL:http://groups.google.de/[email protected]>

 

[Herfried K. Wagner [MVP]]

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.

 

[Agnes]

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

 

[Cor Ligthert]

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

 

[Agnes]

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)

 

[Greg Burns]

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

 

[Herfried K. Wagner [MVP]]

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.

 

[Cor Ligthert]

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

 

[Cor Ligthert]

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

 

[Cor Ligthert]

See for the "31 juli 1996" the other message I sent later that should be in
the local language.

Cor