How to bind country state using ajax with calendar

Step1. data bade code.

Country table-

create table Country

(
cid int primary key identity(1,1),
cname varchar(50)

Insert record-

insert into Country(cname) values('India'),('Pakistan'),('USA'),('Canada'),('Japan'),('Bhutan')

create proc country get.

create proc usp_Country_get

as
begin
select * from Country
end

State table-

create table State

(
sid int primary key identity(1,1),
sname varchar(50),
cidd int foreign key references Country(cid)
)

Insert record

insert into State(Sname,Cidd) values('UP',1)
insert into State(Sname,Cidd) values('HR',1)
insert into State(Sname,Cidd) values('Delhi',1)
insert into State(Sname,Cidd) values('MP',1)
insert into State(Sname,Cidd) values('Baluchistan',2)
insert into State(Sname,Cidd) values('Panjab',2)
insert into State(Sname,Cidd) values('Shind',2)
insert into State(Sname,Cidd) values('Indiana',3)
insert into State(Sname,Cidd) values('Amrica',3)
insert into State(Sname,Cidd) values('canadiya',4)
insert into State(Sname,Cidd) values('Cniforniya',4)
insert into State(Sname,Cidd) values('japanies',5)
insert into State(Sname,Cidd) values('Tokiyo',5)
insert into State(Sname,Cidd) values('bhopal',6)

insert into State(Sname,Cidd) values('nepali',6)

Create proc State-

create proc usp_state_get

@cid int
as
begin
select * from State where cidd=@cid
end

Create proc Employee_get using inner Join.

create proc usp_Employee_getasbeginselect Employee.*,Country.*,State.* from Employee inner join Country on Employee.country=country.cid inner join State on Employee.State=State.sidend

Create Employee table.

create table Employee

(
Empid int primary key identity(1,1),
Name varchar (50),
Email_Id varchar(50),
Password varchar(50),
CPassword varchar(50),
Address varchar(50),
Salary int,
Country int,
State int,
DOB datetime,
File_upload varchar(50),
Hobbies varchar(100)
)

Create insert proc.

create proc usp_employee_insert
@Name varchar(50),
@Email_Id varchar(50),
@Password varchar(50), @CPassword varchar(50), @Address varchar(50), @Salary int,
@Country int,
@State int,
@DOB datetime,
@File_upload varchar(50),
@Hobbies varchar(50)
as
begin
insert into Employee(Name,Email_Id,Password,CPassword,Address,Salary,Country,State,DOB,File_upload,Hobbies)
values(@Name,@Email_Id,@Password,@CPassword,@Address,@Salary,@Country,@State,@DOB,@File_upload,@Hobbies)
end

Create Delete proc-

create proc usp_employee_delete@Empid intasbegindelete from Employee where Empid=@Empidend

Create edit proc.

create proc usp_employee_edit@Empid intasbeginselect * from Employee where Empid=@Empidend

Create update proc-

create proc usp_employee_update
@Empid int,
@Name varchar(50),
@Email_Id varchar(50),
@Password varchar(50), @CPassword varchar(50), @Address varchar(50), @Salary int,
@Country int,
@State int,
@DOB datetime,
@File_upload varchar(50),
@Hobbies varchar(50)
as
begin
update Employee set name=@Name,Email_Id=@Email_Id,Password=@Password,CPassword=@CPassword,Address=@Address,Salary=@Salary,Country=@Country,
State=@State,DOB=@DOB,File_upload=@File_upload,Hobbies=@Hobbies where Empid=@Empid
end

Create search bar proc.

create proc usp_employee_search
@search varchar(50)
as
begin
select * from Employee
where name like '%'+@search+'%' or
address like '%'+@search+'%'
end

--how to find store procedure in sql server---

select * from sys.procedures

-----how to find table in sql server-----

select * from sys.tables

Step1. We provide to article code is country and state and using ajax with calendar. and checkbox and file upload and grd bind.

how to register ajax aspx page.

Coding behind Default.aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Employee.aspx.cs" Inherits="selfprectis29717.Employee" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="Ajax" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">

<title></title>
</head>
<body>
<form id="form1" runat="server">
<Ajax:ToolkitScriptManager ID="DOB" runat="server"></Ajax:ToolkitScriptManager>
<div>
<table>
<tr>
<td>Name:</td>
<td>
<asp:TextBox ID="Textname" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Email Id:</td>
<td>
<asp:TextBox ID="Textemail" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Password:</td>
<td>
<asp:TextBox ID="Textpassword" runat="server" TextMode="Password"></asp:TextBox></td>
</tr>

<tr>
<td>CPassword:</td>
<td>
<asp:TextBox ID="Textcpassword" runat="server" TextMode="Password"></asp:TextBox></td>
</tr>

<tr>
<td>Address:</td>
<td>
<asp:TextBox ID="Textaddress" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Salary:</td>
<td>
<asp:TextBox ID="Textsalary" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Country:</td>
<td>
<asp:DropDownList ID="ddlcountry" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlcountry_SelectedIndexChanged"></asp:DropDownList></td>
</tr>

<tr>
<td>State:</td>
<td>
<asp:DropDownList ID="ddlstate" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlstate_SelectedIndexChanged"></asp:DropDownList></td>
</tr>

<tr>
<td>DOB</td>
<td>
<asp:TextBox ID="TextDOB" runat="server"></asp:TextBox></td>
<Ajax:CalendarExtender ID="clender" runat="server" PopupButtonID="TextDOB" TargetControlID="TextDOB" PopupPosition="BottomRight"></Ajax:CalendarExtender>

</tr>

<tr>
<td>File_upload</td>
<td>
<asp:FileUpload ID="TextFill" runat="server" /></td>
</tr>

<tr>
<td>Hobbies:</td>
<td>
<asp:CheckBoxList ID="cblhobbies" runat="server" RepeatColumns="3">
<asp:ListItem Text="Football" Value="1"></asp:ListItem>
<asp:ListItem Text="Cricket" Value="2"></asp:ListItem>
<asp:ListItem Text="BollyBall" Value="3"></asp:ListItem>
<asp:ListItem Text="Music" Value="4"></asp:ListItem>
<asp:ListItem Text="Yoga" Value="5"></asp:ListItem>
<asp:ListItem Text="Movies" Value="6"></asp:ListItem>
<asp:ListItem Text="Reading" Value="7"></asp:ListItem>
<asp:ListItem Text="Travelling" Value="8"></asp:ListItem>
<asp:ListItem Text="FastFood" Value="9"></asp:ListItem>
</asp:CheckBoxList></td>
</tr>

<tr>
<td></td>
<td>
<asp:Button ID="btnsave" runat="server" Text="Save" OnClientClick="return validation()" OnClick="btnsave_Click" /></td>
</tr>

<tr>
<td></td>
<td>
<asp:TextBox ID="Textsearch" runat="server" ></asp:TextBox><asp:Button ID="btnsearch" runat="server" Text="search" OnClick="btnsearch_Click" /></td>
</tr>

<asp:GridView ID="grd" runat="server" AutoGenerateColumns="false" OnRowCommand="grd_RowCommand">
<Columns>

<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Email">
<ItemTemplate>
<%#Eval("Email_Id") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Password ">
<ItemTemplate>
<%#Eval("Password ") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="CPassword">
<ItemTemplate>
<%#Eval("CPassword") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<%#Eval("Address") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<%#Eval("Salary") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<%#Eval("cname") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="State">
<ItemTemplate>
<%#Eval("Sname") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="DOB">
<ItemTemplate>
<%#Eval("DOB") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="File_upload">
<ItemTemplate>
<asp:Image ID="img" runat="server" ImageUrl='<%#Eval("File_upload","~/Fill_Upload/{0}") %>' Width="70px" Height="60px" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Hobbies">
<ItemTemplate>
<%#Eval("Hobbies") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="delete">
<ItemTemplate>
<asp:LinkButton ID="lnkdel" runat="server" Text="Delete" CommandName="Del" CommandArgument='<%#Eval("Empid") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Edit">
<ItemTemplate>
<asp:LinkButton ID="lnkedt" runat="server" Text="Edit" CommandName="Edt" CommandArgument='<%#Eval("Empid") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="chkdelete" runat="server" />
</ItemTemplate>
<HeaderTemplate>
<asp:Button ID="btndeleteall" runat="server" Text="Delete All" CommandName="DELALL" CommandArgument='<%#Eval("Empid") %>' />
</HeaderTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</table>
</div>
</form>
</body>
</html>

Coding behind Default.CS page-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace selfprectis29717
{
public partial class Employee : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBA"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Fill_country();
ddlstate.Items.Insert(0, new ListItem("--select--", "0"));
Fill_grd();

}
}
public void Fill_country()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_Country_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlcountry.DataValueField = "cid";
ddlcountry.DataTextField = "cname";
ddlcountry.DataSource = ds;
ddlcountry.DataBind();
ddlcountry.Items.Insert(0, new ListItem("--select--", "0"));
}
}
public void Fill_state(string cid)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_state_get", con);
cmd.Parameters.AddWithValue("@cid", cid);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlstate.DataValueField = "sid";
ddlstate.DataTextField = "sname";
ddlstate.DataSource = ds;
ddlstate.DataBind();
ddlstate.Items.Insert(0, new ListItem("--select--", "0"));
}
}
public void Fill_grd()
{

SqlCommand cmd = new SqlCommand("usp_Employee_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
grd.DataSource = ds;
grd.DataBind();
}
else
{
grd.DataSource = null;
grd.DataBind();
}
}

protected void ddlcountry_SelectedIndexChanged(object sender, EventArgs e)
{
Fill_state(ddlcountry.SelectedValue);
}

protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
{

}
protected void btnsave_Click(object sender, EventArgs e)
{
string HOB = "";
for (int i = 0; i < cblhobbies.Items.Count; i++)
{
if (cblhobbies.Items[i].Selected == true)
{
HOB += cblhobbies.Items[i].Text + ',';
}
}

HOB = HOB.TrimEnd(',');
string FN = "";
FN = Path.GetFileName(TextFill.PostedFile.FileName);
TextFill.SaveAs(Server.MapPath("Fill_Upload" + "\\" + FN));
if (btnsave.Text == "Save")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", Textname.Text);
cmd.Parameters.AddWithValue("@Email_Id", Textemail.Text);
cmd.Parameters.AddWithValue("@Password", Textpassword.Text);
cmd.Parameters.AddWithValue("@CPassword", Textcpassword.Text);
cmd.Parameters.AddWithValue("@Address", Textaddress.Text);
cmd.Parameters.AddWithValue("@Salary", Textsalary.Text);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@State", ddlstate.SelectedValue);
cmd.Parameters.AddWithValue("@DOB", TextDOB.Text);
cmd.Parameters.AddWithValue("@File_upload", FN);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.ExecuteNonQuery();
Fill_grd();
con.Close();
}
else if (btnsave.Text == "Update")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", ViewState["vis"]);
cmd.Parameters.AddWithValue("@Name", Textname.Text);
cmd.Parameters.AddWithValue("@Email_Id", Textemail.Text);
cmd.Parameters.AddWithValue("@Password", Textpassword.Text);
cmd.Parameters.AddWithValue("@CPassword", Textcpassword.Text);
cmd.Parameters.AddWithValue("@Address", Textaddress.Text);
cmd.Parameters.AddWithValue("@Salary", Textsalary.Text);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@State", ddlstate.SelectedValue);
cmd.Parameters.AddWithValue("@DOB", TextDOB.Text);
cmd.Parameters.AddWithValue("@File_upload", FN);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.ExecuteNonQuery();
Fill_grd();
con.Close();

}
}

protected void grd_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Del")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
cmd.ExecuteNonQuery();
Fill_grd();
con.Close();
}
else if (e.CommandName == "Edt")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_edit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", e.CommandArgument);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
Textname.Text = ds.Tables[0].Rows[0]["Name"].ToString();
Textemail.Text = ds.Tables[0].Rows[0]["Email_Id"].ToString();
Textpassword.Text = ds.Tables[0].Rows[0]["Password"].ToString();
Textcpassword.Text = ds.Tables[0].Rows[0]["CPassword"].ToString();
Textaddress.Text = ds.Tables[0].Rows[0]["Address"].ToString();
Textsalary.Text = ds.Tables[0].Rows[0]["Salary"].ToString();
ddlcountry.SelectedValue = ds.Tables[0].Rows[0]["Country"].ToString();
Fill_state(ddlcountry.SelectedValue);
ddlstate.SelectedValue = ds.Tables[0].Rows[0]["State"].ToString();
TextDOB.Text = ds.Tables[0].Rows[0]["DOB"].ToString();

string[] arr = ds.Tables[0].Rows[0]["Hobbies"].ToString().Split(',');
cblhobbies.ClearSelection();

for (int i = 0; i < cblhobbies.Items.Count; i++)
{
for (int j = 0; j < arr.Length; j++)
{
if (cblhobbies.Items[i].Text == arr[j])
{
cblhobbies.Items[i].Selected = true;
break;
}
}
}
btnsave.Text = "Update";
ViewState["vis"] = e.CommandArgument;
}
con.Close();
}
}

protected void btnsearch_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_search", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@search", Textsearch.Text);
cmd.ExecuteNonQuery();
con.Close();
}
}
}

Coding behind Default web.config connection string page-

<?xml version="1.0"?>

<!--

For more information on how to configure your ASP.NET application, please visit

http://go.microsoft.com/fwlink/?LinkId=169433

-->

<system.web>

</system.web>

</configuration>

<!--<add name="DBA"

connectionString="Server=Shiva; Database=selfprectis29717; User Id=DDSIR; password= ddsir"

providerName="System.Data.SqlClient" />-->

--how to find store procedure in sql server---

select * from sys.procedures

-----how to find table in sql server-----

select * from sys.tables

--How to show table record

select * from Employee

Author Description

Rock Programmer

Register Now Click On

Read More Tutorials Click On