Aslam-u-Alikuum Beautiful People
==============================
How To Insert View Update Delete and Search Data with Sql Server +(done)
==============================
How to Search Record (done)
==============================
===================================================================================
Part 2:
===================================================================================
How To Insert View Update Delete and Search Data with Sql Server
==================================================================================
Step 1:
==================================================================================
Desing View :
==================================================================================
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="HWORK.aspx.cs" Inherits="HWORK" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1 {
width: 207px;
}
.auto-style2 {
width: 524px;
}
</style>
</head>
<body style="height: 903px; width: 1331px">
<form id="form1" runat="server">
<div>
<asp:HiddenField ID="hfId" runat="server" />
<table border="0" style="width: 657px; margin-left: 261px">
<tr>
<td class="auto-style1">
<asp:Label ID="Label1" runat="server" Text="Day"></asp:Label>
</td>
<td colspan="2" class="auto-style2">
<asp:DropDownList ID="DropDownList6" runat="server" Height="16px" style="margin-left: 0px; margin-top: 0px" Width="103px">
<%--<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>--%>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:Label ID="Label14" runat="server" Text="Class"></asp:Label>
<asp:DropDownList ID="DropDownList4" runat="server" Height="22px" style="margin-left: 26px; margin-top: 0px" Width="103px">
<%--<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>--%>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>
<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label2" runat="server" Text="Session"></asp:Label>
</td>
<td colspan="2" class="auto-style2">
<asp:TextBox ID="txts" runat="server" Width="208px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label3" runat="server" Text="Month"></asp:Label>
</td>
<td colspan="2" class="auto-style2">
<asp:DropDownList ID="DropDownList5" runat="server" Height="16px" style="margin-left: 2px; margin-top: 0px" Width="103px">
<%--<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>--%>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label4" runat="server" Text="Homework"></asp:Label>
</td>
<td colspan="2" class="auto-style2">
<asp:TextBox ID="txthw" runat="server" TextMode="MultiLine" Height="107px" Width="518px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
</td>
<td colspan="2" class="auto-style2">
<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" Width="77px" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" Width="77px" />
<asp:Button ID="btnClear" runat="server" Text="Clear" OnClick="btnClear_Click" Width="69px" />
</td>
</tr>
<tr>
<td class="auto-style1">
</td>
<td colspan="2" class="auto-style2">
<asp:Label ID="lblErrorMessage" runat="server" Text="" ForeColor="Red"></asp:Label>
<asp:Label ID="lblSuccessMessage" runat="server" Text="" ForeColor="Green"></asp:Label>
<asp:Label ID="Label16" runat="server" ForeColor="#FF3300" Text="Fill Al Text Feild" Visible="False"></asp:Label>
</td>
<tr>
<td class="auto-style1">
</td>
<td colspan="2" class="auto-style2">
</td>
</tr>
</tr>
</table>
</div>
<p>
<%--<a href="index.html">SAAD</a>--%>
<p style="border: 2px solid powderblue ; width="100%"">
<asp:Label ID="Label10" runat="server" style="margin-left: 40px" Text="Day:"></asp:Label>
<asp:DropDownList ID="DropDownList1" runat="server" Height="16px" style="margin-left: 26px; margin-top: 0px" Width="103px">
<%--<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>--%>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:Label ID="Label11" runat="server" style="margin-left: 40px" Text="Month"></asp:Label>
<asp:DropDownList ID="DropDownList2" runat="server" Height="16px" style="margin-left: 26px; margin-top: 0px" Width="103px">
<%--<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>--%>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
</asp:DropDownList>
<asp:Label ID="Label12" runat="server" style="margin-left: 40px" Text="Session"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server" Height="20px" style="margin-left: 56px; margin-top: 0px;" Width="105px"></asp:TextBox>
<asp:Label ID="Label13" runat="server" style="margin-left: 40px" Text="Class"></asp:Label>
<asp:DropDownList ID="DropDownList3" runat="server" Height="16px" style="margin-left: 26px; margin-top: 0px" Width="103px">
<%--<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>--%>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>KG-1</asp:ListItem>
<asp:ListItem>KG-2</asp:ListItem>
<asp:ListItem>Nursery</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:Button ID="Button1" runat="server" Height="27px" OnClick="Button1_Click" style="margin-left: 56px" Text="Search" Width="90px" />
<asp:Label ID="Label15" runat="server" ForeColor="Red" Text="Error Fill All Feild" Visible="False"></asp:Label>
</p>
</p>
<asp:GridView ID="gvContact" runat="server" AutoGenerateColumns="false" Height="61px" Width="895px" HorizontalAlign="Center" style="margin-left: 96px">
<Columns>
<asp:BoundField DataField="Day" HeaderText="Day" />
<asp:BoundField DataField="Session" HeaderText="Session" />
<asp:BoundField DataField="Month" HeaderText="Month" />
<asp:BoundField DataField="HomeWork" HeaderText="HomeWork" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkView" runat="server" CommandArgument='<%# Eval("Id") %>' OnClick="lnk_OnClick">View</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<p>
</p>
<p>
</p>
</form>
</body>
</html>
==================================================================================
Step2:
==================================================================================
Sql Server Database code
==================================================================================
CREATE TABLE [dbo].[homework](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Date] [nvarchar](100) NULL,
[Session] [int] NULL,
[Month] [int] NULL,
[HomeWork] [nvarchar](2000) NULL,
[Day] [int] NULL,
[Class] [int] NULL,
CONSTRAINT [PK_homework] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
==================================================================================
SQL STORE PROCEDURE
==================================================================================
fOR INSERT AND UPDATE
==================
CREATE PROCEDURE [dbo].[hworkinsertup]
@Id int,
@Session int,
@Month int,
@HomeWork nvarchar(100),
@Day int,
@Class int
AS
BEGIN
IF(@Id=0)
INSERT INTO homework(
Session ,
Month ,
HomeWork ,
Day,
Class)
VALUES
(
@Session ,
@Month ,
@HomeWork ,
@Day,
@Class)
ELSE
BEGIN
UPDATE homework
SET
Session =@Session ,
Month =@Month ,
HomeWork =@HomeWork,
Day=@Day,
Class=@Class
WHERE Id = @Id
END
===============
fOR VIEW ALL RECORD
==============
CREATE PROCEDURE [dbo].[hwviewall]
AS
BEGIN
SELECT*
FROM [dbo].[homework]
END
=================
FOR VIEW BY ID
================
CREATE PROCEDURE [dbo].[hwviewbyid]
@Id int
AS
BEGIN
SELECT * FROM [dbo].[homework]WHERE Id = @Id
END
================
fOR SEARCH
================
CREATE PROCEDURE [dbo].[hwviewbysearch]
@Day int,
@Month int,
@Session int,
@Class int
AS
BEGIN
SELECT * FROM [dbo].[homework]
WHERE Day = @Day AND Session LIKE @Session AND Class LIKE @Class
END
====================
fOR DELETE
===================
CREATE PROCEDURE [dbo].[hwdelete]
@Id int
AS
BEGIN
DELETE FROM [dbo].[homework]WHERE Id = @Id
END
==================================================================================
Step 3
==================================================================================
C# Code
=================================================================================
On Page Start
=================
SqlConnection sqlCon = new SqlConnection(@"");
returnclass rc = new returnclass();
===============================
On Page Load Event:
==================================
if (!IsPostBack)
{
btnDelete.Enabled = false;
}
==================================
Reset Code :
==================================
protected void btnSave_Click(object sender, EventArgs e)
{
if (txthw.Text == "")
{
Label16.Visible = true;
}
else
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("[dbo].[hworkinsertup]", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Id", (hfId.Value == "" ? 0 : Convert.ToInt32(hfId.Value)));
sqlCmd.Parameters.AddWithValue("@Day", DropDownList6.Text.Trim());
sqlCmd.Parameters.AddWithValue("@Session", txts.Text.Trim());
sqlCmd.Parameters.AddWithValue("@Month", DropDownList5.Text.Trim());
sqlCmd.Parameters.AddWithValue("@HomeWork", txthw.Text.Trim());
sqlCmd.Parameters.AddWithValue("@Class", DropDownList4.Text.Trim());
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
string contactID = hfId.Value;
Clear();
if (contactID == "")
lblSuccessMessage.Text = "Saved Successfully";
else
lblSuccessMessage.Text = "Updated Successfully";
Label16.Visible = false;
// FillGridView();
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
Clear();
}
public void Clear()
{
hfId.Value = "";
DropDownList6.Text = txts.Text = DropDownList5.Text = txthw.Text = "";
lblSuccessMessage.Text = lblErrorMessage.Text = "";
btnSave.Text = "Save";
btnDelete.Enabled = false;
}
void FillGridView()
{
if (TextBox1.Text == "")
{
Label15.Visible = true;
}
else
{
int Day = Convert.ToInt32((DropDownList1.Text));
int Month = Convert.ToInt32((DropDownList1.Text));
int Session = Convert.ToInt32((TextBox1.Text));
int Class = Convert.ToInt32((DropDownList1.Text));
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter("[dbo].[hwviewbysearch]", sqlCon);
sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDa.SelectCommand.Parameters.AddWithValue("@Day", Day);
sqlDa.SelectCommand.Parameters.AddWithValue("@Month", Month);
sqlDa.SelectCommand.Parameters.AddWithValue("@Session", Session);
sqlDa.SelectCommand.Parameters.AddWithValue("@Class", Class);
DataTable dtbl = new DataTable();
sqlDa.Fill(dtbl);
sqlCon.Close();
gvContact.DataSource = dtbl;
gvContact.DataBind();
Label15.Visible = false;
}
}
protected void lnk_OnClick(object sender, EventArgs e)
{
int contactID = Convert.ToInt32((sender as LinkButton).CommandArgument);
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter("[dbo].[hwviewbyid]", sqlCon);
sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDa.SelectCommand.Parameters.AddWithValue("@Id", contactID);
DataTable dtbl = new DataTable();
sqlDa.Fill(dtbl);
sqlCon.Close();
hfId.Value = contactID.ToString();
DropDownList6.Text = dtbl.Rows[0]["Day"].ToString();
txts.Text = dtbl.Rows[0]["Session"].ToString();
DropDownList5.Text = dtbl.Rows[0]["Month"].ToString();
txthw.Text = dtbl.Rows[0]["HomeWork"].ToString();
btnSave.Text = "Update";
btnDelete.Enabled = true;
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("[dbo].[hwdelete]",sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Id",Convert.ToInt32(hfId.Value));
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
Clear();
// FillGridView();
lblSuccessMessage.Text = "Deleted Successfully";
}
// protected void gvContact_SelectedIndexChanged(object sender, EventArgs e)
// {
// }
protected void Button1_Click(object sender, EventArgs e)
{
FillGridView();
// string i,o,p;
// i = DropDownList1.Text;
// o = DropDownList2.Text;
// p = TextBox1.Text;
//
// Label6.Text = rc.scalarReturn("select Date from homework where Day = LIKE'+ i' Month ='+ o' Session= '+ p'");
// Label5.Text = rc.scalarReturn("select HomeWork from homework where Day ='+ i' Month ='+ o' Session= '+ p'");
}
==========================
===================================================================================
Part 2
Compelete :-)
==================================================================================
Thumbs Up This Video
And
Subscribe To My Channel
And
Trun Notifications On
================================================================================================
Till Next Time
Aslam-u-Alikum Beautiful People
==================================================================================
No comments:
Post a Comment