Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

ASP, DBF, Date Checking

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
slink9
Asked:
slink9
  • 16
  • 14
  • 3
  • +3
2 Solutions
 
SweatCoderCommented:
is your db access or sqlserver?
0
 
slink9Author Commented:
Visual Foxpro 7
0
 
SeanLWilliamsCommented:
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
Technology Partners: 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!

 
slink9Author Commented:
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
 
SeanLWilliamsCommented:
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
 
slink9Author Commented:
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
 
L00MCommented:
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
 
fritz_the_blankCommented:
Perhaps this:

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


FtB
0
 
SeanLWilliamsCommented:
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
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
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
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
Good idea.  I have been programming for too long not to think of that one myself.  Thanks.
0
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
I will be glad when I get the book I have ordered!!!!

How do I set the begdt and enddt to values?
0
 
fritz_the_blankCommented:
rs.Open "SELECT * FROM games where uid= 'YourUserID' and [date] BETWEEN '01/01/200.' and '01/01/2004';", conn, 1
0
 
L00MCommented:
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
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
Okay.  Another beginner mistake.  I tried L00m's method and it didn't work.  I will put the date in the SELECT statement.
0
 
fritz_the_blankCommented:
Baby steps are better than stumbling...

FtB
0
 
slink9Author Commented:
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
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
Please give me a real ID, and I will repost this verbitam if you like.

FtB
0
 
slink9Author Commented:
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
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
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
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
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
 
fritz_the_blankCommented:
Okay, great. I have seen commands from the DBMS filter upwards from time to time--I guess that is the case here.

FtB
0
 
slink9Author Commented:
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
 
kssaranCommented:
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
 
fritz_the_blankCommented:
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
 
L00MCommented:
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
 
fritz_the_blankCommented:
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
 
slink9Author Commented:
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
 
slink9Author Commented:
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
 
fritz_the_blankCommented:
I am glad that this worked out!

FtB
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 16
  • 14
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now