Search Car Database

Options

Right now everything but the last selection option works., Due to length of time to get this perfected usually takes forever.

Title:
Year:		<% Response.Write "Any Year" Set rs = DataConn.Execute("SELECT Year FROM cars GROUP By YEAR ORDER BY Year DESC") Do Until rs.EOF Response.Write"" & CInt(rs("Year")) & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
Body Style:		<% Response.Write "Any Body Style" Set rs = DataConn.Execute("SELECT BodyStyle FROM cars GROUP By BodyStyle") Do Until rs.EOF Select case rs("bodystyle") '-------------------------- '2-door coupe (2c) '2-door convertible (2n) '2-door sedan (2s) '2-door hardtop (2d) '2-door targa (2g) '2-door roadster (2r) '2-door hatchback/liftback (2h) '2-door wagon (2w) 'any 2-door car [2c/2n/2s/2d/2g/2r/2h/2w] '4-door sedan (4s) '4-door convertible/phaeton (4n) '4-door hardtop (4d) '4-door hatchback/liftback (4h) '4-door wagon (4w) 'any 4-door car [4s/4n/4d/4h/4w] 'minivan [mp] 'van [vn] 'sport-utility 2-door [2u] 'sport-utility 4-door [4u] '2-door pickup truck [2p] '4-door pickup truck [4p] '4-door crossover [4o] '2-door pickup sedan [2y] '4-door pickup sedan [4y] '------------------------------ Case "2c": BODYST = "2-door coupe" Case "2n": BODYST = "2-door convertible" Case "2s": BODYST = "2-door sedan" Case "2d": BODYST = "2-door hardtop" Case "2g": BODYST = "2-door targa" Case "2r": BODYST = "2-door roadster" Case "2h": BODYST = "2-door hatchback/liftback" Case "2w": BODYST = "2-door wagon" '-- NEXT GROUP (4 door) -- Case "4s": BODYST = "4-door sedan" Case "4n": BODYST = "4-door convertible/phaeton" Case "4d": BODYST = "4-door hardtop" Case "4h": BODYST = "4-door hatchback/liftback" Case "4w": BODYST = "4-door wagon" '-- NEXT GROUP (Other) -- Case "mp": BODYST = "minivan" Case "vn": BODYST = "van" Case "2u": BODYST = "sport-utility 2-door" Case "4u": BODYST = "sport-utility 4-door" Case "2p": BODYST = "2-door pickup truck" Case "4p": BODYST = "4-door pickup truck" Case "4o": BODYST = "4-door crossover" Case "2y": BODYST = "2-door pickup sedan" Case "4y": BODYST = "4-door pickup sedan" Case Else: BODYST = rs("bodystyle") End Select Response.Write"" & BODYST & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
Engine:		<% Response.Write "Any Engine" Set rs = DataConn.Execute("SELECT Engine FROM Engines ORDER BY Identifier") Do Until rs.EOF Select Case rs("Engine") ' List of engine types '---------------------- 'inline-two [I2] 'inline-three [I3] 'inline-four [I4] 'vee-four [V4] 'flat-four [F4] 'inline-five [I5] 'inline-six [I6] 'vee-six [V6] 'flat-six [F6] 'vee-eight [V8] 'w-eight [W8] 'flat-eight [F8] 'vee-ten [V10] 'vee-twelve [V12] 'w-twelve [W12] 'flat-twelve [F12] 'vee-sixteen [V16] 'w-sixteen [W16] 'gas-electric hybrid [EH] 'mono-rotor [R1] 'dual-rotor [R2] 'triple-rotor [R3] 'gas turbine [GT] '-------------------- Case "I2": ETYPE = "inline-two" Case "I3": ETYPE = "inline-three" Case "I4": ETYPE = "inline-four" Case "V4": ETYPE = "vee-four" Case "F4": ETYPE = "flat-four" Case "I5": ETYPE = "inline-five" Case "I6": ETYPE = "inline-six" Case "V6": ETYPE = "vee-six" Case "F6": ETYPE = "flat-six" Case "V8": ETYPE = "vee-eight" Case "W8": ETYPE = "w-eight" Case "F8": ETYPE = "flat-eight" Case "V10": ETYPE = "vee-ten" Case "V12": ETYPE = "vee-twelve" Case "W12": ETYPE = "w-twelve" Case "F12": ETYPE = "flat-twelve" Case "V16": ETYPE = "vee-sixteen" Case "W16": ETYPE = "w-sixteen" Case "EH": ETYPE = "gas-electric hybrid" Case "R1": ETYPE = "mono-rotor" Case "R2": ETYPE = "dual-rotor" Case "R3": ETYPE = "triple-rotor" Case "GT": ETYPE = "gas turbine" Case Else: ETYPE = rs("engine") End Select Response.Write"" & ETYPE & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
EPA Class:		<% Response.Write "Any EPA Class" Set rs = DataConn.Execute("SELECT epaclass FROM cars WHERE [epaclass] IS NOT NULL GROUP BY [EPAClass]") Do Until rs.EOF Response.Write"" & rs("epaclass") & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
Nation of Corporate Origin:		<% Response.Write "Any Country" Set rs = DataConn.Execute("SELECT nationofcorporateorigin FROM cars WHERE [nationofcorporateorigin] IS NOT NULL GROUP BY nationofcorporateorigin ORDER BY nationofcorporateorigin DESC") Do Until rs.EOF Response.Write"" & rs("nationofcorporateorigin") & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
Price Block:		<% Response.Write "Any Price Block" Set rs = DataConn.Execute("SELECT priceblock FROM cars WHERE [priceblock] IS NOT NULL GROUP BY priceblock ORDER BY priceblock ASC") Do Until rs.EOF Select Case rs("priceblock") Case "02999": PRIVVAL = "$0-$2,999" Case "30005999": PRIVVAL = "$3,000-$5,999" Case "60009999": PRIVVAL = "$6,000-$9,999" Case "1000014999": PRIVVAL = "$10,000-$14,999" Case "1500019999": PRIVVAL = "$15,000-$19,999" Case "2000024999": PRIVVAL = "$20,000-$24,999" Case "2500029999": PRIVVAL = "$25,000-$29,999" Case "3000039999": PRIVVAL = "$30,000-$39,999" Case "4000049999": PRIVVAL = "$40,000-$49,999" Case "5000069999": PRIVVAL = "$50,000-$69,999" Case "7000099999": PRIVVAL = "$70,000-$99,999" Case "100000129999": PRIVVAL = "$100,000-$129,999" Case "130000199999": PRIVVAL = "$130,000-$199,999" Case "200000299999": PRIVVAL = "$200,000-$299,999" Case "300000499999": PRIVVAL = "$300,000-$499,999" Case "5000001500000": PRIVVAL = "$500,000-$1,500,000" Case Else: PRIVVAL = rs("priceblock") End Select Response.Write"" & PRIVVAL & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
Driveline:		<% Response.Write "Any Driveline" Set rs = DataConn.Execute("SELECT driveline FROM cars WHERE [driveline] IS NOT NULL GROUP BY driveline ORDER BY driveline DESC") Do Until rs.EOF Select Case rs("driveline") '----- 'Driveline list '----- 'front-engine, front-drive [F/F] 'front-engine, rear-drive [F/R] 'front-engine, all-drive [F/A] 'mid-engine, rear-drive [M/R] 'mid-engine, all-drive [M/A] 'rear-engine, rear-drive [R/R] 'rear-engine, all-drive [R/A] Case "F/F": DRTYPE = "front-engine, front-drive" Case "F/R": DRTYPE = "front-engine, rear-drive" Case "F/A": DRTYPE = "front-engine, all-drive" Case "F/R, F/A": DRTYPE = "front-engine, rear-drive front-engine, all-drive" Case "M/R": DRTYPE = "mid-engine, rear-drive" Case "M/A": DRTYPE = "mid-engine, all-drive" Case "R/R": DRTYPE = "rear-engine, rear-drive" Case "R/A": DRTYPE = "rear-engine, all-drive" Case Else: DRTYPE = rs("driveline") End Select Response.Write"" & DRTYPE & "" & vbcrlf rs.MoveNext Loop rs.Close Set rs = Nothing Response.Write"" %>
Four-wheel Steering:
Turbocharger or Supercharger:

Search Results: <% YEARQRY = Request.QueryString("year") BODYQRY = Request.QueryString("bodystyle") dim RS Set RS = Server.Createobject ("ADODB.Recordset") 'create SQL query strSQL = "SELECT * " strSQL = strSQL & "FROM cars " strSQL = strSQL & "WHERE " If Request.QueryString("year") = "%" Then strSQL = strSQL & "(year LIKE '%')" ElseIf Not Request.QueryString("year") = "%" Then strSQL = strSQL & "year = '" & Request.QueryString("year") & "' " Else End IF strSQL = strSQL & " AND " If Request.QueryString("bodystyle") = "%" Then strSQL = strSQL & "(bodystyle LIKE '%')" ElseIf Not Request.QueryString("bodystyle") = "%" Then strSQL = strSQL & "bodystyle = '" & Request.QueryString("bodystyle") & "' " Else End IF strSQL = strSQL & " AND " If Request.QueryString("engine") = "%" Then strSQL = strSQL & "(engine LIKE '%')" ElseIf Not Request.QueryString("engine") = "%" Then strSQL = strSQL & "engine = '" & Request.QueryString("engine") & "' " Else End IF strSQL = strSQL & " AND " If Request.QueryString("epaclass") = "%" Then strSQL = strSQL & "(epaclass LIKE '%')" ElseIf Not Request.QueryString("epaclass") = "%" Then strSQL = strSQL & "epaclass = '" & Request.QueryString("epaclass") & "' " Else End IF strSQL = strSQL & " AND " If Request.QueryString("nationofcorporateorigin") = "%" Then strSQL = strSQL & "(nationofcorporateorigin LIKE '%')" ElseIf Not Request.QueryString("nationofcorporateorigin") = "%" Then strSQL = strSQL & "nationofcorporateorigin = '" & Request.QueryString("nationofcorporateorigin") & "' " Else End IF strSQL = strSQL & " AND " If Request.QueryString("priceblock") = "%" Then strSQL = strSQL & "(priceblock LIKE '%')" ElseIf Not Request.QueryString("priceblock") = "%" Then strSQL = strSQL & "priceblock = '" & Request.QueryString("priceblock") & "' " Else End IF strSQL = strSQL & " AND " If Request.QueryString("title") = "" Then strSQL = strSQL & "(title LIKE '%')" ElseIf Not Request.QueryString("title") = "" Then strSQL = strSQL & "title = '" & Request.QueryString("title") & "' " Else End If 'What to order by? strSQL = strSQL & " ORDER BY BODYSTYLE, YEAR DESC;" 'Enable Debug by Uncommenting next line. '---------------------------------------------- response.write "
" & strSQL & "" ' - START OF PAGING - ' Check to see if there is value in the PG querystring. If there ' is, we know that the client is using the Next and/or Prev hyperlinks ' to navigate the recordset. '------------------------------------ If Request.QueryString("pg") = "" Then intPage = 1 Else intPage = Request.QueryString("pg") End If 'Set parameters for DB connection '----------------------------------- Rs.CursorLocation = 3 'adUseClient Rs.CursorType = 3 'adOpenStatic ' Set the PageSize, CacheSize, and populate the intPageCount and ' intRecordCount variables. Rs.PageSize = 25 ' - END OF PAGING 1 - 'Debug SQL String 'Response.Write strSQL RS.Open strSQL , Application("DSN") '- START OF PAGING 2 - Rs.CacheSize = Rs.PageSize intPageCount = Rs.PageCount intRecordCount = Rs.RecordCount ' Now you must double check to make sure that you are not before the start ' or beyond end of the recordset. If you are beyond the end, set ' the current page equal to the last page of the recordset. If you are ' before the start, set the current page equal to the start of the recordset. If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount If CInt(intPage) <= 0 Then intPage = 1 ' Make sure that the recordset is not empty. If it is not, then set the ' AbsolutePage property and populate the intStart and the intFinish variables. If intRecordCount > 0 Then Rs.AbsolutePage = intPage intStart = Rs.AbsolutePosition If CInt(intPage) = CInt(intPageCount) Then intFinish = intRecordCount Else intFinish = intStart + (Rs.PageSize - 1) End if End If '- END OF PAGING 2 - If RS.Eof And RS.BoF Then Response.Write "

Sorry no cars found.. You may have invalid search parameters.

Try Again?
" Else '---------------------------- ' Produce the Search Results formatted the way we want. '---------------------------- VALTOTALWID = 500 VALWIDTH1 = 260 VALWIDTH2 = 80 VALWIDTH3 = 150 Response.write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf 'Loop through results til End of file. Select Case rs("priceblock") Case "7000099999": PRIVVAL2 = "$70K-$100K" Case "5000069999": PRIVVAL2 = "$50K-$70K" Case "2000001000000": PRIVVAL2 = "$200K-$1MIL" Case "130000199999": PRIVVAL2 = "$130K-$200K" Case "150000249999": PRIVVAL2 = "$150K-$250K" Case "4000001000000": PRIVVAL2 = "$400K-$1MIL" Case "100000149999": PRIVVAL2 = "$100K-$150K" Case "3000039999": PRIVVAL2 = "$30K-$40K" Case "250000399999": PRIVVAL2 = "$250K-$400K" Case "2000029999": PRIVVAL2 = "$20K-$30K" Case "4000049999": PRIVVAL2 = "$40K-$50K" Case "1500019999": PRIVVAL2 = "$15K-$20K" Case "1000014999": PRIVVAL2 = "$10K-$15K" Case Else: PRIVVAL2 = rS("priceblock") End Select Select case rs("bodystyle") '-------------------------- '2-door coupe (2c) '2-door convertible (2n) '2-door sedan (2s) '2-door hardtop (2d) '2-door targa (2g) '2-door roadster (2r) '2-door hatchback/liftback (2h) '2-door wagon (2w) 'any 2-door car [2c/2n/2s/2d/2g/2r/2h/2w] '4-door sedan (4s) '4-door convertible/phaeton (4n) '4-door hardtop (4d) '4-door hatchback/liftback (4h) '4-door wagon (4w) 'any 4-door car [4s/4n/4d/4h/4w] 'minivan [mp] 'van [vn] 'sport-utility 2-door [2u] 'sport-utility 4-door [4u] '2-door pickup truck [2p] '4-door pickup truck [4p] '4-door crossover [4o] '2-door pickup sedan [2y] '4-door pickup sedan [4y] '------------------------------ Case "2c": BODYST2 = "2-door coupe" Case "2n": BODYST2 = "2-door convertible" Case "2s": BODYST2 = "2-door sedan" Case "2d": BODYST2 = "2-door hardtop" Case "2g": BODYST2 = "2-door targa" Case "2r": BODYST2 = "2-door roadster" Case "2h": BODYST2 = "2-door hatchback/liftback" Case "2w": BODYST2 = "2-door wagon" '-- NEXT GROUP (4 door) -- Case "4s": BODYST2 = "4-door sedan" Case "4n": BODYST2 = "4-door convertible/phaeton" Case "4d": BODYST2 = "4-door hardtop" Case "4h": BODYST2 = "4-door hatchback/liftback" Case "4w": BODYST2 = "4-door wagon" '-- NEXT GROUP (Other) -- Case "mp": BODYST2 = "minivan" Case "vn": BODYST2 = "van" Case "2u": BODYST2 = "sport-utility 2-door" Case "4u": BODYST2 = "sport-utility 4-door" Case "2p": BODYST2 = "2-door pickup truck" Case "4p": BODYST2 = "4-door pickup truck" Case "4o": BODYST2 = "4-door crossover" Case "2y": BODYST2 = "2-door pickup sedan" Case "4y": BODYST2 = "4-door pickup sedan" Case Else: BODYST2 = rs("bodystyle") End Select For intRecord = 1 to Rs.PageSize if intRecord mod 2 Then 'BEGIN = "" Else 'BEGIN = "" End if Response.Write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf Response.Write "" & vbcrlf Rs.MoveNext If Rs.EOF Then Exit for Next Response.Write "

Title	Price Range	Body Style
" & rs("title") & " 1	" & PRIVVAL2 & "	" & BODYST2 & "

" & vbcrlf '--- PAGING 3 NAV BAR ---- QSVALDN = Instr(Request.QueryString,"&pg") QSVALDN1 = Instr(Request.QueryString,"&action=results") WITHOUTACTION = Left(Request.QueryString,QSVALDN1) WITHOUTPAGEANDACTION = Left(Left(Request.QueryString,QSVALDN1), QSVALDN) If intPage = 1 Then TRIMEDQS = Replace(Request.QueryString,"&&", "&") Else End If 'TRIMEDQS = WITHOUTACTION If cInt(intPage) < cInt(intPageCount) Then TRIMEDQS = Replace(Replace(WITHOUTPAGEANDACTION & "&action=results","&&","&"), "&action=results", "") End If If cInt(intPage) = 1 Then TRIMEDQS = Replace(Request.QueryString, "&action=results", "") End If 'Response.Write "DN1 = " & QSVALDN1 & "
" 'Response.Write "DN = " & QSVALDN & "
" 'Response.write Request.QueryString & "
" 'Response.write WITHOUTPAGEANDACTION & "
" ' Check to see if the current page is greater than the first page ' in the recordset. If it is, then add a "Previous" link. If cInt(intPage) > 1 Then Response.Write "<< Prev | " End If ' Check to see if the current page is less than the last page ' in the recordset. If it is, then add a "Next" link. If cInt(intPage) < cInt(intPageCount) Then Response.Write "Next >>" End If '--- /END OF NAV PAGING -- rs.Close Set rs = Nothing End If End If %> <% %>