Visual Basic Tutorials

Welcome to Dream.In.Code
	Getting Help is Easy! Join 132,445 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,472 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Search and connect through the database [ODBC]

Search and connect through the database [ODBC], ASP connecting to a database

Options

Rating

eXceed69

30 Nov, 2006 - 02:34 AM

Post #1

Searching a database is actually a easy one. It just what i done in some tutorials
It just involves a form, and a page that displays the results.

note:
I creating my search to a simple form

Steps in searching through out the database
1. First we had to define variables
2. Searxh field
3. A database connection
4. The search and opening the data
5. Display the outputed search

Firstly to process my example, I want show that there is a form, so what i wanna do is to create a form.Below are the exmple code of the form

CODE

<table>
<tr>
<form method="POST" action="search_results.asp">
<td>Search: <input type="text" name="txtSearch" size="50"></td>
</tr>
<tr>
<td><input type="submit" value="Search"></td>
</tr>
</table>

Then we could proceed to the search

1. Define variables

CODE

           <%
                Dim strInputSearch    'Variable for the search word
                Dim strCon                'connect to the database
                Dim adoCon               'Database Connection Variable Object
                Dim strSQL                'SQL query for the database
                Dim rsSearch             'search recordset storage

2. Search field

This would be variable that has the search word

CODE

strInputSearch = Request.Form("txtSearch")

'This makes it so people cant inject SQL code and/or cause some unwanted errors

CODE

strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)

3. Database Connection

This is setting-up the connection

CODE

Set adoCon = Server.CreateObject("ADODB.Connection")

This is that hold the connection string

CODE

strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("database.mdb")

Open the connection for the database

CODE

adoCon.Open strCon

4. Opening the search from the datase

Set the database connection

CODE

Set rsSearch = Server.CreateObject("ADODB.Recordset")

This is the SQL statement for searching.

CODE

strSQL = "SELECT tblTable.* FROM tblTable WHERE tblTable.Field LIKE '%" & strInputSearch & "%';"

Opens the database so we can get the results of the search

CODE

rsSearch.Open strSQL, adoCon

5. And then we had to display our search by means of this

'If there are no matches then continue

CODE

If rsSearch.EOF Then

   'Write out the message that there are no matches
    Response.Write("There are no matches with your search")

'If there are matches, continue
Else

    'Loop through the database to display all the matches
    DO UNTIL rsSearch.EOF

        'Write out the match found. You need to change Field to your database field you are search (its in red)
        Response.Write(rsSearch("Field") & "<br>")

    'Move to next line in database
    rsSearch.MoveNext
    'Continue looping through database to display all results found
    Loop
End If

note: as we open the database we had to corresponding action to do to close the database

here's the code

CODE

'Reset server objects
Set rsSearch = Nothing
adoCon.Close
Set adoCon = Nothing
%>

Want to add thing's as your reference, I search for more example for you.Take a sneek on it:
ADO Conneciton String
How to access SQL Server in ASP
ASP.NET connection

This post has been edited by eXceed69: 5 Dec, 2006 - 07:37 PM