Tuesday, February 21

Insert Data in Sqlserver DataBase using JSON, ASP.NET Web services and jQuery


Today we will look at how to create a form which will take input from user and insert it into the database using jQuery AJAX call.


Record in Database:



Web service code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;


[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
////Make Sure The above Red Marked Line is uncomment or not if not then uncomment it otherwise you dont get anything

public class WebService : System.Web.Services.WebService {


    public WebService () {


        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }


    [WebMethod]
    public string HelloWorld() {
        return "Hello World";
    }


    string constr = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    int rowsInserted = 0;
    [WebMethod]
    public string ReceiveWebService(string name, string email, string phone, string address)
    {
        using (SqlConnection conn = new SqlConnection(constr))
        {
            string sql = string.Format(@"INSERT INTO [SampleTestDB]([Name] , [Email] , [Phone] ,[Address]) VALUES ('{0}','{1}','{2}','{3}')", name, email, phone, address);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            conn.Open();
            rowsInserted = cmd.ExecuteNonQuery();
            conn.Close();
            cmd = null;
        }
        return string.Format("Thank you ,{0} number of rows inserted!", rowsInserted);
    }
    
    
}

ASPX Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="json.aspx.cs" Inherits="json" %>

<!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></title>
<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
   
<script type="text/javascript">
   
    $(document).ready(function () {
        $(".addbutton").click(function () {

            var _txtname = $('#_txtname').val();
            var _email = $('#_email').val();
            var _phone = $('#_phone').val();
            var _addr = $('#_addr').val();
            var data = { name: $("#_txtname").val(), email: $("#_email").val(), phone: $("#_phone").val(), address: $("#_addr").val() };
            // JSONify the data
            var data = JSON.stringify(data);
            alert(data);

            $.ajax({

                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: "WebService.asmx/ReceiveWebService",
                data: data,                
                dataType: "json",
                success: function () 
                {
                    alert("success")
                }
                


            })

        });
    });

</script>
</head>
<body>
<form id="form1" runat="server">
<div>
Name: <asp:TextBox ID="_txtname" runat="server"></asp:TextBox><br />
Email: <asp:TextBox ID="_email" runat="server"></asp:TextBox><br />
Phone: <asp:TextBox ID="_phone" runat="server"></asp:TextBox><br />
Address: <asp:TextBox ID="_addr" runat="server"></asp:TextBox><br />
<input id="_btnSubmit" class="addbutton"  type="button" value="Submit" />
<span id="message">

</span>

</div>
</form>
</body>
</html>

//I have taken one sql server database in App.Data Folder As shown Above in Diagram

Here Is My Web.Config ConnectionString:-


<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
<connectionStrings>
  
    <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>


  </connectionStrings>
 <system.web>
<compilation debug="true" targetFramework="4.0"/>
</system.web>
 <system.serviceModel>
  <behaviors>
   <serviceBehaviors>
    <behavior name="">
     <serviceMetadata httpGetEnabled="true" />
     <serviceDebug includeExceptionDetailInFaults="false" />
    </behavior>
   </serviceBehaviors>
  </behaviors>
  <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
 </system.serviceModel>
</configuration>

If some one found some error plz inform me i am glad to help you but you just remember you have to follow the instruction what i boldly declared. After all if you are facing trouble then you can try this following one.
Click To View The Alternative Way Of this Tutorial
Thanks Shibashish mohanty

4 comments:

  1. Its Working for first time but when i check the checkbox [prevent open additional dialog-box] after that its not working again

    same problem that "submit" button does not do anything , neither an error nor insertion...

    ReplyDelete
  2. in your pageload write scriptmanager.getcurrent(this.page).registerpostbackcontrol(pass your checkbox id);
    repeat it for your button id and also repaet for all post back control.

    ReplyDelete

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty