<% ' save as record_insertion_form.asp %>

<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Record Insertion Form Generator for ASP and Access</title>
<link rel="stylesheet" type="text/css" href="../index.css">
</head>

<body>
<p>
<b><font face="Webdings" color="#FF0000">3</font></b><i><font size="1"><a href="http://www.classicaspreference.com/aspexamples/menu.htm">Back
to ASP Examples
Menu</a></font><br>
</i>
<b><font face="Webdings" color="#FF0000">3</font><font size="1"><i><a target="_blank" href="select_database.asp">Back
to Database Selection</a></i></font></b><br>
<b><font face="Webdings" color="#FF0000">3</font></b><i><font size="1"><a href="default.asp">Back to Database Interface Wizard Home Page</a></font><br>
</i>
<b><font face="Webdings" color="#FF0000">4</font><i><font size="1"><a title="Please let me know if this has been helpful." href="mailto:lilpeck@gmail.com?subject=database interface generation wizard">This
script cobbled together by Lil Peck</a></font></i></b><p><font size="4"><b>
Generate Record Addition Form with
Data Types</b></font><b><font size="4">.</font></b> <br>
&nbsp;<font size="1">Adapted from
<a target="_blank" href="http://www.aspemporium.com/aspapps.asp?eid=16">Database Table and Field Summary</a>
and <b>
<a target="_blank" href="http://www.4guysfromrolla.com/webtech/082699-1.shtml">A
Generic Database Record Insertion Script</a></b></font><br>
<a target="_blank" href="newrecord.asp">Example</a> of &quot;bare bones&quot; generated
new record insertion script.<hr>
<p align="left"><font size="2"><b>' save as <%=request.form("Table")%>_addrecord.asp&nbsp;</b></font><p align="left"><font size="1"><br>
&lt;FORM ACTION=&quot;&lt;%=request.servervariables(&quot;script_name&quot;)%&gt;&quot; METHOD=POST&gt;<br>
&lt;INPUT TYPE=hidden NAME=add value=&quot;add&quot;&gt;<br>
<br>
<%
dim strMdb
strMdb=request.form("dbname")


''''''''''''''''''''''''''''''
' CLASS CODE
''''''''''''''''''''''''''''''
' *********************** Database Summary ************************

' DateCreated: 4/27/2000
' DateLastModified: 10/17/2004

' NOTES:
' This application requires the aspemporium test database
' to function properly


' ado constants
CONST adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001

' script level variables
Dim objConn

Sub db_summary
' this sub calls the first routine, starting the system
db_tables
End Sub

Sub db_tables
' this sub accesses a db using the ado catalog component
' to gather all the tables of the current db.
Dim i, objCat, displayedct
Set objConn = Server.CreateObject("ADODB.Connection")

Set objCat = Server.CreateObject("ADOX.Catalog")
'objConn.Open Application("dbConn"), Application("dbUsr"), Application("dbPass")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strMdb) & ";"
objCat.ActiveConnection = objConn
' access the tables collection for this db from the catalog object
displayedct = 0
For i = 0 to objCat.tables.Count -1
if displayedct >= 3 then Exit For
if len(objCat.tables(i).name) > 1 then
if left(objCat.tables(i).name, 3) <> "AE_" then
' we are only interested in table objects
' so if we find one, call the db_fields sub
' to display the datatypes and field names
' of any fields in the current table
If objCat.tables(i).type = "TABLE" then

db_fields objCat.tables(i).name
displayedct = displayedct + 1
'end if
end if
end if
end if
NEXT
Set objCat = Nothing
objConn.Close
Set objConn = Nothing
End Sub

Sub db_fields(strTable)
if strTable = request.form("Table") then
' this sub gathers the names and data
' types of fields for a given table.
Dim objRs, strSQL, i, strOutput
Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM " & strTable & ";"
objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
' access the fields collection for this table
For i = 0 to objRs.fields.count -1
'FIX html inject vulnerability


strOutput = strOutput & "<tr>" & vbCrLf
strOutput = strOutput & "<td width=400>&lt;br&gt;"&server.htmlencode(objRs.fields(i).name)&":&#060;&#066;&#082;&#062;&#60;input type="&"&#34;text&#34;"&" name=&#34;"& server.htmlencode(objRs.fields(i).name) &"&#34; size="&"&#34;30&#34;"&"&#62;&#060;&#066;&#082;&#062;" & _
"</td>" & vbCrLf
strOutput = strOutput & "<td width=25><font color=#FF0000>&gt;</FONT></td>" & vbCrLf
' the objRs.fields(i).type command outputs an integer so
' let's put it through the DataTypeName function to give the
' number a better meaning with a text value.
strOutput = strOutput & "<td width=200><font color=#FF0000 size=1>" & server.htmlencode(DataTypeName(objRs.fields(i).type)) & "&lt;br&gt;</FONT></td>" & vbCrLf
strOutput = strOutput &"</tr>" & vbCrLf

Next

objRs.Close
Set objRs = Nothing
end if
response.write "<p>" & vbCrLf
response.write "<table width=600 style=""font-family:helvetica;font-size:10pt;"">" & vbCrLf
response.write "<tr>" & vbCrLf
response.write "<td colspan=3><big><b>" & "</b></big></td>" & vbCrLf
response.write "</tr>" & vbCrLf
response.write strOutput
response.write "</table>" & vbCrLf
response.write "</p>" & vbCrLf
End Sub

Function DataTypeName(icurrenttype)
' this function takes an integer and translates
' it into a text based description of a data type
' that is more familiar than a number.
SELECT CASE icurrenttype
case 20
DataTypeName = "adBigInt"
case 128
DataTypeName = "adBinary (sql server - IMAGE)"
case 11
DataTypeName = "adBoolean (ms access - YES/NO / sql server - BIT)"
case 8
DataTypeName = "adBSTR"
case 136
DataTypeName = "adChapter"
case 129
DataTypeName = "adChar"
case 6
DataTypeName = "adCurrency (ms access - CURRENCY / sql server - MONEY)"
case 7
DataTypeName = "adDate"
case 133
DataTypeName = "adDBDate"
case 134
DataTypeName = "adDBTime"
case 135
DataTypeName = "adDBTimeStamp (sql server - DATETIME)"
case 14
DataTypeName = "adDecimal"
case 5
DataTypeName = "adDouble"
case 0
DataTypeName = "adEmpty"
case 10
DataTypeName = "adError"
case 64
DataTypeName = "adFileTime"
case 72
DataTypeName = "adGUID"
case 9
DataTypeName = "adIDispatch"
case 3
DataTypeName = "adInteger (ms access - NUMBER / sql server - INT)"
case 13
DataTypeName = "adIUnknown"
case 205
DataTypeName = "adLongVarBinary"
case 201
DataTypeName = "adLongVarChar"
case 203
DataTypeName = "adLongVarWChar (ms access - MEMO)"
case 131
DataTypeName = "adNumeric"
case 138
DataTypeName = "adPropVariant"
case 4
DataTypeName = "adSingle"
case 2
DataTypeName = "adSmallInt"
case 16
DataTypeName = "adTinyInt"
case 21
DataTypeName = "adUnsignedBigInt"
case 19
DataTypeName = "adUnsignedInt"
case 18
DataTypeName = "adUnsignedSmallInt"
case 17
DataTypeName = "adUnsignedTinyInt"
case 132
DataTypeName = "adUserDefined"
case 204
DataTypeName = "adVarBinary "
case 200
DataTypeName = "adVarChar (sql server - VARCHAR(x))"
case 12
DataTypeName = "adVariant "
case 139
DataTypeName = "adVarNumeric"
case 202
DataTypeName = "adVarWChar (ms access - TEXT)"
case 130
DataTypeName = "adWChar"
case Else
DataTypeName = "UNDETERMINED"
END SELECT
End Function

'end if

%>


<%
''''''''''''''''''''''''''''''
' RUNTIME CODE
''''''''''''''''''''''''''''''
db_summary

%><br>&lt;INPUT TYPE=SUBMIT&gt;<br>
&lt;/FORM&gt;</font><p align="left"><font size="1">&lt;%<br>
if NOT request.form(&quot;add&quot;)=&quot;&quot; then %&gt;<br>
&lt;%<br>
'Our ADO constants we'll need<br>
Const adOpenForwardOnly = 0<br>
Const adLockOptimistic = 3<br>
Const adCmdTable = &amp;H0002<br>
<br>
Dim conn ' ADO connection<br>
Dim rs ' ADO recordset<br>
Dim strDBPath ' path to our Access database (*.mdb) file<br>
<br>
<br>
strDBPath = Server.MapPath(&quot;<%=request.form("dbname")%>&quot;)<br>
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot;)<br>
conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; &amp; strDBPath &amp; &quot;;&quot;<br>
<br>
<br>
'Create a recordset object<br>
<br>
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)<br>
<br>
'Open a table view for the table name specified by Request(&quot;TableName&quot;)<br>
Dim strTableName<br>
strTableName = &quot;<%=request.form("Table")%>&quot;&nbsp; <br>
<br>
rs.Open strTableName, conn, adOpenForwardOnly, adLockOptimistic, adCmdTable<br>
<br>
<br>
'Add a new record...<br>
rs.AddNew<br>
<br>
'Iterate through the fields of the table...<br>
Dim fld<br>
For Each fld in rs.Fields<br>
'If a value for the column name was passed in,<br>
'set the column name equal to the value passed through the form...<br>
if Len(Request(fld.Name)) &gt; 0 then<br>
fld.Value = Request(fld.Name)<br>
end if<br>
Next<br>
<br>
'We're done, so update the record<br>
rs.Update<br>
<br>
<br>
'Clean Up...<br>
rs.Close<br>
Set rs = Nothing<br>
<br>
conn.Close<br>
Set conn = Nothing<br>
<br>
<br>
'Send the user to some confirmation page or give confirmation message:<br>
'Response.Redirect &quot;list.asp&quot;<br>
Response.Write &quot;Record Added!&quot;<br>
<br>
%&gt;<br>
&lt;%<br>
end if %&gt;
</font>
</BODY>
</HTML>