Gary Carpenter: Works

<%@language=VBScript%> <% dim oRS, results, crit, howmany, sqltext, search_crit ' create a pointer to an object 'set crit to be the search form field criteria sqltext = "SELECT w.id, w.release_date, w.title, w.publisher, w.genre, g.genre_desc " & _ "FROM works w, genres g, works_genre wg WHERE status = 'Y'" & _ "AND w.id = wg.works_id " & _ "AND wg.genre_id = g.genre_id " crit = Request.Form("title_name") ' first bit of criteria If crit <> "" Then sqltext = sqltext & " AND lcase(title) like '%"& lcase(crit) &"%'" search_crit = search_crit & "" & lcase(crit) & " in title," End If crit = Request.Form("pub") ' second bit of criteria If crit <> "" Then sqltext = sqltext & " AND lcase(publisher) like '%"& lcase(crit) &"%'" search_crit = search_crit & "" &lcase(crit) & " in publisher," End If crit = Request.Form("gen") ' second bit of criteria If crit <> "-99" and len(gen) > cint(0) Then sqltext = sqltext & " AND wg.genre_id = " & crit End If sqltext = sqltext & " order by genre_desc, title" ' Response.write sqltext ' Get the search results Set oConn = Server.CreateObject("ADODB.Connection") strConn = "Driver={MySQL}; DATABASE=garycarpenterdb; UID=garycarpenter; PASSWORD=garyin2db;" oConn.Open strConn Set oRS=Server.CreateObject("ADODB.RecordSet") 'create the object oRS.ActiveConnection = oConn oRS.open sqltext, oConn, 2 ' perform the quer %> Gary Carpenter: Works

Works

<% If howmany > 0 Then %> <% oRs.MoveFirst dim last last = 9999 Do While NOT oRS.EOF if oRS("id") <> last then Response.Write "" Response.Write "" Response.Write "" Response.Write "" end if last = ors("id") Response.Write "" oRS.moveNext Loop %> <%else%> <% End If %>

<% howmany = 0 do while not oRs.EOF howmany = howmany + 1 oRS.MoveNext loop if len(search_crit) = 0 and Request.Form("gen") = "-99" then response.write "Full Listing" else if howmany > 0 then if Request.Form("gen") <> "-99" then ors.MoveFirst response.write "
Displaying the " & oRS("genre_desc") & " genre." end if if len(search_crit) > 0 then Response.Write "
Search criteria: " if right(search_crit,1) = "," then search_crit = left(search_crit,len(search_crit)-1) end if response.write search_crit end if end if end if %>

Release Date Title Genre
" & oRS("release_date") & " " & oRS("title") & " " & oRS("genre") & "
No Results

new search >>

<% oRS.Close %>