What's Here?

Members: 118,658
Replies: 435,008
Topics: 67,234
Snippets: 2,407
Tutorials: 641
Total Online: 903
Members: 44
Guests: 859

Visual Basic Tutorials

Welcome to Dream.In.Code
	Getting Help is Easy! Join 118,658 Programmers for FREE! Ask your question and get quick answers from experts. There are 903 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now! Chat LIVE With a Expert Register to Make This Box Go Away!

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 - 03: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 - 08:37 PM