Easy Database Add and View @ werDsWeb.com

A simple Database Add and View Using ASP

Three Seperate Files below you will also need a copy of adovbs.inc and a Database... mine is titled "db3.mdb" You'll need a slight understanding about ASP and HTML to adjust the variables to your needs but hey its a start.

Tip: You can copy the text below drop it into to notepad and choose save as "All Files" and add the .asp or .htm extention and its an instant HTML Editor.

Ive got some good tutorial links from my main page @ www.werdsweb.com

1.A Form To Add data to your Microsoft Access Database

This is where your data comes from, or what you want to see. The fields below can be modified to add more fields or more specific data if necessarry just be sure to have corresponding fields in your database named correctly.

Tip: If you need more help with form inputs try this link

Form.htm or whatever

Put this form in the same directory as your other pages

-------------Start of Page-----------------

<Form action="addnow.asp" method="post">
<p><strong>Date</strong> <input type="text" name="date_"> </p> 'Add input fields here~ remember what you name them
<p><strong>Your Message </strong></p>
<p><Textarea name="Message" cols="70" rows="15"></textarea>
<input type="submit" name="sub" value="Add Record">
</p></form>

</BODY></HTML>

2. A DSN-less Database Connection To Add data

Here's the database connection. By addng the action "addnow.asp" to the above form we are telling the above page to send the data posted by the form's input fields(whether visible or hidden) to the page we designate, in this case addnow.asp

------------Start of page----------------

<HTML><HEAD><TITLE>Record Added!</TITLE></HEAD><BODY>

<%@Language = "VbScript"%>
 'youll need this file
<%
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("db3.mdb")

Set ObjRs=Server.CreateObject("ADODB.Recordset")
ObjRs.Open "MessageTable", strConnect, adOpenKeySet,adLockOptimistic,adCmdTable
ObjRs.AddNew
ObjRs("Date_")=Request.form("Date_") 'This is where you can add the fields you want pulled from the form
ObjRs("Message")=Request.form("Message")
ObjRs.Update
ObjRs.Close
Set ObjRs=Nothing

Response.write "<center><b>Records Added</b></center>"
%>

<a href="viewdatabase.asp">View Results</a>

</BODY></HTML>

3. View The Database Data and order it by date submitted

The following page will display the data from your database in a table ordered by newest date first

Name this one viewdatabase.asp

--------------Start of Page---------------

<HTML><HEAD><Title>Daily Messgaes</Title></HEAD><BODY>

<%@Language = "VbScript"%>

<%
dim objConn,objRec
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open "Data Source=" & Server.Mappath("../databasefolder/db3.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"

Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "SELECT [Date_], [Message] FROM MessageTable Order By [Date_] DESC",objConn,adOpenKeyset,adLockOptimistic, adCmdText
%>

<TABLE WIDTH=98%><TR><TD BGCOLOR=#C0C0C0 COLSPAN=6 ALIGN=CENTER> <FONT SIZE="+1"><b>Daily Messages </b></FONT></TD></TR><TR>
<TD width="20%" COLSPAN=1 ALIGN=CENTER BGCOLOR=#0033CC><em><strong><font color="#FFFFFF" face="Georgia, Times New Roman, Times, serif">Date</font></strong></em></TD>
<TD width="80%" ALIGN=CENTER BGCOLOR=#FFFFFF><div align="left"></div></TD></TR>

<%
While NOT objRec.EOF
Response.Write "<tr><td BGCOLOR=""#0080C0"">" & objRec("Date_") & "</td>"
Response.Write "<td>" & objRec("Message") & "</td></tr>"
objRec.MoveNext
Wend
%>

</TABLE></BODY></HTML>

~Searching Your Database

If youre wanting to display the data... based on the requested date from a form you'll need to change your view page to something along these lines. For this you will need a page with a post form that includes an input named "Date" and the action will need to be directed to this page.

The example below uses the form input "Date" to decide what items will be displayed. ie. Want to search for a message by date

-------------Start of Page--------------

<HTML><HEAD><TITLE>Search Result</TITLE></HEAD><BODY>

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Data Source=" & Server.Mappath("../db3.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"

Input = Request.Form("Date")
Set rs = Conn.Execute("SELECT [Message], [_Date] FROM MessageTable WHERE [_Date] = " & Input)
%>

The Daily Message For <%= rs("_Date")%>was<strong>"<%= rs("Message")%>"</strong>

</BODY></HTML>

werD