Please disable your adblock and script blockers to view this page
 

jQuery AJAX JSON example in Asp.net to insert data into sql server database without postback

Introduction: In this article I am going to share how to validate and save data into SQL SERVER database table in asp.net without any post back (asynchronously) using jQuery AJAX JSON and WebMethod.
Validate and store data in sql server database in asp.net using jQuery Ajax
In previous articles i explained how to call Asp.net server side function without any post back using jquery ajax call and Call Asp.net server side function using Javascript and AJAX PageMethods without any PostBack and jQuery to disable mouse right click on images only and jQuery to limit and display number of characters left in multiline textbox and Upload multiple files or images through one fileupload control using jQuery 

Description: jQuery ajax allows us to call server side ASP.NET page methods/functions declared as WebMethod from client side without any postback. Data can be inserted in sql server table by sending ajax request using jQuery to web method containing code to store data in database.
Implementation: Let’s create a sample page to demonstrate the concept.
  • First of all create the Database in Sql Server and Name it "BooksDB" or whatever you want. Create a table (BookDetails) using the script below.

 CREATE TABLE BookDetails(
                [BookId] [int] IDENTITY(1,1) NOT NULL,
                [BookName] [varchar](100) NULL,
                [Author] [varchar](100) NULL,
                [BookTypeId] [int] NULL,
                [Price] [decimal](18, 2) NULL,
 CONSTRAINT [PK_BookDetails] PRIMARY KEY CLUSTERED
(
                [BookId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


  • In Web.config file create the connection string as: 
  <connectionStrings>
    <add name="conStr" connectionString="Data Source=Lalit;Initial Catalog=BooksDB;Integrated Security=True"/>
  </connectionStrings>

Note:  Replace the Data Source and the Initial catalog as per your  application

  • Now create a stored procedure to save book details 

CREATE PROCEDURE Sp_SaveBookDetails
                @BookName VARCHAR(100),
                @Author VARCHAR(100),
                @BookTypeId INT,
                @Price DECIMAL(18,2)
AS
BEGIN 
INSERT INTO BookDetails (BookName,Author,BookTypeId,Price)
VALUES (@BookName,@Author,@BookTypeId,@Price)    
END

HTML Source Code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script type="text/javascript">

        function SaveRecord() {
            //Get control's values
            var bookName = $.trim($('#<%=txtName.ClientID %>').val());
            var author = $.trim($('#<%=txtAuthor.ClientID %>').val());
            var type = $('#<%=ddlType.ClientID %>').val();
            var price = $.trim($('#<%=txtPrice.ClientID %>').val());

            var msg = "";
            //check for validation
            if (bookName == '') {
                msg += "<li>Please enter book name</li>";
            }
            if (author == '') {
                msg += "<li>Please enter author name</li>";
            }
            if (type == 0) {
                msg += "<li>Please select book type</li>";
            }
            if (price == '') {
                msg += "<li>Please enter book price</li>";
            }

            if (msg.length == 0) {
                //Jquery ajax call to server side method
                $.ajax({
                    type: "POST",
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    //Url is the path of our web method (Page name/function name)
                    url: "MyPageName.aspx/SaveBookDetails",
                    //Pass paramenters to the server side function
                    data: "{'BookName':'" + bookName + "', 'Author':'" + author + "','BookTypeId':'" + type + "','Price':'" + price + "'}",
                    success: function (response) {
                        //Success or failure message e.g. Record saved or not saved successfully
                        if (response.d == true) {
                            //Set message
                            $('#dvResult').text("Record saved successfully");
                            //Reset controls                          
                            $('#txtName').val('');
                            $('#txtAuthor').val('');
                            $('#ddlType').val("0");
                            $('#txtPrice').val('');                         
                        }
                        else {
                            $('#dvResult').text("Record could't be saved");
                        }
                        //Fade Out to disappear message after 6 seconds
                        $('#dvResult').fadeOut(6000);
                    },
                      error: function (xhr, textStatus, error) {
                          //Show error message(if occured)
                          $('#dvResult').text("Error: " +  error);
                      }
                  });
            }
            else {
                  //Validation failure message
                $('#dvResult').html('');
                $('#dvResult').html(msg);
            }
            $('#dvResult').fadeIn();
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table>
                <tr>
                    <td>Book Name: </td>
                    <td>
                        <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Author: </td>
                    <td>
                        <asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Type: </td>
                    <td>
                        <asp:DropDownList ID="ddlType" runat="server">
                            <asp:ListItem Text="--Select--" Value="0"></asp:ListItem>
                            <asp:ListItem Text="MVC" Value="1"></asp:ListItem>
                            <asp:ListItem Text="ASP.NET" Value="2"></asp:ListItem>
                            <asp:ListItem Text="SQL SERVER" Value="3"></asp:ListItem>
                        </asp:DropDownList></td>
                </tr>
                <tr>
                    <td>Price: </td>
                    <td>
                        <asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td></td>
                    <td>
                        <button type="submit" onclick="SaveRecord();return false">Submit</button>
                    </td>
                </tr>
                <tr>
                    <td></td>
                    <td>
                        <div id="dvResult"></div>
                    </td>
                </tr> 
            </table>
        </div>
    </form>
</body>
</html>

Asp.Net C# Code to validate and store data in sql server database table using jQuery Ajax

First add following namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;

then write the code as:

[WebMethod]
        public static bool SaveBookDetails(string BookName, string Author, Int32 BookTypeId, decimal Price)
        {
            bool status;

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("Sp_SaveBookDetails", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@BookName", BookName);
                    cmd.Parameters.AddWithValue("@Author", Author);
                    cmd.Parameters.AddWithValue("@BookTypeId", BookTypeId);
                    cmd.Parameters.AddWithValue("@Price", Price);
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    Int32 retVal = cmd.ExecuteNonQuery();
                    if (retVal > 0)
                    {
                        status = true;
                    }
                    else
                    {
                        status = false;
                    }
                    return status;
                }
            }
        }

Asp.Net VB Code to validate and store data in sql server database table using jQuery Ajax

First add following namespaces

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services

Then write the code as:

<WebMethod> _
    Public Shared Function SaveBookDetails(BookName As String, Author As String, BookTypeId As Int32, Price As Decimal) As Boolean
        Dim status As Boolean

        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
            Using cmd As New SqlCommand("Sp_SaveBookDetails", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@BookName", BookName)
                cmd.Parameters.AddWithValue("@Author", Author)
                cmd.Parameters.AddWithValue("@BookTypeId", BookTypeId)
                cmd.Parameters.AddWithValue("@Price", Price)
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
                Dim retVal As Int32 = cmd.ExecuteNonQuery()
                If retVal > 0 Then
                    status = True
                Else
                    status = False
                End If
                Return status
            End Using
        End Using

    End Function

Now over to you:
" I hope you have got how to insert data into sql server database without postback using jQuery AJAX call and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 
Previous
Next Post »

2 comments

Click here for comments
December 01, 2015 ×

very nice tutorial

Reply
avatar
December 12, 2015 ×

Thanks mathesh for your valuable comment. Stay connected and keep reading for more useful updates..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon