ASP.net Dynamic User Form Creation from Database for Insert and Update
Here was my requirement, I wanted to have a mastertable as shown below where i can put form information with Labels, TextBox, DropDownList, Checkbox, Date Fields etc and just call the formid and all the controls of form will be rendered on screen.
Next important step was to insert the data in designated field of multiple tables. That too needs to be controlled from master table.
Another step was to update the data in designated multiple tables.
You can design the master table field_master for forms in sqlserver, mysql, sqlite etc.
- Printorder is the order of rendering the control on Form
- formid represent which form you want to render. You can have so many forms in the table identified by this field.
- field_name is the name to be displayed as label for control like Name
- field_type is the type of control to be rendered like text for textbox, label for label, dropdown for List etc.
- required Y or N for field is required or not
- default_values Here you can enter values like Vinod for name, If you want value to come from some database table then Use this Syntax TABLE names VALUE name so it will load value of name from names table.
for radiobutton and drop down you can use comma separated values like orange, apple
If you want value to come from some database table then Use this Syntax TABLE names Text name VALUE empno so it will load value of name from names table.
- table_name and table_fname are the name of table and field name where value will be inserted or updated on form submission.
Here is a sample for testform with all possible controls and destination where data will be saved.
So I created a class file in App_code folder to call and render control on any webform and also update it.
How we will implement it.
1. We will create a panel on web form to Hold form Controls
2. We will create dynamic placeHolder Control and add form controls as per the master table
3. Placeholder will be added to Panel
4. On Submit button we will call Insert or Update function which will do validation and insert/update data on destination tables.
Create a class file and make a public function to Create Controls
Public Shared Function CreateDynamicControls(ByVal formid As String, Optional ByVal loadForUpdate As Boolean = False, Optional ByVal MatchPrimaryKey As String = "Needed if you want to get form for updation with prefilled values") As PlaceHolder
'###############################################################################
'"###################### Create Fresh Form ######################################
' call just function
' pnlForm.Controls.Add(CreateDynamicControls("addproj"))
' Supply formid in parameter and all controls will be returned in place holder
' Add this placeholder to any panel in your web page
'###############################################################################
'##################### Updation Form ###########################################
' second parameter loadforupdate should be true and MatchPrimaryKey should be project_code = '157'
' pnlForm.Controls.Add(CreateDynamicControls("addproj", True, "project_code = 151"))
' It will retrieve old values using primary key and prefill the form for update.
' if you have multiple tables in Form then all table must have same key for retrival
' #############################################################################
' #############################################################################
' ################## InsertFormData #########################################
' Dim ret = InsertForm("addproj", PlaceHolder1)
' #############################################################################
' #################### Update Form Data ######################################
' Dim ret = updateForm("addproj", PlaceHolder1, "project_code = '151'")
' if you have multiple tables in Form and wand to get updated multiple tables
' then all table must have same key for updation
' ##############################################################################
'
Try
Dim dt = getDBTable("select field_id, field_name,field_type,default_values, required, table_name, table_fname from Field_master where formid = '" & formid & "' order by printorder")
' dt = CustomFields()
Dim PlaceHolder1 As PlaceHolder = New PlaceHolder()
PlaceHolder1.ID = "PlaceHolder1"
If dt.Rows.Count > 0 Then
For i As Int32 = 0 To dt.Rows.Count - 1
Dim tr As HtmlGenericControl = New HtmlGenericControl("tr")
Dim td As HtmlGenericControl = New HtmlGenericControl("td")
Dim td1 As HtmlGenericControl = New HtmlGenericControl("td")
Dim FieldName As String = Convert.ToString(dt.Rows(i)("field_name"))
Dim FieldID As String = Convert.ToString(dt.Rows(i)("field_id"))
Dim FieldType As String = Convert.ToString(dt.Rows(i)("field_type"))
Dim FieldValue As String = Convert.ToString(dt.Rows(i)("default_values"))
Dim required As String = Convert.ToString(dt.Rows(i)("required"))
Dim table As String = Convert.ToString(dt.Rows(i)("table_name"))
Dim field As String = Convert.ToString(dt.Rows(i)("table_fname"))
Dim star = "*"
If Not required.Contains("Y") Then star = ""
Dim lbcustomename As Label = New Label()
lbcustomename.Text = FieldName & star
lbcustomename.ID = "lb" & FieldID
lbcustomename.CssClass = "form-label"
td.Controls.Add(lbcustomename)
tr.Controls.Add(td)
If FieldType.ToLower().Trim() = "label" Then
Dim lb As Label = New Label()
lb.ID = "lbl" & FieldID
'If FieldValue.StartsWith("KEY") Then
' lb.Text = 156 ' getDBsingle("select max(" & field & ") + 1 from " & table)
'Else
lb.Text = FieldValue
' End If
lb.CssClass = "form-control" 'mt-20
td1.Controls.Add(lb)
PlaceHolder1.Controls.Add(New LiteralControl("<br />"))
ElseIf FieldType.ToLower().Trim() = "text" Or FieldType.ToLower().Trim() = "number" Or FieldType.ToLower().Trim() = "date" Then
Dim txtcustombox As TextBox = New TextBox()
txtcustombox.ID = "txt" & FieldID
If loadForUpdate Then
' update form load with key values
If FieldType.ToLower().Trim() = "date" Then field = "DATE_FORMAT(" & field & ", '%d.%m.%Y')"
txtcustombox.Text = getDBsingle("select " & field & " from " & table & " where " & MatchPrimaryKey & " limit 1")
Else
''' Fresh form load with default values
txtcustombox.Text = FieldValue
End If
txtcustombox.CssClass = "form-control" 'mt-20
'If FieldType.ToLower().Trim() = "date" Then txtcustombox.CssClass = "form-control vindateClass" ''needed for jquery calander but now using ajax
td1.Controls.Add(txtcustombox)
If FieldType.ToLower().Trim() = "number" Then '' add validators for number field
Dim comparevalid = New CompareValidator
comparevalid.ID = "comp" & FieldID
comparevalid.ControlToValidate = "txt" & FieldID
comparevalid.Type = ValidationDataType.[Double]
comparevalid.Operator = ValidationCompareOperator.DataTypeCheck
comparevalid.ErrorMessage = "Only Numeric Value Allowed"
td1.Controls.Add(comparevalid)
End If
If FieldType.ToLower().Trim() = "date" Then '' add calander for date field
Dim calander = New AjaxControlToolkit.CalendarExtender
calander.ID = "cal" & FieldID
calander.TargetControlID = "txt" & FieldID
calander.Format = "dd.MM.yyyy"
td1.Controls.Add(calander)
End If
PlaceHolder1.Controls.Add(New LiteralControl("<br />"))
ElseIf FieldType.ToLower().Trim() = "checkbox" Then
Dim chkbox As CheckBoxList = New CheckBoxList()
chkbox.ID = "cbl" & FieldID
If FieldValue.StartsWith("TABLE") Then
Dim tabledetail() = FieldValue.Split(" ")
'table name
Dim tablename = tabledetail(1)
Dim textfield = tabledetail(3)
Dim valuefield = tabledetail(5)
chkbox.DataTextField = textfield
chkbox.DataValueField = valuefield
Dim q = "select " & textfield & ", " & valuefield & " from " & tablename
' divMsg.InnerHtml = q
chkbox.DataSource = getDBTable(q)
chkbox.DataBind()
ElseIf FieldValue <> String.Empty Then
''split the comma seperated value
Dim items() = FieldValue.Split(",")
For Each item In items
chkbox.Items.Add(New ListItem(item, item))
Next
'Else
' divMsg.InnerHtml = "No values"
End If
If loadForUpdate Then
' update form load with key values
Try
chkbox.SelectedValue = getDBsingle("select " & field & " from " & table & " where " & MatchPrimaryKey & " limit 1")
Catch ex2 As Exception
End Try
End If
td1.Controls.Add(chkbox)
PlaceHolder1.Controls.Add(New LiteralControl("<br />"))
ElseIf FieldType.ToLower().Trim() = "radiobutton" Then
Dim rbnlst As RadioButtonList = New RadioButtonList()
rbnlst.ID = "rbl" & FieldID
If FieldValue.StartsWith("TABLE") Then
Dim tabledetail() = FieldValue.Split(" ")
'table name
Dim tablename = tabledetail(1)
Dim textfield = tabledetail(3)
Dim valuefield = tabledetail(5)
rbnlst.DataTextField = textfield
rbnlst.DataValueField = valuefield
Dim q = "select " & textfield & ", " & valuefield & " from " & tablename
' divMsg.InnerHtml = q
rbnlst.DataSource = getDBTable(q)
rbnlst.DataBind()
ElseIf FieldValue <> String.Empty Then
''split the comma seperated value
Dim items() = FieldValue.Split(",")
For Each item In items
rbnlst.Items.Add(New ListItem(item, item))
Next
'Else
' divMsg.InnerHtml = "No values"
End If
If loadForUpdate Then
' update form load with key values
Try
rbnlst.SelectedValue = getDBsingle("select " & field & " from " & table & " where " & MatchPrimaryKey & " limit 1")
Catch ex2 As Exception
End Try
End If
rbnlst.RepeatDirection = RepeatDirection.Horizontal
rbnlst.RepeatColumns = 5
td1.Controls.Add(rbnlst)
PlaceHolder1.Controls.Add(New LiteralControl("<br />"))
ElseIf FieldType.ToLower().Trim() = "dropdown" Then
Dim ddllst As DropDownList = New DropDownList()
ddllst.ID = "ddl" & FieldID
''check if default value comes from table
If FieldValue.StartsWith("TABLE") Then
Dim tabledetail() = FieldValue.Split(" ")
'table name
Dim tablename = tabledetail(1)
Dim textfield = tabledetail(3)
Dim valuefield = tabledetail(5)
ddllst.DataTextField = textfield
ddllst.DataValueField = valuefield
ddllst.Style.Add("display", "block")
Dim q = "select " & textfield & ", " & valuefield & " from " & tablename
' divMsg.InnerHtml = q
ddllst.DataSource = getDBTable(q)
ddllst.DataBind()
ElseIf FieldValue <> String.Empty Then
''split the comma seperated value
Dim items() = FieldValue.Split(",")
For Each item In items
ddllst.Items.Add(New ListItem(item, item))
Next
End If
If loadForUpdate Then
' update form load with key values
Try
ddllst.SelectedValue = getDBsingle("select " & field & " from " & table & " where " & MatchPrimaryKey & " limit 1")
Catch ex2 As Exception
End Try
End If
td1.Controls.Add(ddllst)
PlaceHolder1.Controls.Add(New LiteralControl("<br />"))
End If
tr.Controls.Add(td1)
PlaceHolder1.Controls.Add(tr)
'If i = dt.Rows.Count - 1 Then
' tr = New HtmlGenericControl("tr")
' td = New HtmlGenericControl("td")
' Dim btnSubmit As Button = New Button()
' btnSubmit.ID = "btnSubmit"
' btnSubmit.CssClass = "primary-btn submit-btn d-inline-flex align-items-center mr-10"
' ' btnSubmit.Style.Add("background-color", "-moz-linear-gradient(0deg, #3e69fe 0%, #4cd4e3 100%)")
' ' btnSubmit.CommandArgument = i.ToString()
' AddHandler btnSubmit.Click, AddressOf Me.btnsubmit_Click
' 'btnSubmit.OnClientClick = "return alert ('hi');"
' 'btnSubmit.Attributes.Add("runat", "server")
' btnSubmit.Text = "Submit"
' td.Controls.Add(btnSubmit)
' td.Attributes.Add("Colspan", "2")
' td.Attributes.Add("style", "text-align:center;")
' tr.Controls.Add(td)
' PlaceHolder1.Controls.Add(tr)
'End If
Next
End If
Return PlaceHolder1
Catch ex As Exception
Dim PlaceHolder1 As PlaceHolder = New PlaceHolder()
PlaceHolder1.ID = "PlaceHolder1"
PlaceHolder1.Controls.Add(New LiteralControl(" <h3>Error </h3> " & ex.Message))
Return PlaceHolder1
End Try
End Function
Make another function to Insert Data
Public Shared Function InsertForm(ByVal formid As String, ByVal PlaceHolder1 As PlaceHolder) As String
'' get tables in order to make insert query
'###############################################################################
'"###################### Create Fresh Form ######################################
' call just function
' pnlForm.Controls.Add(CreateDynamicControls("addproj"))
' Supply formid in parameter and all controls will be returned in place holder
' Add this placeholder to any panel in your web page
'###############################################################################
'##################### Updation Form ###########################################
' second parameter loadforupdate should be true and MatchPrimaryKey should be project_code = '157'
' pnlForm.Controls.Add(CreateDynamicControls("addproj", True, "project_code = 151"))
' It will retrieve old values using primary key and prefill the form for update.
' if you have multiple tables in Form then all table must have same key for retrival
' #############################################################################
' #############################################################################
' ################## InsertFormData #########################################
' Dim ret = InsertForm("addproj", PlaceHolder1)
' #############################################################################
' #################### Update Form Data ######################################
' Dim ret = updateForm("addproj", PlaceHolder1, "project_code = '151'")
' if you have multiple tables in Form and wand to get updated multiple tables
' then all table must have same key for updation
' ##############################################################################
Try
Dim mydt = getDBTable("select field_id,field_name,field_type,default_values, required, table_name, table_fname from Field_master where formid = '" & formid & "' and not field_type = 'label' order by table_name")
Dim msg = ""
Dim insertStmt As New System.Collections.Generic.List(Of String)() ' "insert into currTable values(table_fname,table_fname)"
Dim tables As New System.Collections.Generic.List(Of String)()
Dim currTable = ""
For Each r In mydt.Rows
If currTable <> r("table_name") Then
currTable = r("table_name")
Dim foundRows() As System.Data.DataRow
foundRows = mydt.Select("table_name = '" & currTable & "'")
Dim commaSeparatedFields As String = String.Join(",", foundRows.AsEnumerable().[Select](Function(x) x.Field(Of String)("table_fname").ToString()).ToArray())
insertStmt.Add("insert into " & currTable & " (" & commaSeparatedFields & ") ")
tables.Add(currTable)
End If
Next
'' now append value fields
Dim i = 0
For Each currTable In tables
Dim values = ""
Dim foundRows() As System.Data.DataRow
foundRows = mydt.Select("table_name = '" & currTable & "'")
For Each r In foundRows
Dim FieldName = Convert.ToString(r("field_name"))
Dim FieldID As String = Convert.ToString(r("field_id"))
Dim required As String = Convert.ToString(r("required"))
Dim FieldType = Convert.ToString(r("field_type"))
If FieldType.ToLower().Trim() = "label" Then
Dim lb As Label = CType(PlaceHolder1.FindControl("lbl" & FieldID), Label)
If lb IsNot Nothing Then
values = values & " '" & lb.Text.Replace("'", "") & "',"
End If
End If
If FieldType.ToLower().Trim() = "text" Or FieldType.ToLower().Trim() = "number" Or FieldType.ToLower().Trim() = "date" Then
Dim txtbox As TextBox = CType(PlaceHolder1.FindControl("txt" & FieldID), TextBox)
If txtbox IsNot Nothing Then
If checkForSQLInjection(txtbox.Text) Then Return "Invalid input found in " & FieldName
If required = "Y" And String.IsNullOrEmpty(txtbox.Text) Then Return "Required Field: " & FieldName
If FieldType.ToLower().Trim() = "number" Then If Not isNumber(txtbox.Text) Then Return "Value must be numeric in " & FieldName
If FieldType.ToLower().Trim() = "date" Then
Dim vin_dt = DateTime.ParseExact(txtbox.Text, "dd.MM.yyyy", Nothing)
values = values & " '" & vin_dt.ToString("yyyy-MM-dd") & "',"
Else
values = values & " '" & txtbox.Text.Replace("'", "") & "',"
End If
End If
End If
If FieldType.ToLower().Trim() = "dropdown" Then
Dim ddl As DropDownList = CType(PlaceHolder1.FindControl("ddl" & FieldID), DropDownList)
If ddl IsNot Nothing Then
If required = "Y" And ddl.SelectedIndex < 0 Then Return "Required Field: " & FieldName
values = values & " '" & ddl.SelectedValue & "',"
End If
End If
If FieldType.ToLower().Trim() = "checkbox" Then
Dim cbl As CheckBoxList = CType(PlaceHolder1.FindControl("cbl" & FieldID), CheckBoxList)
If cbl IsNot Nothing Then
If required = "Y" And cbl.SelectedIndex < 0 Then Return "Required Field: " & FieldName
values = values & " '" & cbl.SelectedValue & "',"
End If
End If
If FieldType.ToLower().Trim() = "radiobutton" Then
Dim rbl As RadioButtonList = CType(PlaceHolder1.FindControl("rbl" & FieldID), RadioButtonList)
If rbl IsNot Nothing Then
If required = "Y" And rbl.SelectedIndex < 0 Then Return "Required Field: " & FieldName
values = values & " '" & rbl.SelectedValue & "',"
End If
End If
Next
''trim lastcomma
values = values.TrimEnd(",")
'' append values in insert stmt
insertStmt(i) = insertStmt(i) & " value (" & values & ") "
i = i + 1
Next
For Each q In insertStmt
' divMsg.InnerHtml = divMsg.InnerHtml & q
msg = msg & "Executing query " '& q & " <br/>"
Dim ret = executeDB(q)
If ret = "ok" Then
msg = msg & "Success " & " <br/>"
Else
msg = msg & "Error in query " & q & " <br/>"
End If
Next
Return msg
Catch ex As Exception
Return "Error " & ex.Message
End Try
End Function
Make another function to update data
Public Shared Function isNumber(ByVal val As String) As Boolean
Dim d As Double
Try
Double.TryParse(val, d)
Return True
Catch ex As Exception
Return False
End Try
End Function
Public Shared Function updateForm(ByVal formid As String, ByVal PlaceHolder1 As PlaceHolder, ByVal MatchPrimaryKey As String) As String
''' MatchPrimaryKey should be same in multiple tables for same form
'''
'###############################################################################
'"###################### Create Fresh Form ######################################
' call just function
' pnlForm.Controls.Add(CreateDynamicControls("addproj"))
' Supply formid in parameter and all controls will be returned in place holder
' Add this placeholder to any panel in your web page
'###############################################################################
'##################### Updation Form ###########################################
' second parameter loadforupdate should be true and MatchPrimaryKey should be project_code = '157'
' pnlForm.Controls.Add(CreateDynamicControls("addproj", True, "project_code = 151"))
' It will retrieve old values using primary key and prefill the form for update.
' if you have multiple tables in Form then all table must have same key for retrival
' #############################################################################
' #############################################################################
' ################## InsertFormData #########################################
' Dim ret = InsertForm("addproj", PlaceHolder1)
' #############################################################################
' #################### Update Form Data ######################################
' Dim ret = updateForm("addproj", PlaceHolder1, "project_code = '151'")
' if you have multiple tables in Form and wand to get updated multiple tables
' then all table must have same key for updation
' ##############################################################################
'' get tables in order to make update query
Try
Dim mydt = getDBTable("select field_id,field_name,field_type,default_values, required, table_name, table_fname from Field_master where formid = '" & formid & "' and not field_type = 'label' order by table_name")
Dim msg = ""
Dim updateStmt As New System.Collections.Generic.List(Of String)() ' "update currTable set table_fname = '' ,table_fname = '' where MatchPrimaryKey"
Dim tables As New System.Collections.Generic.List(Of String)()
Dim currTable = ""
For Each r In mydt.Rows
If currTable <> r("table_name") Then
currTable = r("table_name")
tables.Add(currTable)
End If
Next
'' now append value fields
For Each currTable In tables
Dim values = ""
Dim foundRows() As System.Data.DataRow
foundRows = mydt.Select("table_name = '" & currTable & "'")
For Each r In foundRows
Dim FieldName = Convert.ToString(r("field_name"))
Dim FieldID As String = Convert.ToString(r("field_id"))
Dim required As String = Convert.ToString(r("required"))
Dim FieldType = Convert.ToString(r("field_type"))
Dim table_fname As String = Convert.ToString(r("table_fname"))
If FieldType.ToLower().Trim() = "label" Then
Dim lb As Label = CType(PlaceHolder1.FindControl("lbl" & FieldID), Label)
If lb IsNot Nothing Then
values = values & " " & table_fname & " ='" & lb.Text.Replace("'", "") & "',"
End If
End If
If FieldType.ToLower().Trim() = "text" Or FieldType.ToLower().Trim() = "number" Or FieldType.ToLower().Trim() = "date" Then
Dim txtbox As TextBox = CType(PlaceHolder1.FindControl("txt" & FieldID), TextBox)
If txtbox IsNot Nothing Then
If checkForSQLInjection(txtbox.Text) Then Return "Invalid input found in " & FieldName
If required = "Y" And String.IsNullOrEmpty(txtbox.Text) Then Return "Required Field: " & FieldName
If FieldType.ToLower().Trim() = "number" Then If Not isNumber(txtbox.Text) Then Return "Value must be numeric in " & FieldName
If FieldType.ToLower().Trim() = "date" Then
Dim vin_dt = DateTime.ParseExact(txtbox.Text, "dd.MM.yyyy", Nothing)
values = values & " " & table_fname & " ='" & vin_dt.ToString("yyyy-MM-dd") & "',"
Else
values = values & " " & table_fname & " ='" & txtbox.Text.Replace("'", "") & "',"
End If
End If
End If
If FieldType.ToLower().Trim() = "dropdown" Then
Dim ddl As DropDownList = CType(PlaceHolder1.FindControl("ddl" & FieldID), DropDownList)
If ddl IsNot Nothing Then
If required = "Y" And ddl.SelectedIndex < 0 Then Return "Required Field: " & FieldName
values = values & " " & table_fname & " ='" & ddl.SelectedValue & "',"
End If
End If
If FieldType.ToLower().Trim() = "checkbox" Then
Dim cbl As CheckBoxList = CType(PlaceHolder1.FindControl("cbl" & FieldID), CheckBoxList)
If cbl IsNot Nothing Then
If required = "Y" And cbl.SelectedIndex < 0 Then Return "Required Field: " & FieldName
values = values & " " & table_fname & " ='" & cbl.SelectedValue & "',"
End If
End If
If FieldType.ToLower().Trim() = "radiobutton" Then
Dim rbl As RadioButtonList = CType(PlaceHolder1.FindControl("rbl" & FieldID), RadioButtonList)
If rbl IsNot Nothing Then
If required = "Y" And rbl.SelectedIndex < 0 Then Return "Required Field: " & FieldName
values = values & " " & table_fname & " ='" & rbl.SelectedValue & "',"
End If
End If
Next
''trim lastcomma
values = values.TrimEnd(",")
'' append values in insert stmt
updateStmt.Add(" update " & currTable & " set " & values & " where " & MatchPrimaryKey)
Next
For Each q In updateStmt
' divMsg.InnerHtml = divMsg.InnerHtml & q
msg = msg & "Executing query " '& q & " <br/>"
Dim ret = executeDB(q)
If ret = "ok" Then
msg = msg & "Success " & " <br/>"
Else
msg = msg & "Error in query " & q & " <br/>"
End If
Next
Return msg
Catch ex As Exception
Return "Error " & ex.Message
End Try
End Function
How to create dynamic form
In your aspx page create a Panel with submit button as follows:
How to Create form for Insert new Records
In your page load without postback Call the function like this
Private Sub perf_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'''Postback code
End If
pnlForm.Controls.Add(CreateDynamicControls("testform"))
End Sub
This will create a new blank form for Inserting the Data like this
How to Insert on Submit
Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.ClickDim PlaceHolder1 As PlaceHolder = CType(pnlForm.FindControl("PlaceHolder1"), PlaceHolder)
Dim ret = InsertForm("testform", PlaceHolder1)
divInfo.InnerHtml = getMessage(ret, "info") 'success alert info warning
End Sub
Create form for Updation:
For this you need to supply the primary key for preloading the form data. We send a parameter true to indicate updation form with primary key filter as shownPrivate Sub perf_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'''Postback code
End If
pnlForm.Controls.Add(CreateDynamicControls("testform", True, "project_code = '151'"))
End Sub
How to Update on Submit
Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.ClickDim PlaceHolder1 As PlaceHolder = CType(pnlForm.FindControl("PlaceHolder1"), PlaceHolder)
Dim ret = updateForm("testform", PlaceHolder1, "project_code = '151'")
divInfo.InnerHtml = getMessage(ret, "info") 'success alert info warning
End Sub
This way you can make any number of form without much coding
- Written By
Vinod Kotiya
Comments