Lunarpages Web Hosting Forum

Author Topic: Simple database search results with ASP and SQL  (Read 11581 times)

Offline hw15

  • Spacescooter Operator
  • *****
  • Posts: 36
    • Internet ArchiTECH
Simple database search results with ASP and SQL
« on: July 12, 2008, 01:07:25 PM »
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 if


5. 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.

 :yep:
« Last Edit: July 13, 2008, 01:46:15 PM by hw15 »
Web development and consulting by Internet ArchiTECH.

Offline Mitch

  • Berserker Poster
  • *****
  • Posts: 12625
    • MitchKeeler.com
Re: Simple database search results with ASP and SQL
« Reply #1 on: July 14, 2008, 04:51:07 AM »
Very cool, thanks!   ;D
New to Web Site Hosting? Check Out the Lunarpages Blog Hosting Guide!


Follow us @lunarpages on Twitter!
Important Threads: Read This Before Posting! | Lunarforums Rules! | Mitch's Link of the Day!
Also, be sure to check out and subscribe to the Lunartics Blog and the Lunarpages Newsletter !

Need Web Hosting Help? Check out the Lunarpages Web Hosting Wiki. It has tons of tips, tutorials and resources!

Offline Claudster

  • Intergalactic Cowboy
  • *****
  • Posts: 55
  • Hey guys! I'm a Web manager for Transport Canada.
Re: Simple database search results with ASP and SQL
« Reply #2 on: March 03, 2009, 04:05:04 PM »
This is great. Thanks a lot man. Currently in the mood for refreshers as I'm spending a lot more time managing my team than doing actual hands-on work.
Claudster