ASP Search w/ Access Database

<i><b>Originally posted by : Craig Brown (cbrown@madcityautos.com)</b></i><br /><br />I am learning ASP and have what is probably a very simple problem... I am creating a search page that passes a number in the query string, pulls the results out of an Access database and displays them on the results page. My problem is that the when numbered value I pass, (strSalePrice), in my query string is set as a 'number' in Access, I get an error from the results page, "Data type mismatch in criteria expression". This error does not happen when the Access field is set to 'text'. Any help on this would be appreciated!! Thanks! <br /><br />Below is my code...<br /><br />______________________<br />Search Page<br /><br />if not isempty(Request.Form("Search")) then<br /> strType = "AutoType=" & Request.Form("AutoType") & "&"<br /> strYear = "Year=" & Request.Form("Year") & "&"<br /> strSalePrice = "SalePrice=" & Request.Form("SalePrice") & "&"<br /> strOrderBy = "OrderBy=" & Request.Form("OrderBy")<br /> strQ = strType & strYear & strSalePrice & strOrderBy<br /> Response.Redirect "./get-results.asp?" & strQ<br /> end if<br /><br />_____________________<br />Results Page<br /><br />set conn = Server.CreateObject ("adodb.connection")<br /> conn.open "MadCityAutos", "sa", "password"<br /> strOrderBy = "ORDER by " & Request.QueryString("OrderBy") & " DESC"<br /> set RSAutos = conn.execute("SELECT * FROM main where " _<br /> & "AutoType = '" & Request.QueryString("AutoType") & "' and " _<br /> & "Year >= '" & Request.QueryString("Year") & "' and " _<br /> & "SalePrice <= '" & Request.QueryString("SalePrice") & "' ORDER by SalePrice DESC")<br /><br />

<i><b>Originally posted by : steve schofield</b></i><br />Your context is just fine except when passing numbers to the database either lose the single quotes around the data that is passed to the db. <br />hope this helps<br /><br />steve<br /><br />here is your code minus the single quotes around the strSalePrice data.<br /><br />set conn = Server.CreateObject ("adodb.connection")<br />conn.open "MadCityAutos", "sa", "password"<br />strOrderBy = "ORDER by " & Request.QueryString("OrderBy") & " DESC"<br />set RSAutos = conn.execute("SELECT * FROM main where " _<br />& "AutoType = '" & Request.QueryString("AutoType") & "' and " _<br />& "Year >= '" & Request.QueryString("Year") & "' and " _<br />& "SalePrice <= " & Request.QueryString("SalePrice") & " ORDER by SalePrice DESC")<br /><br /><br />------------<br />Craig Brown at 2/2/00 7:07:18 PM<br /><br /><br />I am learning ASP and have what is probably a very simple problem... I am creating a search page that passes a number in the query string, pulls the results out of an Access database and displays them on the results page. My problem is that the when numbered value I pass, (strSalePrice), in my query string is set as a 'number' in Access, I get an error from the results page, "Data type mismatch in criteria expression". This error does not happen when the Access field is set to 'text'. Any help on this would be appreciated!! Thanks! <br /><br />Below is my code...<br /><br />______________________<br />Search Page<br /><br />if not isempty(Request.Form("Search")) then<br /> strType = "AutoType=" & Request.Form("AutoType") & "&"<br /> strYear = "Year=" & Request.Form("Year") & "&"<br /> strSalePrice = "SalePrice=" & Request.Form("SalePrice") & "&"<br /> strOrderBy = "OrderBy=" & Request.Form("OrderBy")<br /> strQ = strType & strYear & strSalePrice & strOrderBy<br /> Response.Redirect "./get-results.asp?" & strQ<br /> end if<br /><br />_____________________<br />Results Page<br /><br />set conn = Server.CreateObject ("adodb.connection")<br /> conn.open "MadCityAutos", "sa", "password"<br /> strOrderBy = "ORDER by " & Request.QueryString("OrderBy") & " DESC"<br /> set RSAutos = conn.execute("SELECT * FROM main where " _<br /> & "AutoType = '" & Request.QueryString("AutoType") & "' and " _<br /> & "Year >= '" & Request.QueryString("Year") & "' and " _<br /> & "SalePrice <= '" & Request.QueryString("SalePrice") & "' ORDER by SalePrice DESC")<br /><br />