How to Dynamically Add HTML Elements with jQuery and Save Data to SQL Server

This article extends my previous one on dynamically adding or removing HTML elements with jQuery. Due to popular demand, especially from beginners, I'll show how to dynamically add textboxes and buttons in jQuery and save the data to an SQL Server table using Asp.Net Web Service and 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 {
        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;
        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() {
        let itxtCnt = 0;    // COUNTER TO SET ELEMENT IDs.

        // CREATE A DIV DYNAMICALLY TO SERVE A CONTAINER TO THE ELEMENTS.
        let 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.
    let 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.

← PreviousNext →