Thursday, 16 January 2014

Insert Data Into SQL Server Using jQuery in ASP.Net

In this article I provide a quick overview of how to insert a record into SQL Server using jQuery. You can do it in many ways such as using Generic Handlers and using a WCF service. Here you will see it using jQuery with Ajax in ASP.Net. First we create a database table named "TestTable".


Creating SQL Database Table

This database contains one table named test.
CREATE TABLE [dbo].[TestTable](
      [Name] [varchar](50) NULL,
      [Email] [varchar](100) NULL

)

Designer Source Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>AutoComplete Box with jQuery</title>
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css"
        rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#Button1').click(function () {
                $.ajax({
                    type: 'POST',
                    contentType: "application/json; charset=utf-8",
                    url: 'Default.aspx/InsertMethod',
                    data: "{'Name':'" + document.getElementById('txtUserName').value + "', 'Email':'" + document.getElementById('txtEmail').value + "'}",
                    async: false,
                    success: function (response) {
                        $('#txtUserName').val('');
                        $('#txtEmail').val('');
                        alert("Record Has been Saved in Database");
                    },
                    error: function ()
                    { console.log('there is some error'); }
                });
            });
        });      
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div class="demo">
        <div class="ui-widget">
            <table>
                <tr>
                    <td>
                        <label for="tbAuto">
                            Enter UserName:
                        </label>
                    </td>
                    <td>
                        <asp:TextBox ID="txtUserName" runat="server" ClientIDMode="Static" Width="202px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Email:
                    </td>
                    <td>
                        <asp:TextBox ID="txtEmail" runat="server" ClientIDMode="Static" Width="210px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    </td>
                    <td>
                        <asp:Button ID="Button1" runat="server" Text="Button" ClientIDMode="Static" />
                    </td>
                </tr>
            </table>
        </div>
    </div>
    </form>
</body>
</html>

 Code Behind :

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    [WebMethod]
    public static string InsertMethod(string Name, string Email)
    {
        //List<string> result = new List<string>();
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Gangwar; integrated security=true;");
        {
            SqlCommand cmd = new SqlCommand("Insert into TestTable values('" + Name + "', '" + Email + "')", con);
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return "True";
            }
        }
    }
}

3 comments:

  1. You can use data adapter

    protected void InsertMethod(object sender, EventArgs e)
    {
    SqlConnection cnn = new SqlConnection();
    cnn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["sqlconnection "].ConnectionString;
    cnn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "select * from TableName";
    cmd.Connection = cnn;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds, " TableName ");
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    DataRow drow = ds.Tables["TableName"].NewRow();

    drow["Name"] = TextBox1.Text;
    drow["Email "] = TextBox2.Text;


    ds.Tables["TableName "].Rows.Add(drow);
    da.Update(ds, " TableName ");
    string script = @
    alert('Data have been Saved Successfully');
    ;";
    Page.ClientScript.RegisterStartupScript(this.GetType(), "myJScript1", script);
    }

    ReplyDelete