Form Wizard 1

[pgferro]

Hi guys,

I have a project with like 50 tables that have to be initialized, hence I will have to prepare a basic form (add/edit/delete) for each one of them....
You all know how boring and time consuming that process is, so I'm wondering if there's anybody who came up with a sort of 'wizard' to rapidly build forms out of a basic structure, better if with some sort of validation
I know, the best wizard is always notepad.exe, so please spare me that tip...

THANKS

--
PG

 

[DreXor]

well i built a form in the past, cant really use it in your application, due to too much customised stuff in it. would take longer to strip it down than to make it from scratch ..
here's the "logisitics" of it...
this is for single record editing, would be easy enough to build a quick front end for selecting the record to edit.

tablename = blah ' table name
keyfield = blahid ' primary id
hiddenfields = "blahid" ' fields to hide in form ( non-editable )
select top1 * from tablename
for each field in rs.fields
fieldnames = fieldnames & field.name ,
fieldtypes = fieldtypes & field.datatype ,
fieldsize = fieldsize & field.datasize ,
next
then split all 3 variables these are used in combo with the recordset, if the field type is truefalse, use radios, iffield type is text or number use a text box, and if the field size is over say 200 use a textarea

then it's just a matter of cycling the fieldnames, checking data types, and spitting out the proper form element for it plus a submit/delete/cancel button set at the bottom ( buttons named action with value checks on submit for actions ) i used a heavy naming convention, typically : <fieldtype><fieldname> and used things like Txt Num and Chk for fieldtype, so it would be alot easier on submit to convert the requested data in the sql as per necessary. then stripped the fieldtype from the request element so i had the field name after.

as for validation it wouldn't be difficult use the same field checking for hidden fields for required fields, and or field type for length/value etc.

if you need more help building this feel free to ask. i put the thread on notification

DreX
aKa - Robert

 

[pgferro]

Thanks Drex,

I was basically thinking the same way, but I wanted to check if anybody had already prepared even a 'stretchable' routine for me to play with, avoiding me the hassle...

The first problem I can think of with this approach would be for the 'update/add' sql statement, heavily dependent on field type. Eg date, logic, number, etc... How did you solved that ?

Thanks again

--
PG

 

[DreXor]

search threads in here for tip/thread functions.inc

the first 3 lines are dims for variable types for SQL
those were used to check against in the form handling.

if the fieldtype fell into

datatypes : put it in single quotes
datetypes : date validation, shove in quotes
numbertypes : no quotes, null if blank



DreX
aKa - Robert

 

[DreXor]

i will post the customised code in here if you'd like, you're welcome to try and chew it down or if i have some time today to overhaul it i'll try.

if i do post it, it's a gawdawful huge mess of function calls and long.

DreX
aKa - Robert

 

[DreXor]

in the process of just making a general dynamic form tool at the moment. i'll post the end result soon.

DreX
aKa - Robert

 

[pgferro]

Thanks Drex, appreciate it !

 

[DreXor]

Needed an unspecialized one anyhow, also with a couple more conditionals and a formset, you could type in the variables at the beginning connection/table/idfield and use this pretty much on any data source you have

<%
Response.Expires = 0
Response.Buffer = True
' [green][b]Connection/Table Variables[/b][/green]
Connection = "dsn=Blah;"
Tablename="TableBlah"
IdField = "ID" ' [green][b]built around a primary key index (integer)[/b][/green]

' [green][b]Data Settings [/b][/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][b]DateChar is the Date Qualifyer for your DataSource, SQL uses single quote, MS Access uses #[/b][/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][b]this may need to be altered for pagination if you have large tables[/b][/green]
Set RS = Con.Execute(SQL)
If Not RS.EOF Then ' [green][b]this check is here just for empty tables, you may want to juggle things around a little[/b][/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][b]Leading Space is important, dont remove[/b][/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][b]Chopping off the trailing comma/space and adding the condition to it[/b][/green]
    Set RSUpd = Con.Execute(SaveSQL(0))
    response.write "Record Saved<br>"
  End If
  ' [green][b]break in conditionals, so that after save, you can still View Record[/b][/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][b]outputting Form[/b][/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][b]looking for text type fields[/b][/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][b]looking for numeric type fields[/b][/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][b]looking for numeric type fields[/b][/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%>>&nbsp;&nbsp;
        False <input type="radio" name="ToF<%=Server.HTMLEncode(Field.Name)%>" value="0"<%=OptFalse%>>
      </td>
<%
          ElseIf ChkArray(DateTypes,Field.Type) Then ' [green][b]looking for date type fields[/b][/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][b]catching unknown field types[/b][/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][b]Displays your Table Contents for Editing[/b][/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>&nbsp;</td>
    </tr>
<%
' [green][b]outputting Data[/b][/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][b]returns true/false on a comparitive set[/b][/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
%>

DreX
aKa - Robert

 

[DreXor]

PS... having a form like this is a severe security issue, bury it and authenticate it strongly

DreX
aKa - Robert