Adding new records example by David Lohmann

SEARCH ASP FREE

This form takes all the hard work out of matching up form field names with your database fields when adding new records. It automatically creates a DSN-Less connection and all the ADO commands necessary to add records to an Access database without the need for a Connection Object. As it stands it can accommodate up to 20 fields, but is easily extendable.

<%@language=vbscript%>


<%

dim sent, dbcon, folder, file, rsname, table, csr1, csr2

sent=request.form("sent")

'This code decides whether to show the input form or the results

If sent="true" then

'Set the variables for the input form

dbcon=request.form("T1")
folder=request.form("T2")
file=request.form("T3")
table=request.form("T4")
rsname=request.form("T5")
csr1=request.form("T6")
csr2=request.form("T7")
fld1=request.form("T8")
fld2=request.form("T9")
fld3=request.form("T10")
fld4=request.form("T11")
fld5=request.form("T12")
fld6=request.form("T13")
fld7=request.form("T14")
fld8=request.form("T15")
fld9=request.form("T16")
fld10=request.form("T17")
fld11=request.form("T18")
fld12=request.form("T19")
fld13=request.form("T20")
fld14=request.form("T21")
fld15=request.form("T22")
fld16=request.form("T23")
fld17=request.form("T24")
fld18=request.form("T25")
fld19=request.form("T26")
fld20=request.form("T27")

response.write "<b>This code will add a new record to your database.</b><br>"
response.write "<b>Copy and paste it into your ASP page.</b><p>"
response.write "<%<br>"

' Create your connection
response.write dbcon&" = ""DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=""&Server.mapPath("&""""

' If your database is in a folder, this needs to be put into the path.
' On the form you can indicate several levels e.g. home\mydata\databases

if folder<>"" then
response.write folder&"\"
end if

' The filename of your database
response.write file&".mdb"")<p>"

'If you name your database fields with the same name as your form input fields,
'this next bit takes all the hard work out of writing the correct code.

response.write "Set "&rsname&"=Server.CreateObject("&"""ADODB.Recordset"&""")<br>"
response.write rsname&".Open "&""""&table&""", "&dbcon&", "&csr1&", "&csr2&"<p>"
response.write rsname&".AddNew<br>"
if fld1<>"" then response.write rsname&"("""&fld1&""")=Request.form("""&fld1&""")<br>"
if fld2<>"" then response.write rsname&"("""&fld2&""")=Request.form("""&fld2&""")<br>"
if fld3<>"" then response.write rsname&"("""&fld3&""")=Request.form("""&fld3&""")<br>"
if fld4<>"" then response.write rsname&"("""&fld4&""")=Request.form("""&fld4&""")<br>"
if fld5<>"" then response.write rsname&"("""&fld5&""")=Request.form("""&fld5&""")<br>"
if fld6<>"" then response.write rsname&"("""&fld6&""")=Request.form("""&fld6&""")<br>"
if fld7<>"" then response.write rsname&"("""&fld7&""")=Request.form("""&fld7&""")<br>"
if fld8<>"" then response.write rsname&"("""&fld8&""")=Request.form("""&fld8&""")<br>"
if fld9<>"" then response.write rsname&"("""&fld9&""")=Request.form("""&fld9&""")<br>"
if fld10<>"" then response.write rsname&"("""&fld10&""")=Request.form("""&fld10&""")<br>"
if fld11<>"" then response.write rsname&"("""&fld11&""")=Request.form("""&fld11&""")<br>"
if fld12<>"" then response.write rsname&"("""&fld12&""")=Request.form("""&fld12&""")<br>"
if fld13<>"" then response.write rsname&"("""&fld13&""")=Request.form("""&fld13&""")<br>"
if fld14<>"" then response.write rsname&"("""&fld14&""")=Request.form("""&fld14&""")<br>"
if fld15<>"" then response.write rsname&"("""&fld15&""")=Request.form("""&fld15&""")<br>"
if fld16<>"" then response.write rsname&"("""&fld16&""")=Request.form("""&fld16&""")<br>"
if fld17<>"" then response.write rsname&"("""&fld17&""")=Request.form("""&fld17&""")<br>"
if fld18<>"" then response.write rsname&"("""&fld18&""")=Request.form("""&fld18&""")<br>"
if fld19<>"" then response.write rsname&"("""&fld19&""")=Request.form("""&fld19&""")<br>"
if fld20<>"" then response.write rsname&"("""&fld20&""")=Request.form("""&fld20&""")<br>"
response.write rsname&".Update<br>"
response.write rsname&".Close<br>"
response.write "Set "&rsname&"=Nothing<p>"
response.write "Response.write ""<center><b>Record Added!</b></center>"""
response.write "<br>%>"

Else

'This is the code for the html input form
%>
<html>

<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage Express 2.0">
<title>Add records to a database using ADO and a DSN-Less Connection</title>
</head>

<body bgcolor="#FFFFFF">

<h1 align="center">ADO Input Code Generator</h1>

<h4 align="center">(For Microsoft Access)</h4>

<hr>

<form action="addrecord.asp" method="POST">
<input type="hidden" name="sent" value="true"><div
align="center"><center><table border="0" cellpadding="4"
cellspacing="0" width="80%">
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Database Connection Variable:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T1" value="strConn"></p>
</td>
</tr>
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Database Folder:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T2"></p>
</td>
</tr>
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Database Filename:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T3">.mdb</p>
</td>
</tr>
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Database Table Name:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T4"></p>
</td>
</tr>
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Recordset Variable Name:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T5" value="ObjRs"></p>
</td>
</tr>
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Cursor Type 1:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T6" value="2"></p>
</td>
</tr>
<tr>
<td align="right" colspan="2" width="50%"><p
align="right">Cursor Type 2:</p>
</td>
<td colspan="2" width="50%"><p align="left"><input
type="text" size="20" name="T7" value="2"></p>
</td>
</tr>
<tr>
<td align="center" colspan="4"><hr><b>(Name the input
elements on your form to match the names of your
database fields.)</b><hr></td>
</tr>
<tr>
<td align="right" width="25%">Input 1:</td>
<td width="25%"><input type="text" size="20"
name="T8"></td>
<td width="25%"><p align="right">Input 11:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T18"></td>
</tr>
<tr>
<td align="right" width="25%">Input 2:</td>
<td width="25%"><input type="text" size="20"
name="T9"></td>
<td width="25%"><p align="right">Input 12:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T19"></td>
</tr>
<tr>
<td align="right" width="25%">Input 3:</td>
<td width="25%"><input type="text" size="20"
name="T10"></td>
<td width="25%"><p align="right">Input 13:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T20"></td>
</tr>
<tr>
<td align="right" width="25%">Input 4:</td>
<td width="25%"><input type="text" size="20"
name="T11"></td>
<td width="25%"><p align="right">Input 14:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T21"></td>
</tr>
<tr>
<td align="right" width="25%">Input 5:</td>
<td width="25%"><input type="text" size="20"
name="T12"></td>
<td width="25%"><p align="right">Input 15:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T22"></td>
</tr>
<tr>
<td align="right" width="25%">Input 6:</td>
<td width="25%"><input type="text" size="20"
name="T13"></td>
<td width="25%"><p align="right">Input 16:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T23"></td>
</tr>
<tr>
<td align="right" width="25%">Input 7:</td>
<td width="25%"><input type="text" size="20"
name="T14"></td>
<td width="25%"><p align="right">Input 17:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T24"></td>
</tr>
<tr>
<td align="right" width="25%">Input 8:</td>
<td width="25%"><input type="text" size="20"
name="T15"></td>
<td width="25%"><p align="right">Input 18:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T25"></td>
</tr>
<tr>
<td align="right" width="25%">Input 9:</td>
<td width="25%"><input type="text" size="20"
name="T16"></td>
<td width="25%"><p align="right">Input 19:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T26"></td>
</tr>
<tr>
<td align="right" width="25%">Input 10:</td>
<td width="25%"><input type="text" size="20"
name="T17"> </td>
<td width="25%"><p align="right">Input 20:</p>
</td>
<td width="25%"><input type="text" size="20"
name="T27"></td>
</tr>
</table>
</center></div><p align="center"><input type="submit"
name="B1" value="Submit"> <input type="reset" name="B2"
value="Reset"></p>
</form>
</body>
</html>
<%End if%>