<%@LANGUAGE="VBSCRIPT"%> <% 'Author: Mostafa Hasheme %> <% on error resume next 'read the form values dim action, pname, pserial, pfeatures, poptions, sql, errmsg action = Trim(Request.Form("Submit")) pname = replace(Trim(Request.Form("pname")),"'","''") pserial = replace(Trim(Request.Form("pserial")),"'","''") pfeatures = replace(Trim(Request.Form("pfeatures")),"'","''") poptions = replace(Trim(Request.Form("poptions")),"'","''") 'check to see if the form is submitted if action <> "" then 'validate the form if pname = "" and pserial = "" and pfeatures = "" and poptions = "" then errmsg = "*You must fill at least one of the fields
" end if 'c stands for criteria 'if there are no errors 'build the dynamic sql statement sql = "select SO.TYPE_DESC, SO.ID, CA.CAT_DESC, CA.CAT_ID, CL.CLASS_DESC, CL.CLASS_ID, PR.PRODUCT_NAME, PR.PRODUCT_SERIAL, PR.PRODUCT_ID from PRODUCTS PR, CATEGORY CA, CLASS CL, SOLUTIONS SO" sqlref = "select SO.TYPE_DESC, SO.ID, CA.CAT_DESC, CA.CAT_ID, CL.CLASS_DESC, CL.CLASS_ID, RE.REF_NAME, RE.REF_SERIAL, RE.REF_ID from REFRIGERATOR RE, CATEGORY CA, CLASS CL, SOLUTIONS SO" sqlacc = "SELECT AC.ACCESSORY_ID, AC.ACCESSORY_NAME, AC.ACCESSORY_SERIAL, CA.CAT_ID, CA.CAT_DESC, CL.CLASS_ID, CL.CLASS_DESC, SO.ID, SO.TYPE_DESC from ACCESSORIES AC, CATEGORY CA, CLASS CL, SOLUTIONS SO" 'sqlacc = "SELECT ACCESSORIES.ACCESSORY_ID, ACCESSORIES.ACCESSORY_NAME, ACCESSORIES.ACCESSORY_SERIAL, CATEGORY.CAT_ID, CATEGORY.CAT_DESC, CLASS.CLASS_ID, CLASS.CLASS_DESC, SOLUTIONS.ID, SOLUTIONS.TYPE_DESC " & _ ' "FROM SOLUTIONS INNER JOIN ((CATEGORY INNER JOIN CLASS ON (CATEGORY.CAT_ID = CLASS.CAT_ID) AND (CATEGORY.CAT_ID = CLASS.CAT_ID)) INNER JOIN ACCESSORIES ON CLASS.CLASS_ID = ACCESSORIES.CLASS_ID) ON (SOLUTIONS.ID = CATEGORY.TYPE_ID) AND (SOLUTIONS.ID = CATEGORY.TYPE_ID) " sqlfur = "SELECT DISTINCT FU.FURNITURE_ID, FU.FURNITURE_NAME, FD.FURNITURE_SERIAL, CA.CAT_ID, CA.CAT_DESC, CL.CLASS_ID, CL.CLASS_DESC, SO.ID, SO.TYPE_DESC from FURNITURES FU, FURNITURE_DETAILS FD, CATEGORY CA, CLASS CL, SOLUTIONS SO" 'sqlfur = "SELECT FURNITURES.FURNITURE_ID, FURNITURES.FURNITURE_NAME, CLASS.CLASS_ID, CLASS.CLASS_DESC, CATEGORY.CAT_ID, CATEGORY.CAT_DESC, SOLUTIONS.ID, SOLUTIONS.TYPE_DESC " & _ ' "FROM SOLUTIONS INNER JOIN ((CATEGORY INNER JOIN CLASS ON (CATEGORY.CAT_ID = CLASS.CAT_ID) AND (CATEGORY.CAT_ID = CLASS.CAT_ID)) INNER JOIN FURNITURES ON CLASS.CLASS_ID = FURNITURES.CLASS_ID) ON (SOLUTIONS.ID = CATEGORY.TYPE_ID) AND (SOLUTIONS.ID = CATEGORY.TYPE_ID) " dim ando dim wherestmt wherestmt = false ando = false if pname <> "" then if wherestmt = false then sql = sql & " where" sqlacc = sqlacc & " where" sqlfur = sqlfur & " where" sqlref = sqlref & " where" wherestmt = true end if sql = sql & " upper(PR.PRODUCT_NAME) " & "like upper('%" & pname & "%')" sqlref = sqlref & " upper(RE.REF_NAME) " & "like upper('%" & pname & "%')" sqlacc = sqlacc & " upper(AC.ACCESSORY_NAME) " & "like upper('%" & pname & "%')" sqlfur = sqlfur & " upper(FU.FURNITURE_NAME) " & "like upper('%" & pname & "%')" ando = true end if if pserial <> "" then if wherestmt = false then sql = sql & " where" sqlacc = sqlacc & " where" sqlref = sqlref & " where" sqlfur = sqlfur & " where" wherestmt = true end if if ando = true then sql = sql & " and" sqlacc = sqlacc & " and" sqlref = sqlref & " and" sqlfur = sqlfur & " and" end if sql = sql & " upper(PR.PRODUCT_SERIAL) like upper('%" & pserial & "%')" sqlref = sqlref & " upper(RE.REF_SERIAL) like upper('%" & pserial & "%')" sqlacc = sqlacc & " upper(AC.ACCESSORY_SERIAL) " & "like upper('%" & pserial & "%')" sqlfur = sqlfur & " upper(FD.FURNITURE_SERIAL) " & "like upper('%" & pserial & "%')" ando = true end if if pfeatures <> "" then if wherestmt = false then sql = sql & " where" sqlacc = sqlacc & " where" sqlfur = sqlfur & " where" sqlref = sqlref & " where" wherestmt = true end if if ando = true then sql = sql & " and" sqlacc = sqlacc & " and" sqlfur = sqlfur & " and" sqlref = sqlref & " and" end if sql = sql & " PR.PRODUCT_FEATURES like '%" & pfeatures & "%'" sqlacc = sqlacc & " AC.ACCESSORY_FEATURES like '%" & pfeatures & "%'" sqlfur = sqlfur & " FU.FURNITURE_DESC like '%" & pfeatures & "%'" sqlref = sqlref & " RE.REF_FEATURES like '%" & pfeatures & "%'" ando = true end if if poptions <> "" then if wherestmt = false then sql = sql & " where" sqlref = sqlref & " where" wherestmt = true end if if ando = true then sql = sql & " and" sqlref = sqlref & " and" end if sql = sql & " PR.PRODUCT_OPTIONS LIKE '%" & poptions & "%'" sqlref = sqlref & " RE.REF_OPTIONS LIKE '%" & poptions & "%'" ando = true end if if wherestmt = false then sql = sql & " where" sqlacc = sqlacc & " where" sqlfur = sqlfur & " where" sqlref = sqlref & " where" end if if ando = true then sql = sql & " and" sqlacc = sqlacc & " and" sqlfur = sqlfur & " and" sqlref = sqlref & " and" end if sql = sql & " PR.CLASS_ID = CL.CLASS_ID AND CL.CAT_ID = CA.CAT_ID AND CA.TYPE_ID = SO.ID" sqlacc = sqlacc & " AC.CLASS_ID = CL.CLASS_ID AND CL.CAT_ID = CA.CAT_ID AND CA.TYPE_ID = SO.ID" sqlfur = sqlfur & " FD.FURNITURE_ID = FU.FURNITURE_ID AND FU.CLASS_ID = CL.CLASS_ID AND CL.CAT_ID = CA.CAT_ID AND CA.TYPE_ID = SO.ID" sqlref = sqlref & " RE.CLASS_ID = CL.CLASS_ID AND CL.CAT_ID = CA.CAT_ID AND CA.TYPE_ID = SO.ID" 'Response.Write(sql & "
" & sqlacc & "
" & sqlfur & "
" & sqlref ) 'response.Write(sql) end if %> CSI Jewett - Morgue, Autopsy&Laboratory equipment
you are here home » search
Search our products:  
Product Name
Product Serial
Product Features
Product Options


<% if Request.Form("Submit") <> "" then %> <% Dim flag1 dim flag2 dim flag3 dim flag4 flag1 = false flag2 = false flag3 = false Dim oConn Dim rsResultSet Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open (MM_csi_con_STRING) Set rsResultSet = oConn.Execute(sql) if rsResultSet.eof then flag1 = true end if %> <% while(not rsResultSet.eof) %> <% rsResultSet.MoveNext wend Set rsResultSet = oConn.Execute(sqlacc) if rsResultSet.eof then flag2 = true end if while(not rsResultSet.eof) %> <% rsResultSet.MoveNext wend Set rsResultSet = oConn.Execute(sqlfur) if rsResultSet.eof then flag3 = true end if while(not rsResultSet.eof) %> <% rsResultSet.MoveNext wend Set rsResultSet = oConn.Execute(sqlref) if rsResultSet.eof then flag4 = true end if while(not rsResultSet.eof) %> <% rsResultSet.MoveNext wend %> <%if flag1 and flag2 and flag3 and flag4 then %> <% end if rsResultSet.close oConn.Close Set rsResultSet = Nothing Set oConn = Nothing %>
Solution Category Class Product Name Product Serial  
<%= rsResultSet("TYPE_DESC") %>  <%= rsResultSet("CAT_DESC") %>  <%= rsResultSet("CLASS_DESC") %>  <%= rsResultSet("PRODUCT_NAME") %>  <%= rsResultSet("PRODUCT_SERIAL") %>  &catagory=<%= rsResultSet("CAT_DESC") %>&pname=<%= rsResultSet("PRODUCT_NAME") %>&curSolution=<%= rsResultSet("ID") %>&curCat=<%= rsResultSet("CAT_ID") %>&curClass=<%= rsResultSet("CLASS_ID") %>">more info
<%= rsResultSet("TYPE_DESC") %>  <%= rsResultSet("CAT_DESC") %>  <%= rsResultSet("CLASS_DESC") %>  <%= rsResultSet("ACCESSORY_NAME") %>  <%= rsResultSet("ACCESSORY_SERIAL") %>  &catagory=<%= rsResultSet("CAT_DESC") %>&pname=<%= rsResultSet("ACCESSORY _NAME") %>&curSolution=<%= rsResultSet("ID") %>&curCat=<%= rsResultSet("CAT_ID") %>&curClass=<%= rsResultSet("CLASS_ID") %>">more info
<%= rsResultSet("TYPE_DESC") %>  <%= rsResultSet("CAT_DESC") %>  <%= rsResultSet("CLASS_DESC") %>  <%= rsResultSet("FURNITURE_NAME") %>  <%= Replace(rsResultSet("FURNITURE_SERIAL"),",","
") %> 		 &catagory=<%= rsResultSet("CAT_DESC") %>&pname=<%= rsResultSet("FURNITURE_NAME") %>&curSolution=<%= rsResultSet("ID") %>&curCat=<%= rsResultSet("CAT_ID") %>&curClass=<%= rsResultSet("CLASS_ID") %>">more info
<%= rsResultSet("TYPE_DESC") %>  <%= rsResultSet("CAT_DESC") %>  <%= rsResultSet("CLASS_DESC") %>  <%= rsResultSet("REF_NAME") %>  <%= "" %>  &catagory=<%= rsResultSet("CAT_DESC") %>&pname=<%= rsResultSet("REF_NAME") %>&curSolution=<%= rsResultSet("ID") %>&curCat=<%= rsResultSet("CAT_ID") %>&curClass=<%= rsResultSet("CLASS_ID") %>">more info
No results found in the database. Please try again.
<% end if %>