Okay, well the first thing I've tried to do so far is allow users to add a record to the database...it seemed easy at first, but now I think I've run into a bit of a wall. I'm using the Add Record tutorial from w3schools.com (linky link (http://www.w3schools.com/ado/ado_add.asp)), and so far here's what my two pages look like:
page1.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Testing ASP with SQL</title>
</head>
<body bgcolor="#a9a9a9">
<br /><br /><br />
<p align="center">ASP + SQL</p>
<br /><br /><br />
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("mytestdb.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT fname, lname, studentnum, bod FROM student", conn
%>
<table border="1" width="100%">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Student Number</th>
<th>Date of Birth</th>
</tr>
<%
do until rs.EOF
Response.write("<tr><td>" & rs.fields(0) & "</td><td>" & rs.Fields(1) & "</td><td>" & rs.Fields(2) & "</td><td>" & rs.Fields(3) & "</td></tr>")
rs.MoveNext
loop
rs.close
conn.close
%>
</table>
<br /><br /><br />
<strong>Add a new record to the database:</strong><br /><br />
<form name="addRecord" action="page2add.asp" method="post">
<table border="0" width="25%">
<tr>
<th>Field</th>
<th>Data </th>
</tr>
<tr>
<td>First Name</td>
<td><input type="text" name="firstname" /></td>
</tr>
<tr>
<td>Last Name</td>
<td><input type="text" name="lastname" /></td>
</tr>
<tr>
<td>Student Number</td>
<td><input type="text" name="studentnumber" /></td>
</tr>
<tr>
<td>Date of Birth</td>
<td><input type="text" name="dateofbirth" /></td>
</tr>
</table>
<input type="submit" value="Add This Record!" />
<input type="reset" value="Clear" />
</form>
</body>
</html>
page2add.asp:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>(ADD RECORD)</title>
</head>
<body bgcolor="#a9a9a9">
<br /><br /><br />
<p align="center">ASP + SQL</p>
<br /><br /><br />
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("mytestdb.mdb"))
sql="INSERT INTO student (fname,lname,"
sql=sql & "studentnum,dob)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("firstname") & "',"
sql=sql & "'" & Request.Form("lastname") & "',"
sql=sql & "'" & Request.Form("studentnumber") & "',"
sql=sql & "'" & Request.Form("dateofbirth") & "')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>
</table>
</body>
</html>
Three things:
1) I noticed on the W3 tutorial they say that if the table contains a primary key, you need to append an incrementing number for this field for each record added...my database has a primary key column, but it is the default ID column that was created when I made the table (there is a studentnum column, but these are random, non-sequential numbers and therefore not ideal for a PK column). So I'm not exactly sure if that's something I'll need to do for this to work or not, I'm hoping someone can tell me if that is the case, and if so, how I would do it.
2) I'm using a total of 4 columns: 2 are text (fname, lname), 1 is integer (studentnum), and the other is date/time (dob). It occurs to me that for users adding a new record, the data they input for the student number and date of birth fields must be somehow converted to that datatype, correct? Can someone tell me or show me how this is done?
3) I'd also like to implement some function that will check to validate the data in the textbox fields...in other words, if they are empty (ie user left it blank by accident), the wrong data type (ie text where it should be an integer), or invalid data/format (ie invalid date format) display a popup alert containing a message with such info (so they can fix it).
I'd also like to point out that with the two pages I've posted above, when I run the code myself and try to add a record, even though I have not yet implemented any of the above things I've mentioned, it gives me an error on the submit page that says "No update permissions!"...I realize the code I've used from the W3 tutorial is what causes this message to be displayed, but I'm wondering how I fix whatever permissions issue exists in order to be able to at least write to the database? Thanks for the help! :D