<% '******************************************************* '* ASP 101 Sample Code - http://www.asp101.com/ * '* * '* This code is made available as a service to our * '* visitors and is provided strictly for the * '* purpose of illustration. * '* * '* http://www.asp101.com/samples/license.asp * '* * '* Please direct all inquiries to webmaster@asp101.com * '******************************************************* %> <% ' Declare our variables... always good practice! Dim strURL ' The URL of this page so the form will return to the same page after submission ' no matter what this file is named. Dim cnnSearch ' ADO connection Dim rstSearch ' ADO recordset Dim strSQL ' The SQL Query we build on the fly Dim strSearch ' The text being looked for Dim strChoice 'Radio button choice value (Tyes or Ayes) Dim strRadio 'Get value of strChoice after submission of form ' Retreive the URL of this page from Server Variables strURL = Request.ServerVariables("URL") ' Retreive the term being searched for. I'm doing it on ' the QS since that allows people to bookmark results. ' You could just as easily have used the form collection (QueryString used with "Get" form method). strSearch = Request.QueryString("search") ' Since I'm doing this all in one page I need to see if anyone ' has searched for something. If they have we hit the DB. ' O/W I just show the search form and quit. %>

Search our Karaoke database by choosing type of search, then enter any part of a song title or artist name:

> Title Search > Artist Search

If no match is found, we still may have it. Please call us at (858) 481-6400.

<% strRadio=Request("strChoice") 'Get the radio button choice and redefine it. If strRadio="Tyes" then 'You are here if radio button "Title Search" was chosen. Response.Write("

Title Search shown below:

") If strSearch <> "" Then ' MapPath of virtual database file path to a physical path. ' If you want you could hard code a physical path here. ' Create an ADO Connection to connect to the database. ' We're using OLE DB but you could just as easily use ODBC or a DSN. Set cnnSearch = Server.CreateObject("ADODB.Connection") ' We're using SQL Server so we use this line instead: cnnSearch.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=willow.safesecureweb.com; PORT=3306; DATABASE=alpha; USER=alpha; PASSWORD=Alphadata1; OPTION=3;" ' Build our query based on the input. ' NOTE: DB FIELDS MUST USE ACCENT (`IN STOCK`), WHEREAS SEARCH VARIABLES USE APOSTROPHE ('%xx%')!! strSQL = "SELECT `Stock No`, `Media Title`, `Price`" _ & ", `SONG1 TITLE`" _ & ", `SONG1 ARTIST`" _ & ", `SONG2 TITLE`" _ & ", `SONG2 ARTIST`" _ & ", `SONG3 TITLE`" _ & ", `SONG3 ARTIST`" _ & ", `SONG4 TITLE`" _ & ", `SONG4 ARTIST`" _ & ", `SONG5 TITLE`" _ & ", `SONG5 ARTIST`" _ & ", `SONG6 TITLE`" _ & ", `SONG6 ARTIST`" _ & ", `SONG7 TITLE`" _ & ", `SONG7 ARTIST`" _ & ", `SONG8 TITLE`" _ & ", `SONG8 ARTIST`" _ & ", `SONG9 TITLE`" _ & ", `SONG9 ARTIST`" _ & ", `SONG10 TITLE`" _ & ", `SONG10 ARTIST`" _ & ", `SONG11 TITLE`" _ & ", `SONG11 ARTIST`" _ & ", `SONG12 TITLE`" _ & ", `SONG12 ARTIST`" _ & ", `SONG13 TITLE`" _ & ", `SONG13 ARTIST`" _ & ", `SONG14 TITLE`" _ & ", `SONG14 ARTIST`" _ & ", `SONG15 TITLE`" _ & ", `SONG15 ARTIST`" _ & ", `SONG16 TITLE`" _ & ", `SONG16 ARTIST`" _ & ", `SONG17 TITLE`" _ & ", `SONG17 ARTIST`" _ & ", `SONG18 TITLE`" _ & ", `SONG18 ARTIST`" _ & ", `SONG19 TITLE`" _ & ", `SONG19 ARTIST`" _ & ", `SONG20 TITLE`" _ & ", `SONG20 ARTIST`" _ & ", `SONG21 TITLE`" _ & ", `SONG21 ARTIST`" _ & "FROM ALPHALIST " _ & "WHERE `MEDIA TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG1 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG2 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG3 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG4 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG5 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG6 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG7 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG8 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG9 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG10 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG11 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG12 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG13 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG14 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG15 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG16 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG17 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG18 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG19 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG20 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG21 TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "ORDER BY `STOCK No`;" ' Execute our query using the connection object. It automatically ' creates and returns a recordset which we store in our variable. Set rstSearch = cnnSearch.Execute(strSQL) ' Display a table of the data in the recordset. We loop through the ' recordset displaying the fields from the table and using MoveNext ' to increment to the next record. We stop when we reach EOF. ' For fun I'm combining some fields and showing you can do more then ' just spit out the data in the form it is in in the table. ' Response.Write(strSQL) 'Return the full SQL Query String (for debug only) '--- Write results of query in a self-expanding table --- Response.Write("") if rstSearch.State = 1 then 'if the recordset has rows show the column names Response.Write("") for each f in rstSearch.Fields Response.Write("") next Response.Write("") 'show the rows do while not rstSearch.EOF Response.Write("") for each f in rstSearch.Fields Response.Write("") next Response.Write("") rstSearch.MoveNext loop else 'DML was performed Response.Write("") end if Response.Write("
" & f.Name & "
" & f.Value & "
Command Completed Successfully
") '--- End of Write results of query in a self-expanding table --- End If Else If strRadio="Ayes" then 'You are here if radio button "Artist Search" was chosen. Response.Write("

Artist Search shown below:

") If strSearch <> "" Then ' MapPath of virtual database file path to a physical path. ' If you want you could hard code a physical path here. ' Create an ADO Connection to connect to the database. ' We're using OLE DB but you could just as easily use ODBC or a DSN. Set cnnSearch = Server.CreateObject("ADODB.Connection") ' We're using SQL Server so we use this line instead: cnnSearch.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=willow.safesecureweb.com; PORT=3306; DATABASE=alpha; USER=alpha; PASSWORD=Alphadata1; OPTION=3;" ' Build our query based on the input. ' NOTE: DB FIELDS MUST USE ACCENT (`IN STOCK`), WHEREAS SEARCH VARIABLES USE APOSTROPHE ('%xx%')!! strSQL = "SELECT `Stock No`, `Media Title`, `Price`" _ & ", `SONG1 TITLE`" _ & ", `SONG1 ARTIST`" _ & ", `SONG2 TITLE`" _ & ", `SONG2 ARTIST`" _ & ", `SONG3 TITLE`" _ & ", `SONG3 ARTIST`" _ & ", `SONG4 TITLE`" _ & ", `SONG4 ARTIST`" _ & ", `SONG5 TITLE`" _ & ", `SONG5 ARTIST`" _ & ", `SONG6 TITLE`" _ & ", `SONG6 ARTIST`" _ & ", `SONG7 TITLE`" _ & ", `SONG7 ARTIST`" _ & ", `SONG8 TITLE`" _ & ", `SONG8 ARTIST`" _ & ", `SONG9 TITLE`" _ & ", `SONG9 ARTIST`" _ & ", `SONG10 TITLE`" _ & ", `SONG10 ARTIST`" _ & ", `SONG11 TITLE`" _ & ", `SONG11 ARTIST`" _ & ", `SONG12 TITLE`" _ & ", `SONG12 ARTIST`" _ & ", `SONG13 TITLE`" _ & ", `SONG13 ARTIST`" _ & ", `SONG14 TITLE`" _ & ", `SONG14 ARTIST`" _ & ", `SONG15 TITLE`" _ & ", `SONG15 ARTIST`" _ & ", `SONG16 TITLE`" _ & ", `SONG16 ARTIST`" _ & ", `SONG17 TITLE`" _ & ", `SONG17 ARTIST`" _ & ", `SONG18 TITLE`" _ & ", `SONG18 ARTIST`" _ & ", `SONG19 TITLE`" _ & ", `SONG19 ARTIST`" _ & ", `SONG20 TITLE`" _ & ", `SONG20 ARTIST`" _ & ", `SONG21 TITLE`" _ & ", `SONG21 ARTIST`" _ & "FROM ALPHALIST " _ & "WHERE `MEDIA TITLE` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG1 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG2 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG3 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG4 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG5 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG6 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG7 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG8 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG9 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG10 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG11 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG12 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG13 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG14 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG15 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG16 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG17 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG18 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG19 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG20 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR `SONG21 ARTIST` LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "ORDER BY `STOCK No`;" ' Execute our query using the connection object. It automatically ' creates and returns a recordset which we store in our variable. Set rstSearch = cnnSearch.Execute(strSQL) ' Display a table of the data in the recordset. We loop through the ' recordset displaying the fields from the table and using MoveNext ' to increment to the next record. We stop when we reach EOF. ' For fun I'm combining some fields and showing you can do more then ' just spit out the data in the form it is in in the table. ' Response.Write(strSQL) 'Return the full SQL Query String (for debug only) '--- Write results of query in a self-expanding table --- Response.Write("") if rstSearch.State = 1 then 'if the recordset has rows show the column names Response.Write("") for each f in rstSearch.Fields Response.Write("") next Response.Write("") 'show the rows do while not rstSearch.EOF Response.Write("") for each f in rstSearch.Fields Response.Write("") next Response.Write("") rstSearch.MoveNext loop else 'DML was performed Response.Write("") end if Response.Write("
" & f.Name & "
" & f.Value & "
Command Completed Successfully
") '--- End of Write results of query in a self-expanding table --- End If Else End If End If ' Close our recordset and connection and dispose of the objects rstSearch.Close Set rstSearch = Nothing cnnSearch.Close Set cnnSearch = Nothing %>