<%
Response.Expires = 0
Response.Buffer = True
' [green]Connection/Table Variables[/green]
Connection = "dsn=blah;"
Tablename="blahtable"
IdField = "ID"
' [green]Data Settings [/green]
NumericTypes = "2,3,4,5,6,17,72,128,131"
TruthTypes = "11"
DataTypes = "128,129,130,200,201,202,203,204,205"
DateTypes = "135"
DateChar = "#"
' [green]DateChar is the Date Qualifyer for your DataSource, SQL uses single quote, MS Access uses #[/green]
%>
<html>
<head>
<title>Record Editor</title>
</head>
<body>
<%
Set Con = CreateObject("ADODB.Connection")
Con.Open Connection
SQL = "Select * From [" & TableName & "] Order by " & IdField ' [green]this may need to be altered for pagination if you have large tables[/green]
Set RS = Con.Execute(SQL)
If Not RS.EOF Then ' [green]this check is here just for empty tables, you may want to juggle things around a little[/green]
If Request("Action") = "Save" AND Request("ID") <> "" Then
Dim SaveSQL(0)
SaveSQL(0) = "Update [" & TableName & "] Set "
For each Element in Request.Form
If Element = "ID" Then
SqlCondition = " Where [" & IDField & "]=" & Request(Element) ' [green]Leading Space is important, dont remove[/green]
Else
FieldName = Mid(Element,4)
If Left(Element,3) = "Txt" Then
If Request(element) <> "" Then
SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]='" & Replace(Request(Element),"'","''") & "', "
Else
SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=NULL, "
End If
ElseIf Left(Element,3) = "Num" Then
If Request(element) <> "" Then
SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=" & Request(Element) & ", "
Else
SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=NULL, "
End If
ElseIf Left(Element,3) = "ToF" Then
SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=" & Request(Element) & ", "
ElseIf Left(Element,3) = "Dte" Then
SaveSQL(0) = SaveSQL(0) & "[" & FieldName & "]=" & DateChar & Request(Element) & DateChar & ", "
End If
End If
Next
SaveSQL(0) = Left(SaveSQL(0),Len(SaveSQL(0))-2) & SqlCondition ' [green]Chopping off the trailing comma/space and adding the condition to it[/green]
Set RSUpd = Con.Execute(SaveSQL(0))
response.write "Record Saved<br>"
End If
' [green]break in conditionals, so that after save, you can still View Record[/green]
If (Request("Action") = "Edit" OR Request("Action") = "Save") AND (Request("ID") <> "") Then
Set RS = Con.Execute("Select * From [" & TableName & "] Where [" & IdField & "]=" & Request("ID"))
' [green]outputting Form[/green]
If Not Rs.EOF then
%>
<form method="post">
<table border=1>
<tr>
<td><%=IdField%>:</td><td><%=RS(IDField)%><input type="hidden" name="ID" value="<%=RS(IDField)%>"></td>
</tr>
<%
For Each Field in Rs.Fields
If StrComp(Field.name,IdField,vbTextcompare) <> 0 Then
%>
<tr>
<td><%=Field.Name%>:</td>
<%
If ChkArray(DataTypes,Field.Type) Then ' [green]looking for text type fields[/green]
If Field.DefinedSize >= 200 then
%>
<td><textarea name="Txt<%=Server.HTMLEncode(Field.Name)%>"><%=Server.HTMLEncode(RS(Field.Name))%></textarea></td>
<%
Else
%>
<td><input type="text" name="Txt<%=Server.HTMLEncode(Field.Name)%>" value="<%=Server.HTMLEncode(RS(Field.Name))%>" maxsize="<%=Field.DefinedSize%>"></td>
<%
End If
ElseIf ChkArray(NumericTypes,Field.Type) Then ' [green]looking for numeric type fields[/green]
%>
<td><input type="text" name="Txt<%=Server.HTMLEncode(Field.Name)%>" value="<%=Server.HTMLEncode(RS(Field.Name))%>"></td>
<%
ElseIf ChkArray(TruthTypes,Field.Type) Then ' [green]looking for numeric type fields[/green]
If Not IsNull(RS(Field.Name)) then
If RS(Field.Name) = 1 OR RS(Field.Name) = True Then
OptTrue = " CHECKED"
OptFalse = ""
ElseIf RS(Field.Name) = 0 OR RS(Field.Name) = False Then
OptTrue = ""
OptFalse = " CHECKED"
Else
OptTrue = ""
OptFalse = ""
End If
Else
OptTrue = ""
OptFalse = ""
End If
%>
<td>
True <input type="radio" name="ToF<%=Server.HTMLEncode(Field.Name)%>" value="1"<%=OptTrue%>>
False <input type="radio" name="ToF<%=Server.HTMLEncode(Field.Name)%>" value="0"<%=OptFalse%>>
</td>
<%
ElseIf ChkArray(DateTypes,Field.Type) Then ' [green]looking for date type fields[/green]
%>
<td nowrap><input type="text" name="Txt<%=Server.HTMLEncode(Field.Name)%>" value="<%=Server.HTMLEncode(RS(Field.Name))%>"> DateValue, Format as MM/DD/YYYY OR MM/DD/YYYY</td>
<%
Else ' [green]catching unknown field types[/green]
%>
<td>Unknown FieldType : <%=Field.Type%></td>
<%
End If
%>
</tr>
<%
End If
Next
%>
</table>
<input type="submit" value="Save" name="action">
<input type="submit" value="Cancel/Done" name="action">
</form>
<%
Else
%>
Error Retrieving Record.
<%
End If
Else ' [green]Displays your Table Contents for Editing[/green]
%>
<table border=1>
<tr>
<td><b><%=IdField%></b></td>
<%
For Each Field in Rs.Fields
If StrComp(Field.name,IdField,vbTextcompare) <> 0 Then ' show all fields except ID since ID is above ( forced first Column )
%>
<td><b><%=Field.Name%></b></td>
<%
End If
Next
%>
<td> </td>
</tr>
<%
' [green]outputting Data[/green]
Do While Not Rs.EOF
%>
<tr>
<td><%=Server.HTMLEncode(RS(IdField))%></td>
<%
For Each Field in Rs.Fields
If StrComp(Field.name,IdField,vbTextcompare) <> 0 Then ' show all fields except ID since ID is above ( forced first Column )
%>
<td><%=Server.HTMLEncode(RS(Field.Name))%></td>
<%
End If
Next
%>
<td><table><tr><td><form method="post"><input type="submit" value="Edit" name="Action"><input type="hidden" name="ID" value="<%=RS(IdField)%>"></td></tr><tr><td></form></td></tr></table></td>
</tr>
<%
Response.Flush
RS.MoveNext
Loop
%>
</table>
<%
End If
Else
%>
Table Empty, enter at least one record to be able to edit.
<%
End if
Set RS = nothing
con.Close
Set Con = nothing
%>
</body>
</html>
<%
Function ChkArray(Values,Value) ' [green]returns true/false on a comparitive set[/green]
If IsArray(Values) Then
ChkArrayArr = Values
Else
ChkArrayArr = Split(Values,",")
End If
ChkArray = False
For ChkArrayArrCounter=0 to Ubound(ChkArrayArr)
If StrComp(ChkArrayArr(ChkArrayArrCounter),Value,vbTextCompare)=0 Then
ChkArray = True
End If
Next
End Function
%>
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
DreX