can you give me manual coding for editing , updating , deleting and selecting a row in gridview using storedprocedures,It must not use SqlDataSourceControl

Question

can you give me manual coding for editing , updating , deleting and selecting a row in gridview using storedprocedures,It must not use SqlDataSourceControl, Iam suffering from 5 days .please send me this article code.

bhargav · Answer

Me itself solved the problem .cs file contains using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Text; using System.Data.SqlClient; using System.Configuration; public partial class GridviewEditing : System.Web.UI. Page { protected void Page_Load( object sender, EventArgs e) { if (!Page.IsPostBack) { BindData(); } } public void BindData() { string constring = ConfigurationManager .AppSettings[ "connection" ]; SqlConnection con = new SqlConnection (constring); //First we make a SqlCommand object and name it cmd. The SqlCommand object takes a stored procedure as an input and the SqlConnection. //create procedure sp_display1 as Begin select EmpID,EmpName,Department,Designation,Salary from Employer end; //We feed the command object to the DataAdapter object named as da. //A dataset is declared which is filled with the result of the Stored procedure. Gridview ID=gvDisplay //gvDisplay.DataBind() binds the datagrid to the page. Don't forget to bind the grid or else it won't be displayed. //Later we opened the connection and execute the query. try { con.Open(); SqlCommand cmd = new SqlCommand ( "sp_display1" , con); SqlDataAdapter da = new SqlDataAdapter (cmd); da.SelectCommand.CommandType = CommandType .StoredProcedure; DataSet ds = new DataSet (); da.Fill(ds, "Employer" ); gvDisplay.DataSource = ds; gvDisplay.DataBind(); con.Close(); } catch ( Exception ex) { Response.Write(ex.ToString()); } } //If we press the link Delete in the datagrid ,to conform whether the user is interested to delete row we will //make use of this function in the OnRowDataBound event of gridview protected void gvDisplay_RowDataBound( object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType .DataRow) { if (e.Row.FindControl( "DeleteUser" ) != null ) { (( LinkButton )(e.Row.FindControl( "DeleteUser" ))).OnClientClick = "return confirm('Are you sure you want to delete?');" ; } } } //To Edit we can call this function in the OnRowEditing event of Gridview protected void gvDisplay_RowEditing( object sender, GridViewEditEventArgs e) { // We use GridViewEditEventArgs e to get the row which is being clicked // This also changes the DataGrid labels into Textboxes so user can edit them gvDisplay.EditIndex = e.NewEditIndex; // Always bind the data so the datagrid can be displayed. BindData(); } //When we Update Gridview to reflect those changes into DataBase we will call this function into OnRowUpdating event protected void gvDisplay_RowUpdating( object sender, GridViewUpdateEventArgs e) { // By using System.Configuration.ConfigurationManager class here we are calling the connection string //in the AppSettings from the Web.Config file string constring = ConfigurationManager .AppSettings[ "connection" ]; //Creating an object for the SqlConnection class as con SqlConnection con = new SqlConnection (constring); //Opening the connection con.Open(); //Creating an object for the SqlCommand class as myCommand and the SqlCommand object takes a stored procedure sp_update2 as an input and the SqlConnection. //create procedure sp_update2 ( @EmpID int,@EmpName varchar(50),@Department varchar(50),@Designation varchar(50), @Salary decimal(12,2)) //as Begin //update Employer set EmpID=@EmpID,EmpName=@EmpName,Department=@Department,Designation=@Designation,Salary=@Salary Where EmpID=@EmpID //end; SqlCommand myCommand = new SqlCommand ( "sp_update2" , con); myCommand.CommandType = CommandType .StoredProcedure; //We declare a variables of string type and we are calling EmpID,EmpName etc... in to Variables.Those variables are inputs for parametric command objects // The reason of declaring a TextBox is that the value that we want is inside the TextBox which is inside the GridView control. string Id = (( Label )gvDisplay.Rows[e.RowIndex].FindControl( "lblEmpID" )).Text; string Name = (( TextBox )gvDisplay.Rows[e.RowIndex].FindControl( "txtEmpName" )).Text; string Dept = (( TextBox )gvDisplay.Rows[e.RowIndex].FindControl( "txtDepartment" )).Text; string Designat = (( TextBox )gvDisplay.Rows[e.RowIndex].FindControl( "txtDesignation" )).Text; string Sal = (( TextBox )gvDisplay.Rows[e.RowIndex].FindControl( "txtSalary" )).Text; //After marking the command object with the stored procedure we passed the parameters. myCommand.Parameters.Add( new SqlParameter ( "@EmpID" , SqlDbType .Int)); myCommand.Parameters[ "@EmpID" ].Value = Id; myCommand.Parameters.Add( new SqlParameter ( "@EmpName" , SqlDbType .VarChar, 50)); myCommand.Parameters[ "@EmpName" ].Value = Name; myCommand.Parameters.Add( new SqlParameter ( "@Department" , SqlDbType .VarChar, 50)); myCommand.Parameters[ "@Department" ].Value = Dept; myCommand.Parameters.Add( new SqlParameter ( "@Designation" , SqlDbType .VarChar, 50)); myCommand.Parameters[ "@Designation" ].Value = Designat; myCommand.Parameters.Add( new SqlParameter ( "@Salary" , SqlDbType .Decimal, 18)); myCommand.Parameters[ "@Salary" ].Value = Sal; //Here we are Executing command object. myCommand.ExecuteNonQuery(); // All we do in the cancel method is to assign '-1' to the datagrid EditIndex // Once the EditIndex is set to '-1' the datagrid returns back to its original condition gvDisplay.EditIndex = -1; BindData(); } //When you change your mind about editing you can press cancel link and it is called in OnRowCancelingEdit event protected void gvDisplay_RowCancelingEdit( object sender, GridViewCancelEditEventArgs e) { // All we do in the cancel method is to assign '-1' to the datagrid EditIndex // Once the EditIndex is set to '-1' the datagrid returns back to its original condition gvDisplay.EditIndex = -1; BindData(); } //To Delete we can call this function in the OnRowDeleting event of Gridview protected void gvDisplay_RowDeleting( object sender, GridViewDeleteEventArgs e) { string constring = ConfigurationManager .AppSettings[ "connection" ]; SqlConnection con = new SqlConnection (constring); //Gridview row employeeid value is retrieved here. string DelID = (( Label )gvDisplay.Rows[e.RowIndex].FindControl( "lblEmpID" )).Text; con.Open(); //Selected row is deleted using this command object. SqlCommand myCommand = new SqlCommand ( "Delete from Employer where EmpID= " + DelID + " ;" , con); myCommand.ExecuteNonQuery(); con.Close(); // All we do in the cancel method is to assign '-1' to the datagrid EditIndex // Once the EditIndex is set to '-1' the datagrid returns back to its original condition gvDisplay.EditIndex = -1; BindData(); } public string GetData( object o) { string str = Convert .ToString(o); if ((str == null ) || (str == string .Empty) || (str == "-1.00" )) return "-" ; else return str.Trim(); } // OnSelectedIndexChanging="gvDisplay_SelectedIndexChanging" //protected void gvDisplay_SelectedIndexChanging(object sender, GridViewSelectEventArgs e) //{ // string Id = ((Label)gvDisplay.Rows[e.RowIndex].FindControl("lblEmpID")).Text; // string Name = ((TextBox)gvDisplay.Rows[e.RowIndex].FindControl("txtEmpName")).Text; // string Dept = ((TextBox)gvDisplay.Rows[e.RowIndex].FindControl("txtDepartment")).Text; // string Designat = ((TextBox)gvDisplay.Rows[e.RowIndex].FindControl("txtDesignation")).Text; // string Sal = ((TextBox)gvDisplay.Rows[e.RowIndex].FindControl("txtSalary")).Text; //} //private void sending() //{ // //Response.Redirect("ViewDetails.aspx?id"=+((LinkButton )gvDisplay.FindControl("ViewUser")).Text); //} protected void ViewUser_Click( object sender, EventArgs e) { } } .aspx file contains @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="GridviewEditing.aspx.cs" Inherits ="GridviewEditing" %> 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 id ="Head1" runat ="server"> title > Untitled Page title > head > body > form id ="form1" runat ="server"> = DateTime .Now %> div > asp : Label ID ="Label2" runat ="server" Style ="position: relative" Text =" "> asp : Label > asp : GridView ID ="gvDisplay" runat ="server" Style ="left: 31px; position: relative; top: 87px" AutoGenerateColumns ="False" OnRowUpdating ="gvDisplay_RowUpdating" OnRowDataBound ="gvDisplay_RowDataBound" OnRowEditing ="gvDisplay_RowEditing" OnRowCancelingEdit ="gvDisplay_RowCancelingEdit" OnRowDeleting ="gvDisplay_RowDeleting" > Columns > asp : TemplateField HeaderText ="Employee ID"> HeaderTemplate > EmpID HeaderTemplate > ItemTemplate > asp : Label ID ="lblEmpID" runat ="server" Text =' '> asp : Label > ItemTemplate > EditItemTemplate > asp : Label ID ="lblEmpID" runat ="server" Text =' '> asp : Label > EditItemTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Employee Name"> ItemTemplate > asp : Label ID ="lblEmpName" runat ="server" Text =' '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtEmpName" runat ="server" Text =' '> asp : TextBox > EditItemTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Department"> ItemTemplate > asp : Label ID ="lblDepartment" runat ="server" Text =' '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtDepartment" runat ="server" Text =' '> asp : TextBox > EditItemTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Designation"> ItemTemplate > asp : Label ID ="lblDesignation" runat ="server" Text =' '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtDesignation" runat ="server" Text =' '> asp : TextBox > EditItemTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Salary"> ItemTemplate > asp : Label ID ="lblSalary" runat ="server" Text =' '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtSalary" runat ="server" Text =' '> asp : TextBox > EditItemTemplate > asp : TemplateField > asp : CommandField HeaderText ="Edit" ShowEditButton ="True" /> asp : TemplateField HeaderText ="Delete" > ItemTemplate > asp : LinkButton runat ="server" ID ="DeleteUser" CausesValidation ="false" CommandArgument =' ' CommandName ="Delete"> Delete asp : LinkButton > ItemTemplate > asp : TemplateField > asp : TemplateField HeaderText ="View Details" > ItemTemplate > --This code is to get a popup window using javascript-- %> a href ="#" title ="Viewdetails" id ="Viewdetails" onclick ="javascript:window.open('ViewDetails.aspx?id= , , , , ','np', 'width=500, height=400, top=200, left=150, scrollbars=1')"> View Details a > ItemTemplate > asp : TemplateField > --This code is used to navigate to other page -- %> asp : HyperLinkField DataNavigateUrlFields ="EmpID,EmpName,Department,Designation,salary" DataNavigateUrlFormatString ="ViewDetails.aspx?id={0},{1},{2},{3},{4}" HeaderText ="view details" Text ="View" /> Columns > asp : GridView >   div > form > body > html >

can you give me manual coding for editing , updating , deleting and selecting a row in gridview using storedprocedures,It must not use SqlDataSourceControl

Frogleg

Vilas Gite