(Using State,City concept with foreign key and
runtime binding CheckBoxList and DropDownList)
//create database test and use this database for table
Create
database test
use test
//create table StateName
…………………………..
create table StateName (SrNo int identity(1,1),StateCode nvarchar(50) primary key ,StateName nvarchar(50) unique)
//create table
CityName relationship with
StateName (foreign key with StateName)
create table CityName (SrNo int identity(1,1),StateCode nvarchar(50) foreign key references stateName(StateCode) on delete cascade,CityCode nvarchar(50) primary key ,CityName nvarchar(50) unique)
Note: using on delete
cascade for which
concept when you will delete any state from state table by default delete all city
from city table related to state table
//insert record both
table……………………..
insert into StateName values ('up11','Uttar Pradesh')
insert into StateName values ('mp11','Madhya Pradesh')
insert into StateName values ('hi11','Himachal Pradesh')
insert into CityName values ('up11','vns11','Varanasi')
insert into CityName values ('up11','gzb11','Ghaziabad')
insert into CityName values ('up11','gb11','G B Nagar')
insert into CityName values ('mp11','bh11','Bhopal')
insert into CityName values ('mp11','re11','Reewa')
//create table EmpDetails …………………………..
create table EmpDetails(Srno int primary key,Name nvarchar(50),Gender nvarchar(50),EmailId nvarchar(50) unique,Password nvarchar(50),QulificationDetails
nvarchar(200),LastQulification nvarchar(50),MobileNo nvarchar(50) unique ,CollegeName nvarchar(50),StateName nvarchar(50),CityName nvarchar(50))
//state city with drop down list in update panel like …………………………..
<asp:UpdatePanel ID="UpdateCity"
runat="server"
ChildrenAsTriggers="False"
UpdateMode="Conditional">
<ContentTemplate>
<asp:DropDownList ID="ddlcityname" runat="server" Width="135px" >
</asp:DropDownList>
</ContentTemplate>
</asp:UpdatePanel>
###############################################################################
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class Regitration
: System.Web.UI.Page
{
SqlConnection
con;
string
quldetails;
//create
method for gridview binding.........
private void GridViewBind()
{
// fetch data
from table using substing metthod....
string
com = "select SrNo,Name,Gender,EmailId,QulificationDetails=
substring
(QulificationDetails,0,20)+'....',LastQulification,MobileNo,CollegeName,StateName,CityName
from EmpDetails";
SqlCommand
cmd = new SqlCommand(com,
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
//create
method for city binding in drop down
list .........
private void CityBind()
{
SqlCommand
cmd = new SqlCommand("select CityCode,CityName from CityName where StateCode='" +
ddlstatename.SelectedValue + "'",
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
ddlcityname.DataSource = ds.Tables[0];
ddlcityname.DataTextField = "CityName";
ddlcityname.DataValueField = "CityCode";
ddlcityname.DataBind();
ddlcityname.Items.Insert(0, "Select");
}
//create method for State binding in drop down list .........
private void StateBind()
{
SqlCommand
cmd = new SqlCommand("select StateCode,StateName from StateName",
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
ddlstatename.DataSource = ds.Tables[0];
ddlstatename.DataTextField = "StateName";
ddlstatename.DataValueField = "statecode";
ddlstatename.DataBind();
ddlstatename.Items.Insert(0, "Select");
}
protected void Page_Load(object
sender, EventArgs e)
{
con = new
SqlConnection("Data
Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True");
if
(!IsPostBack)
{
//call method....for
gridview binding
GridViewBind();
//call
method....for State binding
StateBind();
ddlcityname.Items.Insert(0, "Select"); //for initial record "Select"
//this
code for
runtime course bind with drop
down list..............................
string[]
course = new string[10]
{ "Select", "B A", "B
C A", "B B A", "B.Sc.", "M
A", "M C A", "B. Tech", "M.
Tech", "B.Com" };
foreach
(var c in
course)
{
ddlCourse.Items.Add(c.ToString());
}
//this
code for
runtime qulification bind with
checkboxlist..............................
string[]
qul = new string[4]
{ "High School", "Intermidiate", "Graduation",
"Post Graduation" };
foreach
(var q in qul)
{
CblQulification.Items.Add(q.ToString());
}
}
}
protected void ddlstatename_SelectedIndexChanged(object sender, EventArgs
e)
{
UpdateCity.Update();
CityBind();
}
protected void BtnSave_Click(object
sender, EventArgs e)
{
// How to use
CheckBoxList ...........................
foreach
(ListItem li in
CblQulification.Items)
{
if
(li.Selected)
{
quldetails +=
li.Value.ToString() + ",";
}
}
quldetails =
quldetails.Remove(quldetails.Length - 1);
string
com = "insert into EmpDetails values('"
+ Convert.ToInt32(txtsrno.Text) + "','" + txtname.Text + "','" + RbGender.SelectedValue + "','" + txtemailid.Text + "','" + txtpassword.Text + "','" + quldetails + "','" + ddlCourse.SelectedItem.Text + "','" + txtmobileno.Text + "','" + txtcollegename.Text + "','" + ddlstatename.SelectedItem.Text +
"','" +
ddlcityname.SelectedItem.Text + "')";
SqlCommand
cmd = new SqlCommand(com,
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
LMessage.Visible = true;
LMessage.Text = "Save Data Successfully";
//this code for empty all field after insert
data..................
txtname.Text = String.Empty;
txtmobileno.Text = "";
txtcpassowd.Text = "";
txtcollegename.Text = "";
txtemailid.Text = "";
GridViewBind();
}
//this code for update record by srno...................
protected void btnupdate_Click(object
sender, EventArgs e)
{
//How to use
CheckBoxList ...........................
foreach
(ListItem li in
CblQulification.Items)
{
if
(li.Selected)
{
quldetails +=
li.Value.ToString() + ",";
}
}
quldetails =
quldetails.Remove(quldetails.Length - 1);
string com = "update
EmpDetails set Name='"+txtname.Text+"',Gender='"+RbGender.SelectedItem.Text+"',EmailId='"+txtemailid.Text+"',Password='"+txtpassword.Text+"',QulificationDetails='"+quldetails+"',LastQulification='"+ddlCourse.SelectedItem.Text+"', MobileNo='"+txtmobileno.Text+"',CollegeName='"+txtcollegename.Text+"',StateName='"+ddlstatename.SelectedItem.Text+"',CityName='"+ddlcityname.SelectedItem.Text+"' where Srno='"+Convert.ToInt32(txtsrno.Text)+"'";
SqlCommand
cmd = new SqlCommand(com,
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
LMessage.Visible = true;
LMessage.Text = "Data update Successfully";
GridViewBind();
}
// code for show
record by SrNo...............................
protected void btnshow_Click(object
sender, EventArgs e)
{
string
com = "select * from EmpDetails where
srno='" + Convert.ToInt32(txtsrno.Text)
+ "'";
SqlCommand
cmd = new SqlCommand(com,
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
if
(ds.Tables[0].Rows.Count >= 1)
{
DataRow
dr = ds.Tables[0].Rows[0];
txtsrno.Text = dr[0].ToString();
txtname.Text = dr["name"].ToString();
RbGender.SelectedValue = dr["Gender"].ToString();
txtemailid.Text = dr["EmailId"].ToString();
txtpassword.Text = dr["Password"].ToString();
txtcpassowd.Text = dr["Password"].ToString();
txtmobileno.Text = dr["MobileNo"].ToString();
txtcollegename.Text = dr["CollegeName"].ToString();
ddlCourse.DataBind();
ddlCourse.SelectedItem.Selected = false;
ddlCourse.Items.FindByText(dr["LastQulification"].ToString()).Selected
= true;
ddlstatename.DataBind();
ddlstatename.SelectedItem.Selected
= false;
ddlstatename.Items.FindByText(dr["StateName"].ToString()).Selected = true;
CityBind();
ddlcityname.DataBind();
ddlcityname.SelectedItem.Selected =
false;
ddlcityname.Items.FindByText(dr["CityName"].ToString()).Selected = true;
string[]
kush = dr["QulificationDetails"].ToString().Split(',');
for
(int i = 0; i < kush.Length; i++)
{
foreach
(ListItem li in
CblQulification.Items)
{
if
(li.Text == kush[i])
{
li.Selected = true;
}
}
}
}
}
//code for delete record with
srno.........................................
protected void btnDelete_Click(object
sender, EventArgs e)
{
string
com = "delete from EmpDetails where
Srno='" + Convert.ToInt32(txtsrno.Text)
+ "'";
SqlCommand
cmd = new SqlCommand(com,
con);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
LMessage.Visible = true;
LMessage.Text = "Data delete Successfully";
GridViewBind();
}
}
0 comments:
Post a Comment