Solved

ASP, DBF, Date Checking

Posted on 2004-09-01
38
744 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
  • 16
  • 14
  • 3
  • +3
38 Comments
 
LVL 11

Expert Comment

by:SweatCoder
Comment Utility
is your db access or sqlserver?
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
Visual Foxpro 7
0
 
LVL 2

Expert Comment

by:SeanLWilliams
Comment Utility
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
 
LVL 23

Author Comment

by:slink9
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 23

Author Comment

by:slink9
Comment Utility
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
Comment Utility
Baby steps are better than stumbling...

FtB
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
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
Comment Utility
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
Comment Utility
Please give me a real ID, and I will repost this verbitam if you like.

FtB
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I am glad that this worked out!

FtB
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Using AJAX to call classic ASP function 4 68
CSS Question.. 3 72
SQL Server Web Traffic Limitations 4 41
Time/Date Query 11 33
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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now