Home > ASP.Net > Insert and Update and delete using SqlDataSource

Insert and Update and delete using SqlDataSource


try this example to insert / update and delete using SqlDataSource

.aspx

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>Grid View Add Update Delete</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:label id="lblMessage" runat="server" Font-Bold="True" />
	<asp:gridview id="gvShippers" runat="server" allowpaging="True" allowsorting="True"
		autogeneratecolumns="False" datakeynames="ShipperID" datasourceid="sdsShippers"
		emptydatatext="There are no data records to display." showfooter="True" 
		style="margin-top: 20px;" onrowcommand="gvShippers_RowCommand" 
		onrowdeleted="gvShippers_RowDeleted" onrowupdated="gvShippers_RowUpdated" 
		onpageindexchanged="gvShippers_PageIndexChanged" 
		onrowediting="gvShippers_RowEditing" 
		onselectedindexchanged="gvShippers_SelectedIndexChanged" CellPadding="4" ForeColor="#333333">
		<columns>
			<asp:templatefield headertext="Actions">
				<itemtemplate>
					<asp:linkbutton id="btnEdit" runat="server" commandname="Edit" text="Edit" CausesValidation="False" />
					<asp:linkbutton id="btnDelete" runat="server" commandname="Delete" onclientclick="return confirm('Are you sure you want to delete this shipper?');"
						text="Delete" CausesValidation="False" />
					<asp:linkbutton id="btnSelect" runat="server" commandname="Select" text="Select" CausesValidation="False" />
				</itemtemplate>
				<edititemtemplate>
					<asp:linkbutton id="btnUpdate" runat="server" commandname="Update" text="Update" CausesValidation="False" />
					<asp:linkbutton id="btnCancel" runat="server" commandname="Cancel" text="Cancel" CausesValidation="False" />
				</edititemtemplate>
				<footertemplate>
					<asp:linkbutton id="btnInsert" runat="server" commandname="Insert" text="Insert" />
					<asp:linkbutton id="btnCancel" runat="server" commandname="Cancel" text="Cancel" CausesValidation="False" />
				</footertemplate>
			</asp:templatefield>
			<asp:boundfield datafield="ShipperID" headertext="ShipperID" readonly="True" 
				sortexpression="ShipperID" />
			<asp:templatefield headertext="CompanyName" sortexpression="CompanyName">
				<itemtemplate>
					<%# Eval("CompanyName") %>
				</itemtemplate>
				<edititemtemplate>
					<asp:textbox id="txtCompanyName" runat="server" text='<%# Bind("CompanyName") %>' />
				</edititemtemplate>
				<footertemplate>
					<asp:textbox id="txtCompanyName" runat="server" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtCompanyName"
                        ErrorMessage="RequiredFieldValidator">*</asp:RequiredFieldValidator>
				</footertemplate>
			</asp:templatefield>	
			<asp:templatefield headertext="Phone" sortexpression="Phone">
				<itemtemplate>
					<%# Eval("Phone")%>
				</itemtemplate>
				<edititemtemplate>
					<asp:textbox id="txtPhone" runat="server" text='<%# Bind("Phone") %>' />
				</edititemtemplate>
				<footertemplate>
					<asp:textbox id="txtPhone" runat="server" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtPhone"
                        ErrorMessage="RequiredFieldValidator">*</asp:RequiredFieldValidator>
				</footertemplate>
			</asp:templatefield>
		</columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#EFF3FB" />
        <EditRowStyle BackColor="#2461BF" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
	</asp:gridview>
	<asp:sqldatasource id="sdsShippers" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
		deletecommand="DELETE FROM [Shippers] WHERE [ShipperID] = @ShipperID" insertcommand="INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone); SELECT @ShipperID = SCOPE_IDENTITY();"
		selectcommand="SELECT * FROM [Shippers]" updatecommand="UPDATE [Shippers] SET [CompanyName] = @CompanyName, [Phone] = @Phone WHERE [ShipperID] = @ShipperID"
		oninserted="sdsShippers_Inserted">
		<updateparameters>
			<asp:parameter name="ShipperID" type="Int32" />
			<asp:parameter name="CompanyName" type="String" />
			<asp:parameter name="Phone" type="String" />
		</updateparameters>
		<insertparameters>
			<asp:parameter direction="Output" name="ShipperID" type="Int32" />
			<asp:parameter name="CompanyName" type="String" />
			<asp:parameter name="Phone" type="String" />
		</insertparameters>
		<deleteparameters>
			<asp:parameter name="ShipperID" type="Int32" />
		</deleteparameters>
	</asp:sqldatasource>
        <br />

    </div>
    </form>
</body>
</html>

 .aspx.cs

using System;
using System.Data;
using System.Configuration;
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.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void gvShippers_SelectedIndexChanged(object sender, EventArgs e)
    {
        gvShippers.EditIndex = -1;

        lblMessage.Text = String.Format(
            "Shipper '{0}' successfully selected.",
            gvShippers.SelectedDataKey["ShipperID"]);
    }

    protected void gvShippers_PageIndexChanged(object sender, EventArgs e)
    {
        gvShippers.EditIndex = -1;
        gvShippers.SelectedIndex = -1;
    }

    protected void gvShippers_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvShippers.SelectedIndex = -1;
    }

    protected void gvShippers_RowUpdated(object sender, GridViewUpdatedEventArgs e)
    {
        if ((e.Exception == null) && e.AffectedRows.Equals(1))
        {
            lblMessage.Text = String.Format(
                "Shipper '{0}' successfully updated.",
                e.Keys["ShipperID"]);
        }
        else
        {
            lblMessage.Text = "Unable to successfully update shipper.";
            e.ExceptionHandled = true;
        }
    }

    protected void gvShippers_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("Insert"))
        {
            TextBox txtCompanyName = gvShippers.FooterRow.FindControl("txtCompanyName") as TextBox;
            TextBox txtPhone = gvShippers.FooterRow.FindControl("txtPhone") as TextBox;

            if (txtCompanyName == null) { return; }
            if (txtPhone == null) { return; }

            sdsShippers.InsertParameters["CompanyName"].DefaultValue = txtCompanyName.Text;
            sdsShippers.InsertParameters["Phone"].DefaultValue = txtPhone.Text;

            sdsShippers.Insert();
        }
    }

    protected void gvShippers_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {
        if ((e.Exception == null) && e.AffectedRows.Equals(1))
        {
            lblMessage.Text = String.Format(
                "Shipper '{0}' successfully deleted.",
                e.Keys["ShipperID"]);
        }
        else
        {
            lblMessage.Text = "Unable to successfully delete shipper.";
            e.ExceptionHandled = true;
        }
    }

    protected void sdsShippers_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        if ((e.Exception == null) && e.AffectedRows.Equals(1))
        {
            lblMessage.Text = String.Format(
                "Shipper '{0}' successfully added.",
                e.Command.Parameters["@ShipperID"].Value);
        }
        else
        {
            lblMessage.Text = "Unable to successfully add shipper.";
            e.ExceptionHandled = true;
        }
    }

}
Good Luck
Advertisements
Categories: ASP.Net
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: