Programmers Resource - Step by Step: Database Tutorial

08-05-2004, 01:25 AM

admin

Administrator

Join Date: May 2003

Posts: 116

Step by Step: Database Tutorial

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

Fig. 2

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

Fig. 3

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

Fig. 4

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

Fig. 5

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

Fig. 6

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

Fig. 7

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.

Code:


<%@ 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.

Code:


  <%@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!