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.
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:
2 comments
Click here for commentsvery nice tutorial
ReplyThanks mathesh for your valuable comment. Stay connected and keep reading for more useful updates..:)
ReplyIf 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..