Friday 18 May 2012
Monday 14 May 2012
Insert,Update and Delete through the StoreProcedure
Database Table Design
Page Design
Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Panel ID="Panel1" runat="server"
style="position:absolute; top: 96px; left: 329px; width: 455px; height: 238px; background-color: #66FFFF;">
<asp:TextBox ID="TextBox2" runat="server"
style="position:absolute; top: 89px; left: 218px;"></asp:TextBox>
<asp:RadioButton ID="RadioButton1" runat="server" GroupName="a"
style="position:absolute; top: 133px; left: 265px; height: 21px;" Text="User" />
<asp:RadioButton ID="RadioButton2" runat="server" GroupName="a"
style="position:absolute; top: 132px; left: 150px;" Text="AdminiStrator" />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
style="position:absolute; top: 174px; left: 205px; font-weight: 700; background-color: #339933;"
Text="Login" />
<asp:Label ID="Label1" runat="server"
style="position:absolute; top: 43px; left: 142px;" Text="UserId"></asp:Label>
<asp:Label ID="Label2" runat="server"
style="position:absolute; top: 88px; left: 137px;" Text="Password"></asp:Label>
<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
<asp:Button ID="Button3" runat="server" onclick="Button3_Click"
Style="position:absolute; top: 208px; left: 258px; font-weight: 700; background-color: #FFFF00;"
Text="Delete" />
<asp:Button ID="Button2" runat="server" onclick="Button2_Click"
Style="position:absolute; top: 209px; left: 192px; font-weight: 700; background-color: #FFFF00;"
Text="Insert" />
<asp:TextBox ID="TextBox3" runat="server"
style="position:absolute; top: -32px; left: 194px;"></asp:TextBox>
<asp:TextBox ID="TextBox1" runat="server"
style="position:absolute; top: 42px; left: 219px;"></asp:TextBox>
</asp:Panel>
</div>
<p>
<asp:Label ID="Label4" runat="server" Text=" Id"
Style="position:absolute; top: 66px; left: 492px;"></asp:Label>
</p>
<p>
<asp:Button ID="Button4" runat="server" Text="Update"
Style="position:absolute; top: 304px; left: 450px; font-weight: 700; background-color: #FFFF00;"
onclick="Button4_Click"/>
</p>
<asp:GridView ID="GridView1" runat="server"
Style="position:absolute; top: 347px; left: 452px;" CellPadding="4"
ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<EditRowStyle BackColor="#7C6F57" />
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#E3EAEB" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F8FAFA" />
<SortedAscendingHeaderStyle BackColor="#246B61" />
<SortedDescendingCellStyle BackColor="#D4DFE1" />
<SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
</form>
</body>
</html>
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class Login : System.Web.UI.Page
{
SqlConnection Con;
SqlCommand Cmd;
SqlDataAdapter Da;
SqlDataReader dr;
DataTable Dt;
protected void Page_Load(object sender, EventArgs e)
{
Con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ToString());
Con.Open();
if (!IsPostBack)
{
display();
}
}
//Retrive Data from database
public void display()
{
DataTable ds=new DataTable();
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand("STLOGINDEMO", Con);
Da = new SqlDataAdapter(Cmd);
Cmd.CommandType = CommandType.StoredProcedure;
Da.Fill(ds);
if (ds.Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
catch (Exception Ex)
{
string a = Ex.ToString();
}
}
//Login Page How to Login
protected void Button1_Click(object sender, EventArgs e)
{
string un = TextBox1.Text;
string pwd = TextBox2.Text;
string us;
if (RadioButton2.Checked == true)
{
us = "AdminiStrator";
}
else
{
us = "User";
}
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand("Select [UserName],[Password],[UserType] from LoginDemo where UserName='" + un + "' and Password='" + pwd + "' and UserType='" + us + "'", Con);
dr = Cmd.ExecuteReader();
if (dr.Read())
{
if ((TextBox1.Text == dr[0].ToString()) && (TextBox2.Text == dr[1].ToString()) && ((RadioButton2.Text == dr["UserType"].ToString()) || (RadioButton1.Text == dr["UserType"].ToString())))
{
Response.Redirect("HomePage.aspx");
}
}
else
{
Label3.Text = "Plz Enter Correct USER ID AND PASSWORD";
}
}
catch (Exception Ex)
{
string a = Ex.ToString();
}
finally
{
if (this.Con.State == ConnectionState.Open)
Con.Close();
if (this.Con != null) this.Con = null;
if (this.Con != null) this.Con = null;
}
}
//Insert Data in to Database
protected void Button2_Click(object sender, EventArgs e)
{
string ab;
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand("InsertLogindemo",Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@UserName",TextBox1.Text);
Cmd.Parameters.AddWithValue("@Password", TextBox2.Text);
if (RadioButton1.Checked == true)
{
ab = RadioButton1.Text;
}
else
{
ab = RadioButton2.Text;
}
Cmd.Parameters.AddWithValue("@UserType", ab.ToString());
Cmd.ExecuteNonQuery();
display();
}
catch (Exception Ex)
{
string a = Ex.ToString();
}
finally
{
if (this.Con.State == ConnectionState.Open)
Con.Close();
if (this.Con != null) this.Con = null;
if (this.Con != null) this.Con = null;
}
}
//Delete data From Database
protected void Button3_Click(object sender, EventArgs e)
{
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand("DeleteLogindemo",Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@Id",TextBox3.Text);
Cmd.ExecuteNonQuery();
Label3.Text = "succ";
display();
}
catch (Exception Ex)
{
string a = Ex.ToString();
}
finally
{
if (this.Con.State == ConnectionState.Open)
Con.Close();
if (this.Con != null) this.Con = null;
if (this.Con != null) this.Con = null;
}
}
//update Data from Database
protected void Button4_Click(object sender, EventArgs e)
{
string ab;
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand("updateLogindemo", Con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@Id", TextBox3.Text);
Cmd.Parameters.AddWithValue("@UserName", TextBox1.Text);
Cmd.Parameters.AddWithValue("@Password", TextBox2.Text);
if (RadioButton1.Checked == true)
{
ab = RadioButton1.Text;
}
else
{
ab = RadioButton2.Text;
}
Cmd.Parameters.AddWithValue("@UserType", ab.ToString());
Cmd.ExecuteNonQuery();
Label3.Text = "succ";
display();
}
catch (Exception Ex)
{
string a = Ex.ToString();
}
finally
{
if (this.Con.State == ConnectionState.Open)
Con.Close();
if (this.Con != null) this.Con = null;
if (this.Con != null) this.Con = null;
}
}
}
insert Storeprocedure
ALTER Procedure InsertLogindemo
(
@UserName Varchar(50),
@Password Varchar(50),
@UserType Varchar(50)
)
As
BEGIN
Insert into LoginDemo (UserName,Password,UserType) values(@UserName,@Password,@UserType)
END
insert Storeprocedure
ALTER Procedure DeleteLogindemo
@Id int
As
BEGIN
delete from LoginDemo where Id=@Id
END
Update Storeprocedure
ALTER Procedure UpdateLogindemo
@Id int,
@UserName varchar(50),
@Password varchar(50),
@UserType varchar(50)
As
BEGIN
update LoginDemo set UserName=@UserName,Password=@Password,UserType=@UserType where Id=@Id
END
Select StoreProcedure
ALTER Procedure STLOGINDEMO
AS
BEGIN
Select Id,UserName,Password,UserType from LoginDemo
END
Saturday 12 May 2012
Insert,Update,Delete and Add by the GridView Commnd Event
1. ShowFooter="true" // This Property will set for Footer Reason
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class Gridview_insertupdateDelete : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter da;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ToString());
con.Open();
if (!IsPostBack)
{
GetData();
}
}
public void GetData()
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
da = new SqlDataAdapter("Select * from Demo",con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GetData();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int a = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
string b = GridView1.DataKeys[e.RowIndex].Values["Name"].ToString();
TextBox tb = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");
TextBox tc = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd = new SqlCommand("Update demo set Name='"+tb.Text+"',Address='"+tc.Text+"' where roll="+a,con);
cmd.ExecuteNonQuery();
con.Close();
GridView1.EditIndex = -1;
GetData();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int a = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
//string b = GridView1.DataKeys[e.RowIndex].Value.ToString();
TextBox tb = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");
TextBox tc = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd = new SqlCommand("Delete from Demo where Roll='"+a.ToString()+"'",con);
cmd.ExecuteNonQuery();
con.Close();
GetData();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GetData();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)GridView1.FooterRow.FindControl("TextBox4");
TextBox txtCity = (TextBox)GridView1.FooterRow.FindControl("TextBox5");
TextBox txtDesgnation = (TextBox)GridView1.FooterRow.FindControl("TextBox6");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd =
new SqlCommand(
"insert into Demo(Roll,Name,Address) values('" + txtUsrname.Text + "','" +
txtCity.Text + "','" + txtDesgnation.Text + "')", con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
Response.Write("inserted succefully");
GetData();
// lblresult.ForeColor = Color.Green;
// lblresult.Text = txtUsrname.Text + " Details inserted successfully";
}
else
{
Response.Write(" data not inserted succefully");
GetData();
//lblresult.ForeColor = Color.Red;
//lblresult.Text = txtUsrname.Text + " Details not inserted";
}
}
}
}
Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Gridview insertupdateDelete.aspx.cs" Inherits="Gridview_insertupdateDelete" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:GridView ID="GridView1" runat="server"
style="position:absolute; top: 75px; left: 256px;"
AutoGenerateColumns="False" onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" ShowFooter="true"
onrowupdating="GridView1_RowUpdating" DataKeyNames="Roll,Name"
onrowcommand="GridView1_RowCommand">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
<asp:TemplateField>
<FooterTemplate>
<asp:Button ID="Button1" runat="server" Text="AddNew" CommandName="AddNew" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Roll">
<FooterTemplate>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("Roll")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<FooterTemplate>
<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%#Eval("Name")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address" >
<FooterTemplate>
<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%#Eval("Address")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>
Page Design
Database Design
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class Gridview_insertupdateDelete : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter da;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["Satya"].ToString());
con.Open();
if (!IsPostBack)
{
GetData();
}
}
public void GetData()
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
da = new SqlDataAdapter("Select * from Demo",con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GetData();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int a = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
string b = GridView1.DataKeys[e.RowIndex].Values["Name"].ToString();
TextBox tb = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");
TextBox tc = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd = new SqlCommand("Update demo set Name='"+tb.Text+"',Address='"+tc.Text+"' where roll="+a,con);
cmd.ExecuteNonQuery();
con.Close();
GridView1.EditIndex = -1;
GetData();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int a = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
//string b = GridView1.DataKeys[e.RowIndex].Value.ToString();
TextBox tb = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");
TextBox tc = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd = new SqlCommand("Delete from Demo where Roll='"+a.ToString()+"'",con);
cmd.ExecuteNonQuery();
con.Close();
GetData();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GetData();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)GridView1.FooterRow.FindControl("TextBox4");
TextBox txtCity = (TextBox)GridView1.FooterRow.FindControl("TextBox5");
TextBox txtDesgnation = (TextBox)GridView1.FooterRow.FindControl("TextBox6");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd =
new SqlCommand(
"insert into Demo(Roll,Name,Address) values('" + txtUsrname.Text + "','" +
txtCity.Text + "','" + txtDesgnation.Text + "')", con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
Response.Write("inserted succefully");
GetData();
// lblresult.ForeColor = Color.Green;
// lblresult.Text = txtUsrname.Text + " Details inserted successfully";
}
else
{
Response.Write(" data not inserted succefully");
GetData();
//lblresult.ForeColor = Color.Red;
//lblresult.Text = txtUsrname.Text + " Details not inserted";
}
}
}
}
Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Gridview insertupdateDelete.aspx.cs" Inherits="Gridview_insertupdateDelete" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:GridView ID="GridView1" runat="server"
style="position:absolute; top: 75px; left: 256px;"
AutoGenerateColumns="False" onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" ShowFooter="true"
onrowupdating="GridView1_RowUpdating" DataKeyNames="Roll,Name"
onrowcommand="GridView1_RowCommand">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
<asp:TemplateField>
<FooterTemplate>
<asp:Button ID="Button1" runat="server" Text="AddNew" CommandName="AddNew" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Roll">
<FooterTemplate>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%#Eval("Roll")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<FooterTemplate>
<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%#Eval("Name")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address" >
<FooterTemplate>
<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%#Eval("Address")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>
Page Design
Wednesday 9 May 2012
Subquery in Sql .Example highest Sallary.
OR
SELECT St1.Id,
St1.Name, St2.Sallary
FROM St1
INNER JOIN
St2 ON St1.Id = St2.Id
WHERE
(St2.Sallary =
(SELECT MAX(Sallary) AS
Expr1
FROM St2 AS St2_1))
First Page Design
usingFirst Page Coading
System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class RequestQUery : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect(string.Format("Requet.aspx?val={0}&sal={1}",TextBox1.Text,TextBox2.Text));
}
protected void Button2_Click(object sender, EventArgs e)
{
Response.Redirect("Requet.aspx?ef="+TextBox3.Text);
}
protected void Button3_Click(object sender, EventArgs e)
{
Server.Transfer("Requet.aspx?ff=" + TextBox4.Text);
}
}
Second Page Design
Second Page Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Requet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
TextBox1.Text = Request.QueryString["val"].ToString();
TextBox2.Text = Request.QueryString["sal"].ToString();
}
protected void Button2_Click(object sender, EventArgs e)
{
TextBox3.Text = Request.QueryString["ef"].ToString();
}
protected void Button3_Click(object sender, EventArgs e)
{
TextBox4.Text = Request.QueryString["ff"].ToString();
}
}
Subscribe to:
Posts (Atom)