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

Home » ASP.NET Projects » jQuery AJAX JSON example in Asp.net to insert data into sql server database without postback
ASP.NET Projects No Comments

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

Table Schema:-

  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]

Stored Procedure for inserting Data:-


 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

Complete ASPX Source Codes:-


 <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>

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;
                }
            }
        }

 

loading...

LEAVE A COMMENT