User-367318540 posted
I am retrieving data into gridview from database ,Now i want to apply update|Delete|insert function .
Currently i am inserting data into gridview using VIewstate then i am saving data into database ,
here is my html.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BalePack.aspx.cs" Inherits="WebApplication1.BalePack" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<style>
.grdContent
{
width: 80%;
border: solid 2px black;
min-width: 80%;
}
.header
{
background-color: #646464;
font-family: Arial;
color: White;
border: none 0px transparent;
height: 25px;
text-align: center;
font-size: 16px;
}
.rows
{
background-color: #fff;
font-family: Arial;
font-size: 14px;
color: #000;
min-height: 25px;
text-align: left;
border: none 0px transparent;
}
.rows:hover
{
background-color: #ff8000;
font-family: Arial;
color: #fff;
text-align: left;
}
.selectedrow
{
background-color: #ff8000;
font-family: Arial;
color: #fff;
font-weight: bold;
text-align: left;
}
.mydatagrid a /** FOR THE PAGING ICONS **/
{
background-color: Transparent;
padding: 5px 5px 5px 5px;
color: #fff;
text-decoration: none;
font-weight: bold;
}
/*.mygrdContent a:hover /** FOR THE PAGING ICONS HOVER STYLES**/
{
background-color: #000;
color: #fff;
}*/
.mygrdContent span /** FOR THE PAGING ICONS CURRENT PAGE INDICATOR **/
{
background-color: #c9c9c9;
color: #000;
padding: 5px 5px 5px 5px;
}
.pager
{
background-color: #646464;
font-family: Arial;
color: White;
height: 30px;
text-align: left;
}
.mygrdContent td
{
padding: 5px;
}
.mygrdContent th
{
padding: 5px;
}
.inputbtn
{
-moz-border-radius: 5px;
border-radius: 5px;
border: 1px solid gray;
padding:0 3px 0 3px;
display:inline-block;
text-decoration:none;
background:#595651;
color:#FFFFFF;
cursor:pointer;
font:11px sans-serif;
}
.bordereffect
{
color: #1abc9c;
background-color: Transparent;
padding: 10px;
border: solid 2px #1abc9c;
font-size: 20px;
font-weight: lighter;
font-family: Consolas;
-moz-border-radius: 0px 0px 0px 0px;
border-radius: 0px 0px 0px 0px;
margin-top: 12px;
}
</style>
<title></title>
<style type="text/css">
.auto-style1 {
width: 100%;
}
.input-lg {}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
Ref No.
<asp:TextBox ID="txtRefno" CssClass="form-control input-lg" Placeholder="Ref No" runat="server" OnTextChanged="txtRefno_TextChanged" AutoPostBack="True"></asp:TextBox>
Order No.
<asp:TextBox ID="txtOrder" CssClass="form-control input-lg" Placeholder="Order No" runat="server" Height="16px" Width="68px"></asp:TextBox>
<asp:Label ID="lbmsg" runat="server" Text="." Font-Bold="True" Font-Size="Large" ForeColor="Red"></asp:Label>
Pack No :
<asp:TextBox ID="txtpackno" CssClass="form-control input-lg" Placeholder="Order No" AutoPostBack="true" runat="server" Height="16px" Width="68px" OnTextChanged="txtpackno_TextChanged"></asp:TextBox>
<br/>
<br />
<asp:TextBox ID="TextBox1" AutoPostBack="true" runat="server" Placeholder="Input Bale No" OnTextChanged="TextBox1_TextChanged" Height="16px" Width="100px" style="margin-left: 0px"></asp:TextBox>
<asp:DropDownList ID="DropDownList1" runat="server" Enabled="false" Height="20px" Width="130px">
</asp:DropDownList>
<asp:TextBox ID="txtorderno" Placeholder="Order No" readonly="true" runat="server" Height="16px" Width="51px"></asp:TextBox>
<asp:TextBox ID="txtqty" Placeholder="QTY" readonly="true" runat="server" Height="16px" Width="56px"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" Height="26px" Width="35px" />
</div>
<table class="auto-style1">
<tr>
<td> </td>
<td>
<asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="#66ccff" HeaderStyle-ForeColor="White"
CssClass="mygrdContent" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" RowStyle-CssClass="rows" AutoGenerateColumns="false" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" AllowSorting="True" OnRowDataBound="GridView1_RowDataBound" >
<Columns>
<%--<asp:BoundField DataField="Prdno" ReadOnly="True" HeaderText="Bale_No" />--%>
<asp:TemplateField HeaderText="Bale_No">
<ItemTemplate>
<asp:Label ID="Prdno" runat="server" Text='<%#Bind("Prdno")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:BoundField DataField="_PckID" HeaderText="PackID" Visible="false" />--%>
<asp:TemplateField HeaderText="Bale_No" Visible="false">
<ItemTemplate>
<asp:Label ID="_PID" runat="server" Text='<%#Bind("_PID")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:BoundField DataField="Descriptionitem" HeaderText="Descriptionitem" ReadOnly="true" />--%>
<asp:TemplateField HeaderText="Description">
<ItemTemplate>
<asp:Label ID="Descriptionitem" runat="server" Text='<%#Bind("Descriptionitem")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:BoundField DataField="Orderno" ControlStyle-Width="20px" HeaderText="Orderno" >
<ControlStyle Width="20px"></ControlStyle>
</asp:BoundField>--%>
<asp:TemplateField HeaderText="Orderno" >
<ItemTemplate>
<asp:Label ID="Orderno" runat="server" Text='<%#Bind("Orderno")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:BoundField DataField="QTY" ControlStyle-Width="20px" HeaderText="QTY" >
<ControlStyle Width="20px"></ControlStyle>
</asp:BoundField>--%>
<asp:TemplateField HeaderText="QTY" >
<ItemTemplate>
<asp:Label ID="QTY" runat="server" Text='<%#Bind("QTY")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<%--<asp:LinkButton ID="lnkEdit" Text="Edit" runat="server" CommandName="Edit" />--%>
</ItemTemplate>
<EditItemTemplate>
<%-- <asp:LinkButton ID="lnkUpdate" Text="Update" runat="server" OnClick="OnUpdate" />
<asp:LinkButton ID="lnkCancel" Text="Cancel" runat="server" OnClick="OnCancel" />--%>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ButtonType="Button" />
</Columns>
<HeaderStyle BackColor="#66CCFF" ForeColor="White"></HeaderStyle>
<SelectedRowStyle BackColor="Yellow" />
</asp:GridView>
</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button ID="Save" runat="server" Height="25px" Text="Save" Width="73px" BackColor="#66FFFF" CssClass="inputbtn" Font-Names="Aharoni" ForeColor="#000066" OnClick="Save_Click" />
<asp:Button ID="btn_Update" runat="server" Height="25px" Text="Update" Width="73px" BackColor="#66FFFF" CssClass="inputbtn" Font-Names="Aharoni" ForeColor="#000066" OnClick="btn_Update_Click" />
</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</form>
</body>
</html>
here is 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;
namespace WebApplication1
{
public partial class BalePack : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SPS;MultipleActiveResultSets=True;");
// QryMgmt dm = new QryMgmt();
DataTable dt = new DataTable();
// DataRow dr;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//this.BindGrid();
// txtpno.Text = String.Empty;
dt.Columns.Add("Prdno");
dt.Columns.Add("Descriptionitem");
dt.Columns.Add("Codeitem");
dt.Columns.Add("_PID");
dt.Columns.Add("orderno");
dt.Columns.Add("QTY");
ViewState["dt"] = dt;
itemload();
}
}
private void itemload()
{
con.Open();
SqlDataAdapter adpbp = new SqlDataAdapter("select * from ItemMasterFile ", con);
DataSet dsbp = new DataSet();
adpbp.Fill(dsbp);
DropDownList1.DataSource = dsbp.Tables[0];
DropDownList1.DataTextField = "Descriptionitem";
DropDownList1.DataValueField = "Codeitem";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem("Select Item Name", ""));
con.Close();
}
protected void TextBox1_TextChanged(object sender, EventArgs e)
{
String strQuery = "select Codeitem,Orderno,Prdqty From Probale where" +
" Prdno = @Prdno";
// SqlConnection con = new SqlConnection();
SqlCommand cmde = new SqlCommand();
cmde.Parameters.AddWithValue("@Prdno", TextBox1.Text);
cmde.CommandType = CommandType.Text;
cmde.CommandText = strQuery;
cmde.Connection = con;
try
{
con.Open();
SqlDataReader sdr = cmde.ExecuteReader();
while (sdr.Read())
{
//DropDownList1.SelectedValue = sdr[0].ToString();
DropDownList1.SelectedValue = sdr["Codeitem"].ToString();
txtorderno.Text = sdr["Orderno"].ToString();
txtqty.Text = sdr["Prdqty"].ToString();
lbmsg.Text = "";
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if
(txtOrder.Text == "")
{
Response.Write("<script language='javascript'>alert('Please Input Orderno');</script>");
}
else
{
if
(txtorderno.Text == "")
{
Response.Write("<script language='javascript'>alert('Please Input Bale No');</script>");
}
else if
(txtqty.Text == "")
{
Response.Write("<script language='javascript'>alert('Please Input QTY');</script>");
}
else if
((int.Parse(txtOrder.Text) != (int.Parse(txtorderno.Text))))
lbmsg.Text = "Bale is Not in order";
else if
(txtorderno.Text == "")
{
Response.Write("<script language='javascript'>alert('Please Input Orderno');</script>");
}
else if (DropDownList1.SelectedValue == "")
{
Response.Write("<script language='javascript'>alert('Please Input Item');</script>");
}
else
{
{
if (DropDownList1.SelectedItem != null)
{
// get value and text which item you have selected
int codeitem = Convert.ToInt32(DropDownList1.SelectedItem.Value.ToString());
string Descriptionitem = DropDownList1.SelectedItem.Text.ToString();
//delete the selected item from dropdownlist
//DataTable dtdrop = ViewState["dropdownlistdata"] as DataTable;
//for (int i = dtdrop.Rows.Count - 1; i >= 0; i--)
//{
// DataRow dr1 = dtdrop.Rows[i];
// if (dr1["Codeitem"].ToString() == codeitem.ToString())
// dr1.Delete();
//}
// dtdrop.AcceptChanges();
//ViewState["dropdownlistdata"] = dtdrop;
//DropDownList1.DataSource = dtdrop;
//DropDownList1.DataBind();
//add the selected item to gridview
DataTable dtgridview = ViewState["dt"] as DataTable;
DataRow dr2 = dtgridview.NewRow();
dr2["Prdno"] = TextBox1.Text;
dr2["Codeitem"] = codeitem;
dr2["Descriptionitem"] = Descriptionitem;
dr2["Orderno"] = txtorderno.Text;
dr2["QTY"] = txtqty.Text;
dtgridview.Rows.Add(dr2);
ViewState["dt"] = dtgridview;
GridView1.DataSource = dtgridview;
GridView1.DataBind();
clear();
}
}
}
}
}
private void clear()
{
TextBox1.Text = "";
txtqty.Text = "";
txtorderno.Text = "";
DropDownList1.SelectedValue = "";
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
// if (e.Row.RowType == DataControlRowType.DataRow)
// {
// string CodeItem = e.Row.Cells[0].Text;
// foreach (Button button in e.Row.Cells[3].Controls.OfType<Button>())
// {
// if (button.CommandName == "Delete")
// {
// button.Attributes["onclick"] = "if(!confirm('Do you want to delete " + CodeItem + "?')){ return false; };";
// }
// }
// }
// }
//}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int index = Convert.ToInt32(e.RowIndex);
DataTable dt = (DataTable)ViewState["dt"];
dt.Rows[index].Delete();
ViewState["dt"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGrid();
}
private void BindGrid()
{
GridView1.DataSource = ViewState["dt"] as DataTable;
GridView1.DataBind();
}
protected void txtRefno_TextChanged(object sender, EventArgs e)
{
String strQuery = "select Orderno From SalesOrder where" +
" Order_Ref_No = @Order_Ref_No";
// SqlConnection con = new SqlConnection();
SqlCommand cmde = new SqlCommand();
cmde.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
cmde.CommandType = CommandType.Text;
cmde.CommandText = strQuery;
cmde.Connection = con;
try
{
con.Open();
SqlDataReader sdr = cmde.ExecuteReader();
while (sdr.Read())
{
txtOrder.Text = sdr["Orderno"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Save_Click(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand("[Sp_BalPakinsert]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Order_Ref_No", txtRefno.Text);
cmd.Parameters.AddWithValue("@OrderNo", txtOrder.Text);
con.Open();
_PID = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
DataTable dt = (DataTable)ViewState["dt"];
int codeitem, qty, orderno, prdno;
foreach (DataRow row in dt.Rows)
{
//pid = int.Parse(row["_PID"].ToString());
prdno = int.Parse(row["Prdno"].ToString());
orderno = int.Parse(row["Orderno"].ToString());
qty = int.Parse(row["QTY"].ToString());
codeitem = int.Parse(row["Codeitem"].ToString());
this.InsertRows(codeitem, qty, orderno, prdno);
}
}
}
private void InsertRows(int codeitem, int qty, int orderno, int prdno)
{
con.Open();
using (SqlCommand cmd = new SqlCommand("[SP_BalPakDetail_Insert]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@PDID", _PID);
cmd.Parameters.AddWithValue("@PID", _PID);
cmd.Parameters.AddWithValue("@CodeItem", codeitem);
cmd.Parameters.AddWithValue("@QTY", qty);
cmd.Parameters.AddWithValue("@Orderno", orderno);
cmd.Parameters.AddWithValue("@Prdno", prdno);
cmd.ExecuteNonQuery();
con.Close();
}
}
public int _PID { get; set; }
// Search By Pack No of Bale //
protected void txtpackno_TextChanged(object sender, EventArgs e)
{
String strQuery = "select Orderno,Order_Ref_No From tbl_BalPacM where" +
" PID = @PID";
// SqlConnection con = new SqlConnection();
SqlCommand cmde = new SqlCommand();
cmde.Parameters.AddWithValue("@PID", txtpackno.Text);
cmde.CommandType = CommandType.Text;
cmde.CommandText = strQuery;
cmde.Connection = con;
try
{
con.Open();
SqlDataReader sdr = cmde.ExecuteReader();
while (sdr.Read())
{
txtOrder.Text = sdr["Orderno"].ToString();
txtRefno.Text = sdr["Order_Ref_No"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
SqlCommand cmdbno = new SqlCommand("[SP_PackViewbyID]", con);
cmdbno.CommandType = CommandType.StoredProcedure;
cmdbno.Parameters.AddWithValue("PID", txtpackno.Text);
SqlDataAdapter adptbno = new SqlDataAdapter(cmdbno);
DataTable dtbno = new DataTable();
adptbno.Fill(dtbno);
GridView1.DataSource = dtbno;
GridView1.DataBind();
cmdbno.Dispose();
con.Close();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbl = (Label)e.Row.FindControl("Prdno");
}
}
protected void btn_Update_Click(object sender, EventArgs e)
{
}
}
}
Please guide..
How to update|Delete|insert row after retrieving data from Database in Master Detail Form ?
