Demo Website for Clubs

Clubs Demo Website

This application is a sample on how to add, delete and retrieve the records from an Access database using ASP.
The following extract the code used for the different functions in the application.

1. Opening a database connection

         ' declaring variables
         ' not neccesary but a good habit
         dim conn
         dim rs
         dim sql
         dim inputtxt
         dim datetxt
         dim resultcode
    
         ' create ADODB Server Object for connection
         set conn = Server.CreateObject("ADODB.Connection")
         conn.Provider="Microsoft.Jet.OLEDB.4.0"         
         conn.Open(Server.Mappath("_db/demo.mdb"))

         ' create ADODB Server Object for record set
         set rs = Server.CreateObject("ADODB.Recordset")

2. Retrieving records from the database

         ' prepare sql statement
         sql = "SELECT * FROM Info Order by id desc"
         rs.Open sql, conn
       
         ' iterate thru the each record in the record set
         While Not rs.EOF

            <tr class="displaytr">
               <td width="5%"> <%=rs("id")%></td>
               <td width="20%"> <%=rs("name")%></td>
               <td width="25%"> <%=rs("remarks")%></td>
               <td width="25%"> <%=rs("entrytime")%></td>
               <td width="5%"> <input type="button" value="Del" onclick=javascript:deleteRec(<%= rs("id") %>)></td>
            </tr>

         ' the following code ends the loop
         rs.MoveNext
         wend

3. Adding a record to the database

         ' if no input text in name, then  skip this section
         inputtxt = Request.Form("nametxt")
         datetxt = date() + time()
         if inputtxt<>"" then
            ' prepare sql statement
            sql = "INSERT INTO Info(Name, Remarks, Entrytime) VALUES "
            sql = sql & "('" & Request.Form("nametxt") & "',"
            sql = sql & "'" & Request.Form("remarkstxt") & "',"
            sql = sql & "'" & datetxt & "')"

            ' resume next when error encountered
            on error resume next

            ' execute sql statement
            conn.execute sql, resultcode

            ' print insert status
            if err<>0 then
               response.write("Record is not updated.")
            end if
         end if

4. Deleting a record from the database

         ' if no text in delid hidden field, then skip this section
         inputtxt = Request.Form("delid")
         if inputtxt<>"" then
            ' prepare sql statement
            sql = "DELETE FROM Info WHERE id="
            sql = sql & inputtxt
 
            ' resume next when error encountered
            on error resume next

            ' execute sql statement
            conn.execute sql, resultcode

            ' print insert status
            if err<>0 then
               response.write("Deletion is unsuccessful.")
            end if
         end if

5. Closing the database connection

         ' closing objects and setting them to nothing
         rs.close
         set rs = Nothing
         conn.close
         Set conn = Nothing

Click here for complete code.