Date Querry

Hello,

I am working with an Access Database and am trying to return a Querry that
is between two specified dates, I have tried the following and none of them
work:

1)

ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE
'Date' > " & dtpFrom.Text & " and 'Date' < dtpTo.Text & ";",
AConnectionString)

2)

ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE
[Date] BETWEEN DATEPART ('" & dtpFrom.Text & "') AND DATEPART ('" &
dtpTo.Text & "')" , AConnectionString)

3)

ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE
'Date' > 1/1/2004 and 'Date' < 6/1/2004;", AConnectionString)

No records are returned. Any suggestions will be greatly appreciated.

Thanks,

Chuck

William
Sun Apr 25 19:17:39 CDT 2004

Don't use concatenated dynamic sql. The reason I say this is that it causes
problems just like this, in addition to problmes if you use a field that has
a value like O'Rourke, O'Toole etc. Plus it opens you up for injection
attacks and it performs worse. Even if you are positive no one will ever
try an injection attack, you'll forget to escape a field one day (or some
other programmer will) and the problem probably won't show itself for a
while -- and it's almost always a customer that finds it.

Dynamic SQL with Concatenated fields is something that's outlived its
usefulness:

Instead:

ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE
'Date' > ? And 'Date < ?", AConnectionString)
mySelectCommand.Parameters.Add(CType(dtpTo.Text, DateTime))
mySelectCommand.Parameters.Add(CType(dtpFrom.Text, DateTime))

This query won't actually work though because you are using a reserved word
for a field name. You'll need to wrap Date in [] like this Where [Date] > ?
... http://www.knowdotnet.com/articles/reservedwords.html

However, this is still a challenged approach b/c you'll always have to
remember to do this and you'll invariably forget, or another programmer
will. So I'd change the name to xxxxDate where xxx is some descriptive
name. Using reserved words for field names is very risky in general, but
ADO.NET and Reserved names (particularly with Access) don't play well
together.

HTH,

Bill

Here's another article on using Parameters. In access you use the ? mark
instead of the @ParamName (Maybe you can use Named params like that but I'm
not sure, but ? will absolutely work. Then just add the values, in the
order they appear with the ? to the command's parameters collection. You
may want to walk through the DataAdapter configuration wizard and let it
generate the Statements for you just so you can see how it declares and uses
the params. I'm not saying don't do it this way, but the wizard will
generate code and you can use that code for a learning tool)
http://www.knowdotnet.com/articles/storedprocsvb.html

"Charles A. Lackman" <Charles@cet.com> wrote in message
news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I am working with an Access Database and am trying to return a Querry
that
> is between two specified dates, I have tried the following and none of
them
> work:
>
> 1)
>
> ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions
WHERE
> 'Date' > " & dtpFrom.Text & " and 'Date' < dtpTo.Text & ";",
> AConnectionString)
>
> 2)
>
> ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions
WHERE
> [Date] BETWEEN DATEPART ('" & dtpFrom.Text & "') AND DATEPART ('" &
> dtpTo.Text & "')" , AConnectionString)
>
> 3)
>
> ADataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Contributions
WHERE
> 'Date' > 1/1/2004 and 'Date' < 6/1/2004;", AConnectionString)
>
> No records are returned. Any suggestions will be greatly appreciated.
>
> Thanks,
>
> Chuck
>
>
>

Brad
Mon Apr 26 06:35:05 CDT 2004

This is a multi-part message in MIME format.

------=_NextPart_000_000A_01C42B60.FE302780
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Charles,

Being fairly new to .NET and ADO, I don't feel "qualified" to help, but =
I had a similar situation recently. I have a data adapter that is =
filled based on a date selection and then later I use a dataview to =
filter the records based on the year. I know that my code is probably =
not the best, but here is the filter line:

dv.RowFilter =3D "EventDate > #12/31/03# AND EventDate < #1/1/2005#"

I do know that dates in Access need to be surrounded by #. I hope this =
helps. I am sure that Mr. Vaughn's answer is the best and his answer =
has even helped me some.

Brad

"Charles A. Lackman" <Charles@cet.com> wrote in message =
news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl...
> Hello,
>=20
> I am working with an Access Database and am trying to return a Querry =
that
> is between two specified dates, I have tried the following and none of =
them
> work:
>=20
> 1)
>=20
> ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM =
Contributions WHERE
> 'Date' > " & dtpFrom.Text & " and 'Date' < dtpTo.Text & ";",
> AConnectionString)
>=20
> 2)
>=20
> ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM =
Contributions WHERE
> [Date] BETWEEN DATEPART ('" & dtpFrom.Text & "') AND DATEPART ('" &
> dtpTo.Text & "')" , AConnectionString)
>=20
> 3)
>=20
> ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM =
Contributions WHERE
> 'Date' > 1/1/2004 and 'Date' < 6/1/2004;", AConnectionString)
>=20
> No records are returned. Any suggestions will be greatly appreciated.
>=20
> Thanks,
>=20
> Chuck
>=20
>=20
>
------=_NextPart_000_000A_01C42B60.FE302780
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT size=3D2>Charles,</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>Being fairly new to .NET and ADO, I don't feel =
"qualified" to=20
help, but I had a similar situation recently.  I have a data =
adapter that=20
is filled based on a date selection and then later I use a dataview to =
filter=20
the records based on the year.  I know that my code is probably not =
the=20
best, but here is the filter line:</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV>
<P><FONT size=3D2>dv.RowFilter =3D "EventDate > #12/31/03# AND =
EventDate <=20
#1/1/2005#"</FONT></P>
<P><FONT size=3D2></FONT><FONT size=3D2>I do know that dates in Access =
need to be=20
surrounded by #.  I hope this helps.  I am sure that Mr. =
Vaughn's=20
answer is the best and his answer has even helped me some.</FONT></P>
<P><FONT size=3D2>Brad</FONT></P></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>"Charles A. Lackman" <</FONT><A=20
href=3D"mailto:Charles@cet.com"><FONT =
size=3D2>Charles@cet.com</FONT></A><FONT=20
size=3D2>> wrote in message </FONT><A=20
href=3D"news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl"><FONT=20
size=3D2>news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl</FONT></A><FONT=20
size=3D2>...</FONT></DIV><FONT size=3D2>> Hello,<BR>> <BR>> I =
am working=20
with an Access Database and am trying to return a  Querry =
that<BR>> is=20
between two specified dates, I have tried the following and none of =
them<BR>>=20
work:<BR>> <BR>> 1)<BR>> <BR>> ADataAdapter =3D New=20
OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE<BR>> 'Date' =
> "=20
& dtpFrom.Text & " and 'Date' < dtpTo.Text & ";",<BR>> =

AConnectionString)<BR>> <BR>> 2)<BR>> <BR>> ADataAdapter =3D =
New=20
OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE<BR>> [Date] =
BETWEEN=20
DATEPART ('" & dtpFrom.Text & "') AND DATEPART ('" &<BR>> =

dtpTo.Text & "')" , AConnectionString)<BR>> <BR>> 3)<BR>> =
<BR>>=20
ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM Contributions =

WHERE<BR>> 'Date' > 1/1/2004 and 'Date' < 6/1/2004;",=20
AConnectionString)<BR>> <BR>> No records are returned.  Any=20
suggestions will be greatly appreciated.<BR>> <BR>> =
Thanks,<BR>>=20
<BR>> Chuck<BR>> <BR>> <BR>> </FONT></BODY></HTML>

------=_NextPart_000_000A_01C42B60.FE302780--

William
Mon Apr 26 09:12:36 CDT 2004

This is a multi-part message in MIME format.

------=_NextPart_000_052B_01C42B76.FF76FD60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

I think you meant Mr Ryan in this case, but it's an honor to be confused =
with him b/c Bill and Sceppa taught me everythign I know (although Miha =
has taught me a few tricks too). ;-)

BTW, don't ever feel like you aren't qualified. The point you bring up =
is excellent and even though it's a slighlty different case, what you =
point out is a mistake that's made pretty often and it's definitely =
helfpul. When I've needed somethign answered, I appreciate the help of =
a newbie just as much as anyone else so please don't let any worries =
about being 'qualified' keep you from participating, from the looks of =
it you are probably a lot more 'qualified' than you give yourself credit =
for!
"Brad Allison" <ballison@ukcdogs.com> wrote in message =
news:#yYKLL4KEHA.3428@TK2MSFTNGP09.phx.gbl...
Charles,

Being fairly new to .NET and ADO, I don't feel "qualified" to help, =
but I had a similar situation recently. I have a data adapter that is =
filled based on a date selection and then later I use a dataview to =
filter the records based on the year. I know that my code is probably =
not the best, but here is the filter line:

dv.RowFilter =3D "EventDate > #12/31/03# AND EventDate < #1/1/2005#"

I do know that dates in Access need to be surrounded by #. I hope =
this helps. I am sure that Mr. Vaughn's answer is the best and his =
answer has even helped me some.

Brad

"Charles A. Lackman" <Charles@cet.com> wrote in message =
news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl...
> Hello,
>=20
> I am working with an Access Database and am trying to return a =
Querry that
> is between two specified dates, I have tried the following and none =
of them
> work:
>=20
> 1)
>=20
> ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM =
Contributions WHERE
> 'Date' > " & dtpFrom.Text & " and 'Date' < dtpTo.Text & ";",
> AConnectionString)
>=20
> 2)
>=20
> ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM =
Contributions WHERE
> [Date] BETWEEN DATEPART ('" & dtpFrom.Text & "') AND DATEPART ('" &
> dtpTo.Text & "')" , AConnectionString)
>=20
> 3)
>=20
> ADataAdapter =3D New OleDb.OleDbDataAdapter("SELECT * FROM =
Contributions WHERE
> 'Date' > 1/1/2004 and 'Date' < 6/1/2004;", AConnectionString)
>=20
> No records are returned. Any suggestions will be greatly =
appreciated.
>=20
> Thanks,
>=20
> Chuck
>=20
>=20
>
------=_NextPart_000_052B_01C42B76.FF76FD60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I think you meant Mr Ryan in this case, =
but it's an=20
honor to be confused with him b/c  Bill and Sceppa taught me =
everythign I=20
know (although Miha has taught me a few tricks too). ;-)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>BTW, don't ever feel like you aren't=20
qualified.  The point you bring up is excellent and even though =
it's a=20
slighlty different case, what you point out is a mistake that's made =
pretty=20
often and it's definitely helfpul.   When I've needed =
somethign=20
answered, I appreciate the help of a newbie just as much as anyone else =
so=20
please don't let any worries about being 'qualified' keep you from=20
participating, from the looks of it you are probably a lot more =
'qualified' than=20
you give yourself credit for!</FONT></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Brad Allison" <<A=20
href=3D"mailto:ballison@ukcdogs.com">ballison@ukcdogs.com</A>> =
wrote in=20
message <A=20
=
href=3D"news:#yYKLL4KEHA.3428@TK2MSFTNGP09.phx.gbl">news:#yYKLL4KEHA.3428=
@TK2MSFTNGP09.phx.gbl</A>...</DIV>
<DIV><FONT size=3D2>Charles,</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>Being fairly new to .NET and ADO, I don't feel =
"qualified"=20
to help, but I had a similar situation recently.  I have a data =
adapter=20
that is filled based on a date selection and then later I use a =
dataview to=20
filter the records based on the year.  I know that my code is =
probably=20
not the best, but here is the filter line:</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV>
<P><FONT size=3D2>dv.RowFilter =3D "EventDate > #12/31/03# AND =
EventDate <=20
#1/1/2005#"</FONT></P>
<P><FONT size=3D2></FONT><FONT size=3D2>I do know that dates in Access =
need to be=20
surrounded by #.  I hope this helps.  I am sure that Mr. =
Vaughn's=20
answer is the best and his answer has even helped me some.</FONT></P>
<P><FONT size=3D2>Brad</FONT></P></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>"Charles A. Lackman" <</FONT><A=20
href=3D"mailto:Charles@cet.com"><FONT =
size=3D2>Charles@cet.com</FONT></A><FONT=20
size=3D2>> wrote in message </FONT><A=20
href=3D"news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl"><FONT=20
size=3D2>news:uvlJYbwKEHA.1392@TK2MSFTNGP09.phx.gbl</FONT></A><FONT=20
size=3D2>...</FONT></DIV><FONT size=3D2>> Hello,<BR>> <BR>> I =
am working=20
with an Access Database and am trying to return a  Querry =
that<BR>> is=20
between two specified dates, I have tried the following and none of=20
them<BR>> work:<BR>> <BR>> 1)<BR>> <BR>> ADataAdapter =
=3D New=20
OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE<BR>> =
'Date' >=20
" & dtpFrom.Text & " and 'Date' < dtpTo.Text & =
";",<BR>>=20
AConnectionString)<BR>> <BR>> 2)<BR>> <BR>> ADataAdapter =
=3D New=20
OleDb.OleDbDataAdapter("SELECT * FROM Contributions WHERE<BR>> =
[Date]=20
BETWEEN DATEPART ('" & dtpFrom.Text & "') AND DATEPART ('"=20
&<BR>> dtpTo.Text & "')" , AConnectionString)<BR>> =
<BR>>=20
3)<BR>> <BR>> ADataAdapter =3D New =
OleDb.OleDbDataAdapter("SELECT * FROM=20
Contributions WHERE<BR>> 'Date' > 1/1/2004 and 'Date' < =
6/1/2004;",=20
AConnectionString)<BR>> <BR>> No records are returned.  Any =

suggestions will be greatly appreciated.<BR>> <BR>> =
Thanks,<BR>>=20
<BR>> Chuck<BR>> <BR>> <BR>> =
</FONT></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_052B_01C42B76.FF76FD60--

Date Querry

Re: Date Querry by William

Re: Date Querry by Brad

Re: Date Querry by William