Step by Step: Database Tutorial

[admin]

Note:
There are several ways to connect to your database. Below we show you how to create a System DSN. We'll also show you the alternate methods to connect to your database.

Create a new empty database. Build your tables as shown in Fig. 1 and Fig. 2. Save your database.

Fig. 1
http://programmersresource.com/forum/images/article_images/tblCustomer.gif

Fig. 2
http://programmersresource.com/forum/images/article_images/tblUser.gif

Now open up Control Panel and open up ODBC Data Sources (see Fig. 3)

Fig. 3
http://programmersresource.com/forum/images/article_images/odbc0.gif

Select System DSN and then click Add. (see Fig. 4)

Fig. 4
http://programmersresource.com/forum/images/article_images/odbc1.gif

Select the Microsoft Access Driver and click Finish. (see Fig. 5)

Fig. 5
http://programmersresource.com/forum/images/article_images/odbc2.gif

Type in your Data Source Name (DSN). Then click on Select. (see Fig. 6)

Fig. 6
http://programmersresource.com/forum/images/article_images/odbc3.gif

Find and select your database and then click OK. (see Fig. 7)

Fig. 7
http://programmersresource.com/forum/images/article_images/odbc4.gif

You're now done setting up your DSN.

Now you're ready to create your pages.

First let's create the search page. This page uses a form where one can enter the criteria to search the database. In this example the last name is entered into the form.



<%@ Language=VBScript %>

<center>
<form name="Search" method="Post" action="searchdb.asp">
<input type="text" name="strSearch" size="20">
<input type="submit" name="btnSearch" value="Search">
</form>
</center>

<br>
<br>

<center>Valid names to query are Smith, Jones, or Edwards.</center>
<br>
<center>Try entering a different name or leave the field blank.</center>

'Save the above code as form.asp

Now we create the page that retrieves the data from the database.


<%@Language=VBScript%>
<%Response.Buffer=True%>

<%
'dim your variables
Dim searchStr, MyConn, RS, i

'grab the values entered into the form (from form.asp)
'the Request.Form() method is used to grab the value. The argument is
'the name given to the form field.
'the Replace() method replaces any single apostrophes ( ' )
'with double apostrophes ( '' ). Otherwise, you'll get a nasty error

searchStr = Replace(Request.Form("strSearch"), "'", "''")

'create the connection object
Set MyConn=Server.CreateObject("ADODB.Connection")

'Open the connection to the database
'Note: pick whichever connection method you want to use.
'the first method uses a System DSN, like the one at the beginning
'of this article.
'the second method uses an OLE DB connection... the preferred method.
'and the third method uses a DSN-Less connection
'the first method (System DSN) is set as the default connection

MyConn.Open "getdata"
'MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Databases\demo.mdb"
'MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Databases\demo.mdb"

SQL = "SELECT * FROM tblCustomer, tblUser WHERE tblCustomer.LastName "
SQL = SQL & "LIKE '%"&searchStr&"%' AND tblCustomer.ID = tblUser.ID"

Set RS = MyConn.Execute(SQL)

'as long as a value was entered into the form...
If searchStr <> "" Then

'here we check for both Beginning of file (BOF) And End of file (EOF)
'if both are True then no records were found.
If RS.BOF AND RS.EOF Then
Response.Write "<center> No Records Found.</center>"

'otherwise, create a HTML table and fill it with the search result(s)
Else
Response.Write "<center><table border=""1""><tr>"

'the RS.Fields.Count counts the number of columns in the database.
'the HTML table's headers ( <th> ) are created and the column names
'are entered into them.
For i= 0 to RS.Fields.Count - 1
Response.Write "<th>" & RS(i).Name & "</th>"
Next

Response.Write "</tr>"

'the headers are now completed, now the rest of the HTML table is filled
'with the result(s) of the search.
While Not RS.EOF
Response.Write "<tr>"
For i= 0 to RS.Fields.Count - 1
Response.Write "<td>" & RS(i) & "</td>"
Next
Response.Write "</tr>"

'move to the next record
RS.MoveNext
WEND

'close the HTML table
Response.Write "</table>"
End If

'if the form was left blank then alert the user.
Else
Response.Write "<center>No Search String Entered.</center>"

End IF

'close and destroy all objects created to free up system memory
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>


'Save the above code as searchdb.asp

That's it!