Hi all,

Im a beginner in ASP programming n MS SQL server. I created a table named DataReport in SQL server with the following colom
1. Date
2. Item
3. Location
4. Rate
5. Price

I created an HTML form page with three imput fields namely, Location, Datefrom, and Dateto. What I want to achiev is dat when user enter a location and the date from and date, then submit the form, an ASP page will show to display the data from the location selected and within the dates input in the form field. However, all records in the table were displayed regardless of location typed or date range selected.

below is my ASP code


<%


Set oCon = Server.CreateObject ("ADODB.Connection"
oCon.Open "Provider=sqloledb;Data Source=RESEARCH\SQLEXPRESS;Initial Catalog=smarta;User Id=sa;Password=10010;"

Set Rec = Server.CreateObject ("ADODB.Recordset"


search = request.querystring("search"
location = request.form("slocation"
datefrom = request.form("datefrom"
dateto = request.form("dateto"

if agent <> "" then

SQL = "SELECT * FROM DataReport WHERE location like '%" & slocation & "%' AND DATE BETWEEN #" & Format(datefrom,"mm/dd/yyyy" & "# and #" & Format(dateto,"mm/dd/yyyy" & "#;"




else

SQL = "SELECT * FROM DataReport ORDER BY Item" & sort
End if
Rec.Open SQL, oCon

if Rec.EOF OR Rec.BOF Then
response.write "<br><br>"

response.write "<p align=center><b>No Records Found , </b></p>"

end if


Do While Not Rec.EOF
'AND DisplayNum < 5
r = n Mod 2
if r <> 0 then
'rowBColor = "#99CCFF"
rowBColor = "white"
else
'rowBColor = "#CCFFFF"
rowBColor = "#FFFFFF"
end if

If Rec("DATE" < Date-3 then
rowColor = "Red"
else If Rec("DATE" < Now()-2 then
rowColor = "Blue"
else
rowColor = "Green"
end if
end if

response.write("<tr>"
response.write("<td bgcolor="& rowBColor&">" & Rec("Item"& "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("Location" & "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("rate" & "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("price" & "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("total" & "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("Status" & "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("Approved" & "</td>"
response.write("<td bgcolor="& rowBColor&">" & Rec("date" & "</td>"

%>
<% rec.MoveNext
Loop
%>

Pls I will be happy if I can get way out to this problem

Yeah, I did it successfully

Tank God