Auto form builder from DB schema - code that codes
Hi guys, I developed this about a week ago but it has come in bloody handy already.
All you have to do is stick in your connection string (line 279), run the page and type the name of the table you want to build a form for. Once you hit submit it spits out your html form, select/insert/update/delete and collect form values functions and basic logic sequence.
Bear in mind its a fresh concept, coded quickly, so may be a bit buggy and could be coded a bit better but I will keep this page updated, feel free to mess with it (but show me what you come up with).
Code:
<%
'*************************************************
' Form builder
' Created by: Derek Robertson
' Last modified: 14/05/2008
'
'*************************************************
Dim strAction, strSQL, rsBuilder, strTable
Dim strFormCollection, strHTMLForm, strInsertQuery, strUpdateQuery, strSelectQuery, strDeleteQuery, strDeclaration, strFields, strValues, strIDField
'################################ FUNCTIONS AT END OF PAGE
strAction = Request("action")
strTable = Request("table")
strFormCollection = ""
strHTMLForm = ""
strInsertQuery = ""
strUpdateQuery = vbtab&"strSQL = ""Update " & strTable & " Set ""&_"&vbcrlf
strSelectQuery = ""
strDeclaration = ""
strFields = ""
strValues = ""
If strAction = "1" Then
call OpenDB()
strSQL = "select column_name, ordinal_position, data_type, character_maximum_length from information_schema.columns where table_name = '" & strTable & "' order by ordinal_position"
Set rsBuilder = adoConn.Execute(strSQL)
If Not rsBuilder.EOF Then
strDeleteQuery = vbtab & "strSQL = ""Delete From " & strTable & " Where " & rsBuilder("column_name") & " = "" & intRecordID"&vbcrlf&vbtab&"adoConn.Execute(strSQL)"&vbcrlf
strIDField = rsBuilder("column_name")
rsBuilder.MoveNext
Do Until rsBuilder.EOF
Select Case rsBuilder("data_type")
Case "int", "smallint", "bigint", "tinyint"
strDeclaration = strDeclaration & "int"&rsBuilder("column_name")&", "
strFormCollection = strFormCollection & vbtab & "int"&rsBuilder("column_name")&" = Numeric(Request.Form("""&rsBuilder("column_name")&"""))" & vbcrlf
strSelectQuery = strSelectQuery & vbtab & "int"&rsBuilder("column_name")&" = rsResults("""&rsBuilder("column_name")&""")" & vbcrlf
strHTMLForm = strHTMLForm & vbtab &"<tr>" & vbcrlf &_
vbtab & vbtab & "<td>"&Replace(rsBuilder("column_name"), "_", " ")&"</td>" &vbcrlf &_
vbtab & vbtab & "<td><input name="""&rsBuilder("column_name")&""" type=""text"" value=""<"&"%= int"&rsBuilder("column_name")&"%"&">"" maxlength=""5""></td>" &vbcrlf &_
vbtab &"</tr>" & vbcrlf
strFields = strFields & rsBuilder("column_name") & ","
strValues = strValues & """&int"&rsBuilder("column_name")&"&"","
strUpdateQuery = strUpdateQuery & vbtab & vbtab & """" & rsBuilder("column_name") & " = ""&int"&rsBuilder("column_name")&"&"",""&_"&vbcrlf
Case "nvarchar", "varchar", "ntext", "text"
strDeclaration = strDeclaration & "str"&rsBuilder("column_name")&", "
strFormCollection = strFormCollection & vbtab & "str"&rsBuilder("column_name")&" = FormatEncode(Request.Form("""&rsBuilder("column_name")&"""))" & vbcrlf
strSelectQuery = strSelectQuery & vbtab & "str"&rsBuilder("column_name")&" = rsResults("""&rsBuilder("column_name")&""")" & vbcrlf
strHTMLForm = strHTMLForm & vbtab &"<tr>" & vbcrlf &_
vbtab & vbtab & "<td>"&Replace(rsBuilder("column_name"), "_", " ")&"</td>" &vbcrlf &_
vbtab & vbtab & "<td><input name="""&rsBuilder("column_name")&""" type=""text"" value=""<"&"%= Replace(str"&rsBuilder("column_name")&", ""'"", ""''"")%"&">"" maxlength="""&rsBuilder("character_maximum_length")&"""></td>" &vbcrlf &_
vbtab &"</tr>" & vbcrlf
strFields = strFields & rsBuilder("column_name") & ","
strValues = strValues & "'""&str"&rsBuilder("column_name")&"&""',"
strUpdateQuery = strUpdateQuery & vbtab & vbtab & """" & rsBuilder("column_name") & " = '""&str"&rsBuilder("column_name")&"&""',""&_"&vbcrlf
Case "bit"
strDeclaration = strDeclaration & "bln"&rsBuilder("column_name")&", "
strFormCollection = strFormCollection & vbtab & "bln"&rsBuilder("column_name")&" = Request.Form("""&rsBuilder("column_name")&""")" & vbcrlf
strSelectQuery = strSelectQuery & vbtab & "bln"&rsBuilder("column_name")&" = rsResults("""&rsBuilder("column_name")&""")" & vbcrlf
strHTMLForm = strHTMLForm & vbtab &"<tr>" & vbcrlf &_
vbtab & vbtab & "<td>"&Replace(rsBuilder("column_name"), "_", " ")&"</td>" &vbcrlf &_
vbtab & vbtab & "<td><input name="""&rsBuilder("column_name")&""" type=""checkbox"" value=""1""></td>" &vbcrlf &_
vbtab &"</tr>" & vbcrlf
strFields = strFields & rsBuilder("column_name") & ","
strValues = strValues & "'""&bln"&rsBuilder("column_name")&"&""',"
strUpdateQuery = strUpdateQuery & vbtab & vbtab & """" & rsBuilder("column_name") & " = '""&bln"&rsBuilder("column_name")&"&""',""&_"&vbcrlf
Case "smalldatetime", "datetime"
strDeclaration = strDeclaration & "dte"&rsBuilder("column_name")&", "
strFormCollection = strFormCollection & vbtab & "If IsDate(Request.Form("""&rsBuilder("column_name")&""")) Then dte"&rsBuilder("column_name")&" = MediumDate(Request.Form("""&rsBuilder("column_name")&"""))" & vbcrlf
strSelectQuery = strSelectQuery & vbtab & "dte"&rsBuilder("column_name")&" = rsResults("""&rsBuilder("column_name")&""")" & vbcrlf
strHTMLForm = strHTMLForm & vbtab &"<tr>" & vbcrlf &_
vbtab & vbtab & "<td>"&Replace(rsBuilder("column_name"), "_", " ")&"</td>" &vbcrlf &_
vbtab & vbtab & "<td>"&DateDropDown(rsBuilder("column_name"))&"</td>" &vbcrlf &_
vbtab &"</tr>" & vbcrlf
strFields = strFields & rsBuilder("column_name") & ","
strValues = strValues & "'""&dte"&rsBuilder("column_name")&"&""',"
strUpdateQuery = strUpdateQuery & vbtab & vbtab & """" & rsBuilder("column_name") & " = '""&dte"&rsBuilder("column_name")&"&""',""&_"&vbcrlf
Case "money"
strDeclaration = strDeclaration & "dec"&rsBuilder("column_name")&", "
strFormCollection = strFormCollection & vbtab & "dec"&rsBuilder("column_name")&" = Request.Form("""&rsBuilder("column_name")&""")" & vbcrlf
strSelectQuery = strSelectQuery & vbtab & "dec"&rsBuilder("column_name")&" = rsResults("""&rsBuilder("column_name")&""")" & vbcrlf
strHTMLForm = strHTMLForm & vbtab &"<tr>" & vbcrlf &_
vbtab & vbtab & "<td>"&Replace(rsBuilder("column_name"), "_", " ")&"</td>" &vbcrlf &_
vbtab & vbtab & "<td><input name="""&rsBuilder("column_name")&""" type=""text"" value=""<"&"%= dte"&rsBuilder("column_name")&"%"&">"" maxlength="""&rsBuilder("character_maximum_length")&"""></td>" &vbcrlf &_
vbtab &"</tr>"
strFields = strFields & rsBuilder("column_name") & ","
strValues = strValues & """&dec"&rsBuilder("column_name")&"&"","
strUpdateQuery = strUpdateQuery & vbtab & vbtab & """" & rsBuilder("column_name") & " = ""&dec"&rsBuilder("column_name")&"&"",""&_"&vbcrlf
End Select
rsBuilder.MoveNext
Loop
strDeclaration = "Dim "& Left(strDeclaration,len(strDeclaration)-2) & ", intRecordID, arrDate, intCounter, strSQL, rsResults"
strHTMLForm = "<form name=""form1"" action=""<"&"%=Request.ServerVariables(""SCRIPT_NAME"")%"&">?id=<"&"%=intRecordID%"&">"" method=""post"" style=""margin:0px;"">"& vbcrlf&_
"<table>"& vbcrlf&_
strHTMLForm& vbcrlf&_
"<tr>" & vbcrlf&_
vbtab & "<td>Submit</td>" & vbcrlf&_
vbtab & "<td><input type=""button"" value=""Cancel"" name=""Cancel"" onClick=""history.go(-1);"" /> <input name=""submit"" type=""submit"" value=""Update""><input name=""formGo"" type=""hidden"" value=""1"" />" & vbcrlf&_
vbtab & "</td>" & vbcrlf&_
"</tr>"& vbcrlf&_
"</table>"& vbcrlf&_
"</form>"
strFormCollection = "Function GetFormValues()" & vbcrlf &_
strFormCollection &_
"End Function"
strInsertQuery = "Function InsertIntoDatabase()" & vbcrlf &_
vbtab & "strSQL = ""Insert Into " & strTable & " (" & Left(strFields,len(strFields)-1) & ") Values(" & Left(strValues,len(strValues)-1) & ")"""&vbcrlf&_
vbtab & "OpenDB()"&vbcrlf&_
vbtab & "adoConn.Execute(strSQL)"&vbcrlf&_
vbtab & "CloseDB()"&vbcrlf&_
"End Function"
strUpdateQuery = "Function UpdateDatabase(intRecordID)" & vbcrlf &_
Left(strUpdateQuery,len(strUpdateQuery)-6) & """&_"& vbcrlf & vbtab & vbtab & """" & " Where " & strIDField & " = "" & intRecordID"&vbcrlf&_
vbtab&"OpenDB()"&vbcrlf&vbtab&"adoConn.Execute(strSQL)"&vbcrlf&vbtab&"CloseDB()"&vbcrlf&_
"End Function"
strSelectQuery = "Function GetDBValues(intRecordID)" & vbcrlf &_
vbtab&"strSQL = ""select * from "&strTable&" where "&strIDField&" = "" & intRecordID"&vbcrlf&_
vbtab&"OpenDB()"&vbcrlf&_
vbtab&"Set rsResults=adoConn.Execute(strSQL)"&vbcrlf&_
strSelectQuery & vbcrlf &_
vbtab&"rsResults.Close()"&vbcrlf&_
vbtab&"Set rsResults = Nothing"&vbcrlf&_
vbtab&"CloseDB()"&vbcrlf&_
"End Function"
strDeleteQuery = "Function DeleteRow(intRecordID)" & vbcrlf &_
strDeleteQuery & vbcrlf &_
"End Function"
End If
rsBuilder.close()
Set rsBuilder = Nothing
call CloseDB()
End If
%>
<!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=iso-8859-1" />
<title>Dereks form builder</title>
</head>
<body>
<form action="formbuilder.asp" method="post" name="sqltool">
<table width="100%">
<tr>
<td valign="top">
table name: <input type="text" name="table" value="<%=strTable%>" style="width:200px;" />
<input type="hidden" name="action" value="1" />
<input type="submit" />
</td>
</tr>
</table>
</form>
<%If strAction = "1" Then%>
<textarea style="width:1250px;height:800px;">
<%
Response.write("<"&"%option explicit%"&">"&vbcrlf)
Response.write("<"&"!--#include virtual=""/inc/connfunc.asp""--"&">"&vbcrlf)
Response.write("<"&"!--#include virtual=""/inc/functions.asp""--"&">"&vbcrlf)
Response.write("<"&"%"&vbcrlf)
Response.write(strDeclaration&vbcrlf)
Response.write(vbcrlf)
Response.write(ActionLogic())
Response.write(vbcrlf)
Response.write("%"&">"&vbcrlf)
Response.write("<html>"&vbcrlf)
Response.write("<head>"&vbcrlf)
Response.write("</head>"&vbcrlf)
Response.write("<body>"&vbcrlf)
Response.write(strHTMLForm&vbcrlf)
Response.write("</body>"&vbcrlf)
Response.write("</html>"&vbcrlf)
Response.write("<"&"%"&vbcrlf)
Response.write(strFormCollection&vbcrlf)
Response.write(vbcrlf)
Response.write(strInsertQuery&vbcrlf)
Response.write(vbcrlf)
Response.write(strUpdateQuery&vbcrlf)
Response.write(vbcrlf)
Response.write(strSelectQuery&vbcrlf)
Response.write(vbcrlf)
Response.write(strDeleteQuery&vbcrlf)
Response.write("%"&">"&vbcrlf)
%>
</textarea>
<%End If%>
</body>
</html>
<%
Function DateDropDown(strFieldName)
DateDropDown = vbcrlf&"<"&"%"&vbcrlf&_
"If dte"&strFieldName&" <> """" Then"&vbcrlf&_
vbtab&"arrDate = Split(dte"&strFieldName&",""/"")"&vbcrlf&_
"Else"&vbcrlf&_
vbtab&"arrDate = Split(""0/0/0"",""/"")"&vbcrlf&_
"End If"&vbcrlf&_
"%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&"<select name="""&strFieldName&""" id="""&strFieldName&"1"">"&vbcrlf&_
vbtab&vbtab&vbtab&vbtab&"<option value=""0"">?</option>"&vbcrlf&_
"<"&"%for intCounter = 1 to 31%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&vbtab&"<option value=""<"&"%=intCounter%"&">""<"&"%if intCounter = Cint(arrDate(0)) Then Response.write("" selected=""""selected"""""")%"&">><"&"%=intCounter%"&"></option>"&vbcrlf&_
"<"&"%next%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&"</select>"&vbcrlf&_
vbtab&vbtab&vbtab&"<select name="""&strFieldName&""" id="""&strFieldName&"2"">"&vbcrlf&_
vbtab&vbtab&vbtab&vbtab&"<option value=""0"">?</option>"&vbcrlf&_
"<"&"%for intCounter = 1 to 12%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&vbtab&"<option value=""<"&"%=intCounter%"&">""<"&"%if intCounter = Cint(arrDate(1)) Then Response.write("" selected=""""selected"""""")%"&">><"&"%=monthname(intCounter)%"&"></option>"&vbcrlf&_
"<"&"%next%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&"</select>"&vbcrlf&_
vbtab&vbtab&vbtab&"<select name="""&strFieldName&""" id="""&strFieldName&"3"">"&vbcrlf&_
vbtab&vbtab&vbtab&vbtab&"<option value=""0"">?</option>"&vbcrlf&_
"<"&"%for intCounter = Cint(Year(NOW)) to Cint(Year(NOW))+7%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&vbtab&"<option value=""<"&"%=intCounter%"&">""<"&"%if intCounter = Clng(Left(arrDate(2),4)) Then Response.write("" selected=""""selected"""""")%"&">><"&"%=intCounter%"&"></option>"&vbcrlf&_
"<"&"%next%"&">"&vbcrlf&_
vbtab&vbtab&vbtab&"</select>"&vbcrlf&vbtab&vbtab
End Function
Function ActionLogic()
ActionLogic = "Session.lcid = 3081"&vbcrlf&_
"intRecordID = Request(""id"")"&vbcrlf&_
"Select Case Request(""formGo"")"&vbcrlf&_
" Case 1"&vbcrlf&_
" GetFormValues()"&vbcrlf&_
" If intRecordID <> """" Then"&vbcrlf&_
" UpdateDatabase(intRecordID)"&vbcrlf&_
" response.redirect(Request.ServerVariables(""SCRIPT_NAME"")&""?id=""&intRecordID)"&vbcrlf&_
" Else"&vbcrlf&_
" InsertIntoDatabase()"&vbcrlf&_
" End If"&vbcrlf&_
" Case 2"&vbcrlf&_
" If intRecordID <> """" Then DeleteRow(intRecordID)"&vbcrlf&_
" Case Else"&vbcrlf&_
" If intRecordID <> """" Then GetDBValues(intRecordID)"&vbcrlf&_
"End Select"
End Function
'Open the connection
Sub OpenDB()
set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open strActiveConn 'stick your conn string here
End Sub
'Close the connection
Sub CloseDB()
adoConn.Close()
Set adoConn = Nothing
End Sub
%>
connfunc.asp
Code:
<%'Open the connection
Sub OpenDB()
set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open strActiveConn 'stick your conn string here
End Sub
'Close the connection
Sub CloseDB()
adoConn.Close()
Set adoConn = Nothing
End Sub
%>
functions.asp
Code:
<%Function MediumDate (strDate)
Dim dteDay
Dim dteMonth
Dim dteYear
Response.Write(strDate)
dteDay = Day(strDate)
dteMonth = Monthname(Month(strDate),True)
dteYear = Year(strDate)
MediumDate = dteDay & "-" & dteMonth & "-" & dteYear
End Function
Function FormatEncode(strInput)
If Not IsNull(strInput) Then
strInput = Server.HTMLEncode(strInput)
strInput = Replace(strInput, "<","<")
strInput = Replace(strInput, ">",">")
End If
FormatEncode = strInput
End Function
%>
Comments on this post
Shadow Wizard
agrees: thanks!
__________________
Last edited by Dr_Rock : May 13th, 2008 at 09:43 PM .
ASP Free Forums Sponsor:
May 13th, 2008, 07:00 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
