Insert Data Into SQL Database Using jQuery Ajax

We know that jQuery is a very important part of a JavaScript. jQuery is an open-source library for client-side programming or scripting.

Now I will explain here step-by-step how to insert text data into a database.

Step 1

Go to the database side.

  1. Create a PersonData table in the SQL database as in the following:
    1. create table PersonData  
    2. (  
    3. Name varchar(20),  
    4. LName varchar(20)  
    5. )  
  2. Create a procedure to insert a data record into a table as in the following:
    1. create procedure sp_PersonData  
    2. @Name varchar(20),  
    3. @LName varchar(20)  
    4. as  
    5. begin  
    6. set nocount off;  
    7. insert into PersonData (Name,LName)values(@Name,@LName)  
    8. End  

Step 2

Go to the Visual Studio project side.

I. Go to the project in the Solution Explorer.

Select the project.
Right-click on it and select Add New Item.
Select the page as in the following figure.

Add Design Page
Figure 1: Add Design Page

II. Now go to the page design side.

Add a TextBox control and Button control as in the following:

Design Page
Figure 2: Design Page

III. Finally write scripting on the design page for the TextBox data insert as in the following:

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.Master" AutoEventWireup="true" CodeBehind="DataInsertUseJQuery.aspx.cs" Inherits="Test_WebApplication.UI.DataInsertUseJQuery" %>  
  2. <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">  
  3. </asp:Content>  
  4. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">  
  5.   
  6. <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>  
  7.   
  8. <script type="text/javascript">  
  9.   
  10.     function SavePersonRecord() {  
  11.           
  12.         var Name = $.trim($('#<%=txtName.ClientID %>').val());  
  13.         var LName = $.trim($('#<%=txtLastName.ClientID %>').val());  
  14.   
  15.         var Messege = "";  
  16.          
  17.         if (Name == '') {  
  18.             Messege = "Can not Blank Name";  
  19.         }  
  20.           
  21.         if (LName == '') {  
  22.             Messege += "Can not Blank Last Name";  
  23.         }  
  24.   
  25.         if (Messege.length == 0) {  
  26.       
  27.             $.ajax({  
  28.                 type: "POST",  
  29.                 dataType: "json",  
  30.                 contentType: "application/json; charset=utf-8",  
  31.                 url: "DataInsertUseJQuery.aspx/InsertPersonRecord",  
  32.                 data: "{'Name':'" + Name + "', 'LName':'" + LName + "'}",  
  33.                 success: function (Record) {  
  34.                        
  35.                         $('#txtName').val();  
  36.                         $('#txtLastName').val();  
  37.   
  38.   
  39.                     if (Record.d == true) {  
  40.                   
  41.                         $('#Result').text("Your Record insert");  
  42.                     }  
  43.                     else {  
  44.                         $('#Result').text("Your Record Not Insert");  
  45.                     }  
  46.   
  47.                 },  
  48.                 Error: function (textMsg) {  
  49.                       
  50.                     $('#Result').text("Error: " + Error);  
  51.                 }  
  52.             });  
  53.         }  
  54.         else {             
  55.             $('#Result').html('');  
  56.             $('#Result').html(Messege);  
  57.         }  
  58.         $('#Result').fadeIn();  
  59.     }  
  60.     </script>  
  61.   
  62. <fieldset style="width: 250px"><legend> Data insert Use jQuery </legend>  
  63. <h3 id="Result"></h3>  
  64. <div>  
  65.     <asp:Table runat="server" >  
  66.         <asp:TableRow>  
  67.             <asp:TableCell>Name </asp:TableCell><asp:TableCell><asp:TextBox ID="txtName" runat="server" ></asp:TextBox></asp:TableCell>  
  68.         </asp:TableRow>  
  69.      
  70.        <asp:TableRow>  
  71.             <asp:TableCell>Last Name</asp:TableCell><asp:TableCell><asp:TextBox ID="txtLastName" runat="server" ></asp:TextBox></asp:TableCell>  
  72.         </asp:TableRow>  
  73.   
  74.          <asp:TableRow>  
  75.             <asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID="btnInsertRecord" runat="server" Text="Save"  OnClientClick="SavePersonRecord(); return false"/></asp:TableCell>  
  76.         </asp:TableRow>  
  77.   
  78.      </asp:Table>  
  79. </div>  
  80. </fieldset>  
  81. </asp:Content>  
IV. Now go to the page code side.

Here we will add:
  1. using System.Configuration;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Web.Services;  
For the database connection string use the web method and a add connection string to the project's web.config file as in the following:
  1. <connectionStrings>  
  2.     <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>  
  3.   
  4.     <add name="Pratical_testConnectionString" connectionString="Data Source=RAKESH-PC;Initial Catalog=Pratical_test;User ID=sa" providerName="System.Data.SqlClient"/>  
  5.   </connectionStrings>  
And finally insert code into the .aspx.cs side as in the following:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Configuration;  
  8. using System.Data;  
  9. using System.Data.SqlClient;  
  10. using System.Web.Services;  
  11.   
  12.   
  13. namespace Test_WebApplication.UI  
  14. {  
  15.     public partial class DataInsertUseJQuery : System.Web.UI.Page  
  16.     {  
  17.         string conString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
  18.         protected void Page_Load(object sender, EventArgs e)  
  19.         {  
  20.   
  21.         }  
  22.         [WebMethod]  
  23.         public static bool InsertPersonRecord(string Name, string LName)  
  24.         {  
  25.             bool InsertData;  
  26.             using (SqlConnection con = new SqlConnection(conString)  
  27.             {  
  28.                 using (SqlCommand cmd = new SqlCommand("sp_PersonData", con))  
  29.                 {  
  30.                     cmd.CommandType = CommandType.StoredProcedure;  
  31.   
  32.                     cmd.Parameters.AddWithValue("@Name", Name);  
  33.                     cmd.Parameters.AddWithValue("@LName", LName);  
  34.   
  35.                     if (con.State == ConnectionState.Closed)  
  36.                     {  
  37.                         con.Open();  
  38.                     }  
  39.                     int Result = cmd.ExecuteNonQuery();  
  40.                     if (Result > 0)  
  41.                     {  
  42.                         InsertData = true;  
  43.                     }  
  44.                     else  
  45.                     {  
  46.                         InsertData = false;  
  47.                     }  
  48.                     return InsertData;  
  49.                 }  
  50.             }  
  51.         }  
  52.     }  
  53. }  
V. Now you will run the page on the browser side by pressing the F5 button. You will finally see your design page as in the following:

Browser side Design Page
Figure 3: Browser side Design Page

Now fill in the record in the TextBox and click the Save button to save the record to the database.

Save Data
Figure 4: Save Data

Check in your database for the last inserted record as in the following:

check Record in Database
Figure 5: Check Record in Database

Finally now you understand how to insert a record with jQuery ajax.