ASP, DBF, Date Checking

is your db access or sqlserver?

Visual Foxpro 7

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

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?

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?

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

Perhaps this:

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


FtB

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

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.

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

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

Good idea.  I have been programming for too long not to think of that one myself.  Thanks.

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

I will be glad when I get the book I have ordered!!!!

How do I set the begdt and enddt to values?

rs.Open "SELECT * FROM games where uid= 'YourUserID' and [date] BETWEEN '01/01/200.' and '01/01/2004';", conn, 1

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

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.

Okay.  Another beginner mistake.  I tried L00m's method and it didn't work.  I will put the date in the SELECT statement.

Baby steps are better than stumbling...

FtB

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 ...

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

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

FtB

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.

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

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.

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

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.

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

FtB

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.

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...

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

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

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
%>

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?

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

I am glad that this worked out!

FtB