Solved

ASP, DBF, Date Checking

Posted on 2004-09-01
38
770 Views
Last Modified: 2010-08-05
I have an input screen that takes a user id, password, and date range.  I want to compare that date range to the date field of each record for that user id.  I can't find the conversion needed in order to be able to accomplish this.  The select line is

rs.Open "SELECT * FROM games where uid= '"& Username &"' and date >=" & begdt & " and date <=" & enddt, conn, 1

I am accepting the date in text format.  Is there a different format I should be using?
0
Comment
Question by:slink9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 14
  • 3
  • +3
38 Comments
 
LVL 11

Expert Comment

by:SweatCoder
ID: 11955540
is your db access or sqlserver?
0
 
LVL 23

Author Comment

by:slink9
ID: 11955684
Visual Foxpro 7
0
 
LVL 2

Expert Comment

by:SeanLWilliams
ID: 11956260
I would change to
rs.Open "SELECT * FROM games where uid= '"& Username &"' and [date] >=" & begdt & " and [date] <=" & enddt, conn, 1

try that, as date is a function I believe.

the other you might want to do is
**  untested **

rs.Open "SELECT * FROM games where uid= '"& Username &"' and convert(datetime, [date]) >=" & begdt & " and convert(datetime, [date]) <=" & enddt, conn, 1


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Author Comment

by:slink9
ID: 11957210
date is the field name.  It is already in date format in the DBF file.  Do I need to do a concersion on it or should the conversion be on the bedgate and enddate variables?
0
 
LVL 2

Assisted Solution

by:SeanLWilliams
SeanLWilliams earned 200 total points
ID: 11957308
if its already in date format then you won't need to convert it

try this instead
rs.Open "SELECT * FROM games where uid= '"& Username &"' and [date] >='" & begdt & "' and [date] <='" & enddt & "'", conn, 1

I have added quote marks around the dates
0
 
LVL 23

Author Comment

by:slink9
ID: 11957690
The begdate and enddate variables are where I believe the conversion needs to be made.  They are input as text.  When I do a convert() on them I get

Type mismatch: 'convert'


Like I can't convert a text field to date.  Can I input it as a date in the form?
0
 
LVL 11

Expert Comment

by:L00M
ID: 11957718
You may need to format your dates differently as well:

prior to rs.open, do something like:

begdt = day(begdt) & "/" & month(begdt) & "/" & year(begdt)
enddt = day(enddt) & "/" & month(enddt) & "/" & year(enddt)

rs.open...

NB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11957948
Perhaps this:

rs.Open "SELECT * FROM games where uid= '"& Username &"' and [date] BETWEEN '" & begdt & "' and '" & enddt & "'", conn, 1


FtB
0
 
LVL 2

Expert Comment

by:SeanLWilliams
ID: 11957985
You won't need to convert the text field if you are entering it as a date,
all I will say though is ( if your not in the USA )   then use a format of  dd/mmm/yy ( or yyyy )

or mmm/dd/yy     anything where you type the 3 letter code of the date,  this avoids any confusion in date entry
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 300 total points
ID: 11958028
Here is a query for dates from a table that I have in VFP run in VFP:

SELECT * FROM invoices WHERE inv_date between CtoD('01/01/2003') AND CtoD('01/01/2004')

Running this through ASP, however, I think that I would have to do:

SELECT * FROM invoices WHERE inv_date between '01/01/2003' AND '01/01/2004'

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11958051
I believe I am on the right track.  The begdate and enddate appear not to be sent from the login page.  With the [] around date and quotes around the actual date I get an empty page.  If I then display those two varables they are empty.  On to other problems ... I hope to have this resolved in the next hour or so.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958081
Make life easier on yourself--hard code the dates and the UID for now until you know that part of the code works. Next, work on passing the values from the other page.

FtB

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958091
BTW, I put Date in [ ] delimiters because Date is a reserved word in sql and that might give you issues. Also, I used the BETWEEN operator because that seems most appropriate for what you are trying to do.

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11958100
Good idea.  I have been programming for too long not to think of that one myself.  Thanks.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958102
Finally, don't forget to put delimiters around your dates:

WRONG: SELECT * FROM invoices WHERE inv_date between 01/01/2003 AND 01/01/2004


CORRECT: SELECT * FROM invoices WHERE inv_date between '01/01/2003' AND '01/01/2004'


FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11958169
I will be glad when I get the book I have ordered!!!!

How do I set the begdt and enddt to values?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958183
rs.Open "SELECT * FROM games where uid= 'YourUserID' and [date] BETWEEN '01/01/200.' and '01/01/2004';", conn, 1
0
 
LVL 11

Expert Comment

by:L00M
ID: 11958197
you can set them many ways, but basically:

begdt='01/01/2003'
enddt='01/01/2004'

But that's not very dynamic. Instead, you could retrieve them from a web form:

begdt = Request.Form("begdt")

NB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958263
LOOM--

Please read the whole thread. We are hard coding the values for now for troubleshooting purposes. Once slink9 has found the proper syntax to match the VFP DBMS system, he will grab the values from the request object.
0
 
LVL 23

Author Comment

by:slink9
ID: 11958344
Okay.  Another beginner mistake.  I tried L00m's method and it didn't work.  I will put the date in the SELECT statement.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958374
Baby steps are better than stumbling...

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11958516
I put the date in the select statement with BETWEEN and as shown

rs.Open "SELECT * FROM games where uid= '"& Username &"' and gmdt >= '12/01/2003' and gmdt <= '12/08/2003'", conn, 1

I get

ODBC driver does not support the requested properties.

Any ideas?  Time to get ready for church ...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958558
This doesn't look like anything that I have suggested above!!!!

Please try this exactly as written with only one change--replace YourUserID with the name of a real id:

rs.Open "SELECT * FROM games where uid= 'YourUserID' and [date] BETWEEN '12/01/2003'  and '12/08/2003';", conn, 1
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958561
Please give me a real ID, and I will repost this verbitam if you like.

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11958630
By the way, I changed the fieldname from DATE to GMDT.  The Username was working fine before, but I hardcoded it for the test.  here is the exact statement now.

rs.Open "SELECT * FROM games where uid= 'br' and gmdt >= '12/01/2003' and gmdt <= '12/08/2003'", conn, 1

and it displays the same message.  It seems like it is unable to compare the text field and the date field.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11958806
rs.Open "SELECT * FROM games where uid= 'br' and gmdt BETWEEN '12/01/2003' and '12/08/2003'", conn, 1


If that doesn't work, then we can try a few other things...

ftB
0
 
LVL 23

Author Comment

by:slink9
ID: 11959362
Same problem.

Does it matter that the GMDT field is a VFP date and the dates seem to be formatted as text?  If this were VFP I would do ctod() on the two dates and it would then be a date to date comparison.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11959372
That is what I had above in the sample from my table:

SELECT * FROM invoices WHERE inv_date between CtoD('01/01/2003') AND CtoD('01/01/2004')

I don't think it likely, however, that ADO will support this. Nonetheless, it is worth a shot:



rs.Open "SELECT * FROM games where uid= 'br' and gmdt >= CToD('12/01/2003') and gmdt <= CToD('12/08/2003')", conn, 1

0
 
LVL 23

Author Comment

by:slink9
ID: 11959409
Interesting, VFP command used in ASP.  The list was filtered and worked beautifully.  Now that I know what format to use I will try to get it from the login form.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11959459
Okay, great. I have seen commands from the DBMS filter upwards from time to time--I guess that is the case here.

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11959562
I have unsuccessfully done a little on getting the fields from the LOGIN page.  It brings over uid and pwd fine, but seems to ignore the two dates.  I will work on it tomorrow.  Thanks.
0
 
LVL 4

Expert Comment

by:kssaran
ID: 11959587
It has always been troublesome with this date comparision, try this

In general the comparision to avoid all troubles can be done this way
'CONVERT(VARCHAR,DATECOLUMN,112) will convert the date value like yyyymmdd and can be compared
'Convert the date range also into the same format
begdt = year(begdt) & month(begdt) & day(begdt)
enddt = year(enddt) & month(enddt) & day(enddt)

objRS.Open "SELECT * FROM games where uid= 'br' and CONVERT(VARCHAR,gmdt,112) >= '" & begdt & "' and CONVERT(VARCHAR,gmdt,112) <= '"& enddt &"'", conn, 1

I'm sure this will give you the solution what u r expecting...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11959616
Let's do a little troubleshooting by putting this at the top of your page:

<%
for each objField in Request.Form
   response.write(objField & ": " & request.Form(objField) & "<BR>")
next

Before we jump to conclusions about the date formatting, let's make sure that the login page is passing what it is supposed to.

FtB
0
 
LVL 11

Expert Comment

by:L00M
ID: 11959761
Sorry Experts, didn't mean to jump ahead of the baby steps.
I was trying to accomplish the same thing as you... and I see we are almost ready to walk.
This will be a good tutorial when it's done.
NB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11959785
Not a problem. I just had the sense that this was going to be a step-by-step deal and that it would be best to test each step along the way.

So up next--figure out what is being passed!

<%
for each objField in Request.Form
   response.write(objField & ": " & request.Form(objField) & "<BR>")
next
%>
0
 
LVL 23

Author Comment

by:slink9
ID: 11961781
Okay.  The begin and end dates are being passed.  Now I need to get the proper format to use them for filtering purposes.  Right now I have

rs.Open "SELECT * FROM games where uid= '" & Username & "' and gmdt between ctod(begdt) and ctod(enddt)", conn, 1

and get

txtbegdate: 12/01/03
txtenddate: 12/08/03
cmdSubmit: Login
12/01/03;;;;
12/08/03;;;;
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

ODBC driver does not support the requested properties.

/data/verify.asp, line 49

Username and password were left out of the display intentionally.  I hard coded the dates in there and it worked fine.  The repeat of the dates with the ; characters after it show that the variables are still set before the SELECT.

What is missing here?
0
 
LVL 23

Author Comment

by:slink9
ID: 11961868
Problem solved.  I did some more looking and decided to try setting a variable to use for the query.  Now it works.  Here are the final two statements to accomplish this.

qry = "select * from games where uid= '"&Username&"' and gmdt between ctod('"&begdt&"') and ctod('"&enddt&"')"
rs.Open qry, conn, 1
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11962770
I am glad that this worked out!

FtB
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question