The example in this article is an extension of my previous article on how to add or remove HTML elements dynamically using jQuery. The previous article is very popular, however programmers, especially beginners, have requested me to share a post on how to add textbox and button dynamically in jQuery and save the data to an SQL Server table. I’ll show you how this done and I am using Asp.Net Web Method to get the data from the dynamically created textboxes using jQuery Ajax post method.
Scenario
Here’s a simple scenario. I wish to create an SQL Server table dynamically using Asp.Net. I need to pass the table name and column names to the Web Method. Since this is dynamic and I do not have names or actual number of columns, I’ll create some textboxes dynamically using jQuery, which will allow me to enter the columns names for my table. Finally, I’ll create a button using jQuery, to submit the data.
The CSS
The CSS here, is not just for designing. There's one important class that we need to focus, that is, the input class. While dynamically creating the textboxes, I would assign the class name to each textbox. In the jQuery script, I'll then loop through each textbox (using class name) to get the values in it.
<style>
.bt {
font: 13px Verdana;
margin: 5px;
padding: .3em 1em;
color: #000;
border: 1px solid #999;
border: 0 rgba(0,0,0,0);
background: #E6E6E6;
border-radius:2px;
line-height:normal;
white-space:nowrap;
vertical-align:middle;
text-align:center;
cursor:pointer;-webkit-user-drag:none;-webkit-user-select:none;outline:none;
}
.bt:hover {
background-image: linear-gradient(transparent,rgba(0,0,0,.05) 40%,rgba(0,0,0,.1));
}
input[type=text] {
width: 100%;
padding: 2px 10px;
margin: 3px;
display: inline-block;
border: 1px solid #CCC;
box-sizing: border-box;
font: 13px Verdana;
outline: none;
}
</style>
The Markup
In the markup section, I have an input box of type text and a button. I need the button to create the dynamic text boxes. Every click will create a textbox and append the textbox to a container. I’ll create multiple textboxes for my table columns and a single button at the end to submit the values.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
<div>
<div>
<input type="text" id="tbTableName" placeholder="Enter Table Name" />
<input type="button" id="btAdd" value="Add Field" class="bt" />
</div>
<%--THE CONTAINER TO HOLD THE DYNAMICALLY CREATED ELEMENTS.--%>
<div id="main"></div>
</div>
The Script
The below script has the jQuery functions to Create the elements (textbox and button) dynamically and append the elements to a container.
<script>
$(document).ready(function () {
BindControls();
});
function BindControls() {
var itxtCnt = 0; // COUNTER TO SET ELEMENT IDs.
// CREATE A DIV DYNAMICALLY TO SERVE A CONTAINER TO THE ELEMENTS.
var container = $(document.createElement('div')).css({
width: '100%',
clear: 'both',
'margin-top': '10px',
'margin-bottom': '10px'
});
// CREATE THE ELEMENTS.
$('#btAdd').click(function () {
itxtCnt = itxtCnt + 1;
$(container).append('<input type="text"' +
'placeholder="Field Name" class="input" id=tb' + itxtCnt + ' value="" />');
if (itxtCnt == 1) {
var divSubmit = $(document.createElement('div'));
$(divSubmit).append('<input type="button" id="btSubmit" value="Submit" class="bt"' +
'onclick="getTextValue()" />');
}
// ADD EVERY ELEMENT TO THE MAIN CONTAINER.
$('#main').after(container, divSubmit);
});
}
// THE FUNCTION TO EXTRACT VALUES FROM TEXTBOXES AND POST THE VALUES (TO A WEB METHOD) USING AJAX.
var values = new Array();
function getTextValue() {
$('.input').each(function () {
if (this.value != '')
values.push(this.value);
});
if (values != '') {
// NOW CALL THE WEB METHOD WITH THE PARAMETERS USING AJAX.
$.ajax({
type: 'POST',
url: 'default.aspx/loadFields',
data: "{'fields':'" + values + "', 'table': '" + $('#tbTableName').val() + "'}",
dataType: 'json',
headers: { "Content-Type": "application/json" },
success: function (response) {
values = []; // EMPTY THE ARRAY.
alert(response.d);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
}
else { alert("Fields cannot be empty.") }
}
</script>
Web Method (C#)
Now, let's create the Web Method to Save the values in the dynamically created Textboxes.
The Web Method is also special, since I am creating a Table in SQL Server dynamically using data from Dyanamically created input boxes (textboxes).
using System;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
[System.Web.Services.WebMethod]
public static string loadFields(string fields, string table)
{
string sConnString = "Data Source=DNA;Persist Security Info=False;" +
"Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;";
string msg = ""; // A MESSAGE TO BE RETURNED TO THE AJAX CALL.
try
{
// EXTRACT VALUES FROM THE "fields" STRING FOR THE COLUMNS.
int iCnt = 0;
string sColumns = "";
for (iCnt = 0; iCnt <= fields.Split(',').Length - 1; iCnt++)
{
if (string.IsNullOrEmpty(sColumns))
{
sColumns = "[" + fields.Split(',')[iCnt].Replace(" ", "") + "] VARCHAR (100)";
}
else
{
sColumns = sColumns + ", [" + fields.Split(',')[iCnt].Replace(" ", "") + "] VARCHAR (100)";
}
}
using (SqlConnection con = new SqlConnection(sConnString))
{
// CREATE TABLE STRUCTURE USING THE COLUMNS AND TABLE NAME.
string sQuery = null;
sQuery = "IF OBJECT_ID('dbo." + table.Replace(" ", "_") + "', 'U') IS NULL " +
"BEGIN " +
"CREATE TABLE [dbo].[" + table.Replace(" ", "_") + "](" +
"[" + table.Replace(" ", "_") + "_ID" + "] INT IDENTITY(1,1) NOT NULL CONSTRAINT pk" +
table.Replace(" ", "_") + "_ID" + " PRIMARY KEY, " +
"[CreateDate] DATETIME, " + sColumns + ")" +
" END";
using (SqlCommand cmd = new SqlCommand(sQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
msg = "Table created successfuly.";
}
}
}
catch (Exception ex)
{
msg = "There was an error.";
}
finally
{ }
return msg;
}
}
Once the table is created, it will send a confirmation message to the Ajax.
Web Method (VB.Net)
Option Explicit On
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
<System.Web.Services.WebMethod()> _
Public Shared Function loadFields(ByVal fields As String, ByVal table As String) As String
Dim sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
"Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"
Try
' EXTRACT VALUES FROM THE "fields" STRING FOR THE COLUMNS.
Dim iCnt As Integer = 0
Dim sColumns As String = ""
For iCnt = 0 To fields.Split(",").Length - 1
If Trim(sColumns) = "" Then
sColumns = "[" & Replace(fields.Split(",")(iCnt), " ", "") & "] VARCHAR (100)"
Else
sColumns = sColumns & ", [" & Replace(fields.Split(",")(iCnt), " ", "") & "] VARCHAR (100)"
End If
Next
Using con As SqlConnection = New SqlConnection(sConnString)
' CREATE TABLE STRUCTURE USING THE COLUMNS AND TABLE NAME.
Dim sQuery As String
sQuery = "IF OBJECT_ID('dbo." & Replace(table, " ", "_") & "', 'U') IS NULL " & _
"BEGIN " & _
"CREATE TABLE [dbo].[" & Replace(table, " ", "_") & "](" & _
"[" & Replace(table, " ", "_") & "_ID" & "] INT IDENTITY(1,1) NOT NULL CONSTRAINT pk" & _
Replace(table, " ", "_") & "_ID" & " PRIMARY KEY, " & _
"[CreateDate] DATETIME, " & _
sColumns & _
")" & _
" END"
Using cmd As SqlCommand = New SqlCommand(sQuery)
With cmd
.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
loadFields = "Table created successfuly."
End With
End Using
End Using
Catch ex As Exception
loadFields = "There was an error."
Finally
End Try
Return loadFields
End Function
End Class
You can apply similar method using Web API.
Well that’s it. Hope you like this article and its example. Thanks for reading.
.png)
