Searching Database using ASP

hello,

i want to have this option of 'searching by date' in the form. the date in the database is in the following format: 15 June 2003. the database is ms access based.

i have managed to create the form, the database and all the necessary connections. the queries are also there. however i m getting a problem in the search result.

the asp page that does the querying and displaying always returns a 'no match found' whenever i type june or 2003 in the search form. but when i type 15 i get the search result.

my code is as follows:

<b>
<!--#include file="adovbs.inc"-->

<%
date = request.form("date")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=letmein; DBQ=" & Server.MapPath("\db.mdb") & ";"
Conn.Open
Set rsEnquiry_Search = Server.CreateObject("ADODB.Recordset")
Search_SQL = "Select * from enquiries where "

If date <> "" then
Search_SQL = Search_SQL & " Date LIKE '" & date & "%'"
end if

<%
rsEnquiry_Search.Open Search_SQL, Conn, 1
If Not rsEnquiry_Search.BOF Then
rsEnquiry_Search.MoveFirst

Do While not rsEnquiry_Search.EOF
%>

<table border="0" cellpadding="0" cellspacing="2" style="border-collapse: collapse; bordercolor="#111111" width="100%">
<tr>
<td width= "34%" style="border-top-style: solid; border-top-width: 1"><b>Enquiry ID</b></td></td>
<td width= "66%" style="border-top-style: solid; border-top-width: 1"><b>Complete Name</b></td>
</tr>
<tr>
<td width= "34%"><%= (rsEnquiry_Search("ID_no")) %></td></td>
<td width= "66%"><%= (rsEnquiry_Search("CompleteName")) %></td>
</tr>

</table>


<%
rsEnquiry_Search.MoveNext
Loop

Else
response.write "<tr><td><i>No Matches Found</i></td>"
End If
%>
</b>

is there a way whereby i can get the result displayed even if i enter june or 2003? i would appreciate youy help.

thnx in advance,
fmh002

Try changing the variable name to something other than date (i.e. searchdate).
From memory, the word date is reserved and holds other meanings for the sql query.

yea i noticed that, i've changed to some other name but it still gives me the same prob

I think you should separate the date into 3 separate fields for Mont.h, Day and Year, and then you would need some more complex logic in your code to produce the correct query results

that would make things complicated like before. i wanted it to be a single field only that's why i got the date vbscript in the first place (previous post). well, thnx for the suggestion.

im still looking for help on this one.

fmh002

well, it can be one field or 3, but if it's one you first have to determine if what they entered is a month or year and then create your where clause appropriately, and if they entered a full date you will have to make sure it is formatted correctly before using it in the where clause.

and how would do all that? could you give me some help on it?

I still feel you would be much better off with 3 separate fields, probably with pulldowns to choose from, and don't think this makes the process more complicated. That way you have more control on how the date is formatted and the user could still only enter the parts of the date they wanted to use. But if you must do it the other way you could first check if what was entered was in the list of valid months, then if it was a 4 digit number in the valid range of years, etc

i guess i'll have to stick to those pull down lists for the date then.

thnx for the concern pal, appreciate your suggestions.

peace,
fmh002

This occurred to me overnight.
Have you thought of making the data type of the date field to a text field (in the db structure).
That way, the current query string you have should (theoretically) work.
Because you are asking the database to look for text strings, rather than date components.

This might be your answer, unless you need the date field for date calculations of some sort.

hello

actaully the date feilds' data type is set to text. practically it doesnt work, how come your theory is contradicting? the problem is those spaces, if i only i could get rid of them somehow... or maybe try to enter the date in the following format 16/June/2003

peace

to get it to work as a string search you would need to add *'s to your where as in
Date LIKE '*" & date & "*'"
since Access uses * not %.
You would still need to make sure everything is formatted properly so you do get a match. It seems to me the big problem with formatting dates this way (as text) is you lose the ability to check for > or < or a range of dates easily.

Actually, Access does use % for wildcards.

I found the problem though. You will need to change the Search query as follows


The reason you wouldn't get any matches with the previous search string,
is you hadn't told the query to look for any characters before the date.


Hope this helps.

mohecan man you helped me out big time. finally i got it working. i have to admit it, looking for answers on this forum is effective for beginners like me.

i really appreciate your help.

fmh002

No problem, that's what we're all here for.
Before you know it, you'll be posting solutions for others.