Hello, just wanted to offer a refresher for anyone who wants to do a simple database search form and results page with ASP and SQL.
To connect to a SQL database with classic ASP on Lunarpages, please see my other post here:
http://www.lunarforums.com/lunarpages_asp/asp_connection_string_for_sql_server-t48727.0.html;msg332856#msg332856 .
You have a table setup as follows:
TableName: myTable
ColumnName: myColumn
Datatype: varchar
You have sample data entered into the table:
Row 1: Hello World!
Row 2: John Doe
Row 3: Rock and roll
Row 4: Rockstar
1. First you need to make a page for the search form. Using your favorite web page editor like Dreamweaver, place the following code on a blank page called mySearchForm.asp and upload it to the server via FTP.
<form id="form1" name="form1" method="get" action="mySearchResults.asp">
<label>Enter Keywords:
<input type="text" name="searchTerm" />
</label>
<p>
<input type="submit" name="Submit" value="Submit" />
</p>
</form>2. Next you need to make a page for the search results. Place the following code on a blank page called mySearchResults.asp and upload it to the server via FTP.
<%
'open the connection
Dim Connect, myRecordSet
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open = "Provider=MSDASQL;Driver={SQL Server};Server=209.200.235.3;Database=myDatabase;Uid=myUser;Pwd=myPassword;"
%>
<%
'collect the form input
searchInput = Request.QueryString("searchTerm")
'check for a match
Set myRecordSet = Connect.Execute ("SELECT * FROM dbo.myTable WHERE myColumn LIKE '%" & searchInput & "%'")
'display the results
if myRecordSet.EOF then
response.write("You searched for: " & searchInput & "<br>")
response.write("A match was not found.<br>Sorry try again.")
else
response.write("You searched for: " & searchInput & "<br>")
response.write("The record was found!<br>The match is: " & myRecordSet("myColumn"))
end if
%>
<br><br>
<a href="mySearchForm.asp">Try Again</a>3. To test it out, go to
www.myDomainName.com/mySearchPage.asp . Type the word hello into the search form and click submit. The output displayed will be:
You searched for: hello
A record was found!
The match is: Hello world!
The above example will search the database for any records that contain the search term because we used the SQL command LIKE with % signs wrapped around the search term. That is why typing in hello will return the match Hello World!
4. As you may have noticed in the above example, only the first matching record will be displayed. If there are multiple matches, you may want to list all the matching records. To do this you need to add a loop to the display results, as follows.
'display the results
if myRecordSet.EOF then
response.write("You searched for: " & searchInput & "<br>")
response.write("A match was not found.<br>Sorry try again.")
else
response.write("You searched for: " & searchInput & "<br>")
response.write("Records were found!<br>The matches are:<br>")
do until myRecordSet.EOF
response.write(myRecordSet("myColumn") & "<br>")
myRecordSet.MoveNext
loop
end if5. Test it out again by typing the word rock into the search form. Two matches will be displayed, as follows:
You searched for: rock
Records were found!
The matches are:
rock and roll
rockstar
All records that contain the word rock will be displayed.
6. If you only want to find an exact match, modify the SELECT statement, as follows:
("SELECT * FROM dbo.myTable WHERE test = '" & searchInput & "'")Test it out again by typing the word rock into the search form. No matches will be found because it is searching for an exact match. Type in rockstar and the exact match for rockstar will be found.
Warning: databases are highly vulnerable to hackers. The SQL injection attacks and XSS (cross-site scripting) attacks are running rampant. So please ensure you have setup custom error pages and use ASP input validation as a precaution to ensure database security. For more info, check out:
http://www.ehow.com/how_4434719_protect-website-hacker-attacks.html .
Hope you may find this useful.
