Date Querry

  • Thread starter Thread starter Charles A. Lackman
  • Start date Start date
C

Charles A. Lackman

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
 
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 said:
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
Click to expand...
 
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 = "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
 
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!
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 = "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
 
Back
Top