Insert,Update and Delete Using Grid View and ObjectDataSource

Here, I will demonstrate how to use ObjectDataSource with Grid view for Insert,Update and Delete functionality.

Download Complete Source Code and Store procedure Script here

Insert :-

I have used InsertParameters of ObjectDataSource for Inserting record into DB using Grid view. In grid view, footer has been used for inserting new row. Necessary text box and Button added to save record into Database. Screen looks like below one

I have explicitly mention Insert Parameters in ObjectDataSource. As CustomerID is an auto increment and Primary Key so no need to insert it. Otherwise I will get error “ObjectDataSource 'Customer' could not find a non-generic method 'InsertCustomer' that has parameters: FirstName, MiddleName, LastName, Desgination, Address, City, State, Country, CustomerID.” So its necessary to mention correct parameters. And I would like to suggest that its better to mention parameters explicitly.

Note :-
The ObjectDataSource uses reflection to match its parameters against the parameters of the method that its calls. The Order of the parameters does not matter and the case of the parameters does not matter. However, the one thing that does matter is the names of the parameters.

ObjectDataSource for InsertParameter looks like below one

<asp:Parameter Name="FirstName" />

<asp:Parameter Name="MiddleName" />

<asp:Parameter Name="LastName" />

<asp:Parameter Name="Desgination" />

<asp:Parameter Name="Address" />

<asp:Parameter Name="City" />

<asp:Parameter Name="State" />

<asp:Parameter Name="Country" />

</InsertParameters>

I will also pass InsertMethod property of ObjectDataSource,which will have an InsertCustomer method.

InsertCustomer method looks like below one :-

public void InsertCustomer(string FirstName, string MiddleName,

string LastName, string Desgination, string Address,

string City, string State, string Country)

{

SqlConnection con = new SqlConnection(conStr);

SqlCommand cmd = new SqlCommand("InsertCustomer", con);

cmd.CommandType = CommandType.StoredProcedure;

//this check is necessary, when u don't pass any value as it will pass as [default] and will give error

if (string.IsNullOrEmpty(FirstName))

FirstName = string.Empty;

if (string.IsNullOrEmpty(LastName))

LastName = string.Empty;

if (string.IsNullOrEmpty(MiddleName))

MiddleName = string.Empty;

if (string.IsNullOrEmpty(Desgination))

Desgination = string.Empty;

if (string.IsNullOrEmpty(Address))

Address = string.Empty;

if (string.IsNullOrEmpty(City))

City = string.Empty;

if (string.IsNullOrEmpty(State))

State = string.Empty;

if (string.IsNullOrEmpty(Country))

Country = string.Empty;

cmd.Parameters.AddWithValue("@IV_FirstName", FirstName);

cmd.Parameters.AddWithValue("@IV_LastName", LastName);

cmd.Parameters.AddWithValue("@IV_MiddleName", MiddleName);

cmd.Parameters.AddWithValue("@IV_Desgination", Desgination);

cmd.Parameters.AddWithValue("@IV_Address", Address);

cmd.Parameters.AddWithValue("@IV_City", City);

cmd.Parameters.AddWithValue("@IV_State", State);

cmd.Parameters.AddWithValue("@IV_Country", Country);

using (con)

{

con.Open();

cmd.ExecuteNonQuery();

}

Button Save for inserting record.

//Insert record Save Button

protected void btnSave_Click(object sender, EventArgs e)

{

Customer.InsertParameters["FirstName"].DefaultValue = GetGridTextBoxValue("txtFirstName");

Customer.InsertParameters["MiddleName"].DefaultValue = GetGridTextBoxValue("txtMiddleName");

Customer.InsertParameters["LastName"].DefaultValue = GetGridTextBoxValue("txtLastName");

Customer.InsertParameters["Desgination"].DefaultValue= GetGridTextBoxValue("txtDesgination");

Customer.InsertParameters["Address"].DefaultValue = GetGridTextBoxValue("txtAddress");

Customer.InsertParameters["City"].DefaultValue = GetGridTextBoxValue("txtCity");

Customer.InsertParameters["State"].DefaultValue = GetGridTextBoxValue("txtState");

Customer.InsertParameters["Country"].DefaultValue = GetGridTextBoxValue("txtCountry");

Customer.Insert();

}

GetGridTextBoxValue function will get TextBox text value from footer row of respective column.

//Get TextBox value of GridView Footer Row

public string GetGridTextBoxValue(string txtID)

{

try

{

TextBox txt = (TextBox)gvCustomer.FooterRow.FindControl(txtID);

return txt.Text;

}

catch (Exception ex)

{

return string.Empty;

throw ex;

}

(whole HTML mark up I will show in end of page for better understanding and of course I will provide whole source code for download)

Update :-

The Grid View automatically add the update parameters to the ObjectDataSource control’s UpdateParameters collections. But as I am using TemplateField column (this is done because adding footer row for insert purpose). So I have to explicitly mention the UpdateParameters. Set AutoGenerateEditButton="True" of Gridview to show Edit link. Screen looks like below one

First row change into textbox, and on clicking on Upate link button it will save change record into DB.

ObjectDataSource for Update Parameters looks like below one :-

<asp:Parameter Name="FirstName" />

<asp:Parameter Name="MiddleName" />

<asp:Parameter Name="LastName" />

<asp:Parameter Name="Desgination" />

<asp:Parameter Name="Address" />

<asp:Parameter Name="City" />

<asp:Parameter Name="State" />

<asp:Parameter Name="Country" />

<asp:Parameter Name="CustomerID" />

</UpdateParameters>

UpdateMethod property of ObjectDataSource,which will have an UpdateCustomer method.

UpdateCustomer method looks like below one :-

public void UpdateCustomer(int CustomerID, string FirstName, string MiddleName,

string LastName, string Desgination, string Address,

string City, string State, string Country)

{

SqlConnection con = new SqlConnection(conStr);

SqlCommand cmd = new SqlCommand("UpdateCustomer", con);

cmd.CommandType = CommandType.StoredProcedure;

//this check is necessary, when u don't pass any value as it will pass [default].

//and will give error

if (string.IsNullOrEmpty(FirstName))

FirstName = string.Empty;

if (string.IsNullOrEmpty(LastName))

LastName = string.Empty;

if (string.IsNullOrEmpty(MiddleName))

MiddleName = string.Empty;

if (string.IsNullOrEmpty(Desgination))

Desgination = string.Empty;

if (string.IsNullOrEmpty(Address))

Address = string.Empty;

if (string.IsNullOrEmpty(City))

City = string.Empty;

if (string.IsNullOrEmpty(State))

State = string.Empty;

if (string.IsNullOrEmpty(Country))

Country = string.Empty;

// Add parameters

cmd.Parameters.AddWithValue("@IV_FirstName", FirstName);

cmd.Parameters.AddWithValue("@IV_LastName", LastName);

cmd.Parameters.AddWithValue("@IV_MiddleName", MiddleName);

cmd.Parameters.AddWithValue("@IV_Desgination", Desgination);

cmd.Parameters.AddWithValue("@IV_Address", Address);

cmd.Parameters.AddWithValue("@IV_City", City);

cmd.Parameters.AddWithValue("@IV_State", State);

cmd.Parameters.AddWithValue("@IV_Country", Country);

cmd.Parameters.AddWithValue("@IV_CustomerID", CustomerID);

using (con)

{

con.Open();

cmd.ExecuteNonQuery();

}

For Update, I am using GridView RowCommand event and check for CommandName as Update. and will pass CommandArgument as RowIndex number for getting row number and get respective textbox id.

//RowCommand used for Update

protected void gvCustomer_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName=="Update")

{

Label lblCustID = new Label();

lblCustID = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[1].Controls[1] as Label ;

TextBox txtFirstName = new TextBox();

txtFirstName=gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[2].Controls[1] as TextBox;

TextBox txtMiddleName = new TextBox();

txtMiddleName = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[3].Controls[1] as TextBox;

TextBox txtLastName = new TextBox();

txtLastName = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[4].Controls[1] as TextBox;

TextBox txtDesgination = new TextBox();

txtDesgination = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[5].Controls[1] as TextBox;

TextBox txtAddress = new TextBox();

txtAddress = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[6].Controls[1] as TextBox;

TextBox txtCity = new TextBox();

txtCity = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[7].Controls[1] as TextBox;

TextBox txtState = new TextBox();

txtState = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[8].Controls[1] as TextBox;

TextBox txtCountry = new TextBox();

txtCountry = gvCustomer.Rows[Convert.ToInt16(e.CommandArgument)].Cells[9].Controls[1] as TextBox;

Customer.UpdateParameters["FirstName"].DefaultValue = txtFirstName.Text ;

Customer.UpdateParameters["MiddleName"].DefaultValue = txtMiddleName.Text;

Customer.UpdateParameters["LastName"].DefaultValue = txtLastName.Text;

Customer.UpdateParameters["Desgination"].DefaultValue = txtDesgination.Text;

Customer.UpdateParameters["Address"].DefaultValue = txtAddress.Text;

Customer.UpdateParameters["City"].DefaultValue = txtCity.Text;

Customer.UpdateParameters["State"].DefaultValue = txtState.Text;

Customer.UpdateParameters["Country"].DefaultValue = txtCountry.Text;

Customer.UpdateParameters["CustomerID"].DefaultValue = lblCustID.Text ;

}

Delete :-

Same as Update Parameter. The Grid View automatically add the delete parameters to the ObjectDataSource control’s DeleteParameters collections. But here no need to mention it explicitly, as I don’t have to pass parameters other then CustomerID, which will take automatically as i mentioned above. Make AutoGenerateDeleteButton="True" of GridView.

Here, Just mention DeleteMethod of ObjectDataSource as DeleteCustomer.Screen Shot looks like below.

DeleteCustomer method looks like below one :-

public void DeleteCustomer(int CustomerID)

{

SqlConnection con = new SqlConnection(conStr);

SqlCommand cmd = new SqlCommand("DELETE FROM Customer WHERE CustomerID=@CustomerID", con);

// Add parameters

cmd.Parameters.AddWithValue("@CustomerID", CustomerID);

using (con)

{

con.Open();

cmd.ExecuteNonQuery();

}

Generally, when we delete a record we would like to prompt a message that do u want to delete record ?. As data are very important for any projects. Here, I am doing this with Gridview RowDataBound event. Where I will get an Delete button on cell zero. Here, one important thing to mention that before assigning onClientClick event check for button CommandName as “Delete'”. This is necessary because when you try to edit record you will get two links as Update and Cancel. So, if you don’t want to update record and click on cancel it will prompt message for deleting record However, it won’t delete record.

Gridview RowDataBound event looks like below one :-

protected void gvCustomer_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

LinkButton btnDelete = new LinkButton();

btnDelete = e.Row.Cells[0].Controls[2] as LinkButton;

//below check is necessary, else it will give problem while editing record.

if (btnDelete.CommandName =="Delete")

btnDelete.OnClientClick = "return confirm('do you want to delete ?');";

}

The complete HTML markup below.

<asp:GridView ID="gvCustomer" DataSourceID="Customer" DataKeyNames="CustomerID" AutoGenerateEditButton="True"

AutoGenerateDeleteButton="True" runat="server" ShowFooter="True" AutoGenerateColumns="false"

CellPadding="4" ForeColor="#333333" GridLines="None" onrowcommand="gvCustomer_RowCommand"

onrowdatabound="gvCustomer_RowDataBound">

<asp:TemplateField Visible="false" HeaderText="CustomerID" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("CustomerID")%>' />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="First Name" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("FirstName")%>' />

</ItemTemplate>

<asp:TextBox ID="txtFirstName" Width="90px" runat="server" Text='<%#Eval("FirstName")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtFirstName" Width="90px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Middle Name" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblMiddleName" runat="server" Text='<%#Eval("MiddleName")%>' />

</ItemTemplate>

<asp:TextBox ID="txtMiddleName" Width="90px" runat="server" Text='<%#Eval("MiddleName")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtMiddleName" Width="90px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="LastName" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblLastName" runat="server" Text='<%#Eval("LastName")%>' />

</ItemTemplate>

<asp:TextBox ID="txtLastName" Width="90px" runat="server" Text='<%#Eval("LastName")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtLastName" Width="90px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Desgination" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblDesgination" runat="server" Text='<%#Eval("Desgination")%>' />

</ItemTemplate>

<asp:TextBox ID="txtDesgination" Width="90px" runat="server" Text='<%#Eval("Desgination")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtDesgination" Width="90px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address")%>' />

</ItemTemplate>

<asp:TextBox ID="txtAddress" Width="90px" runat="server" Text='<%#Eval("Address")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtAddress" Width="90px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="City" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblCity" runat="server" Text='<%#Eval("City")%>' />

</ItemTemplate>

<asp:TextBox ID="txtCity" Width="80px" runat="server" Text='<%#Eval("City")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtCity" Width="80px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="State" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblState" runat="server" Text='<%#Eval("State")%>' />

</ItemTemplate>

<asp:TextBox ID="txtState" Width="70px" runat="server" Text='<%#Eval("State")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtState" Width="70px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Country" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lblCountry" runat="server" Text='<%#Eval("Country")%>' />

</ItemTemplate>

<asp:TextBox ID="txtCountry" Width="90px" runat="server" Text='<%#Eval("Country")%>' />

</EditItemTemplate>

<asp:TextBox ID="txtCountry" Width="90px" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:ObjectDataSource ID="Customer" TypeName="ObjCustomer" SelectMethod="GetCustomer" UpdateMethod="UpdateCustomer" InsertMethod="InsertCustomer" DeleteMethod="DeleteCustomer" runat="server" >

<asp:Parameter Name="FirstName" />

<asp:Parameter Name="MiddleName" />

<asp:Parameter Name="LastName" />

<asp:Parameter Name="Desgination" />

<asp:Parameter Name="Address" />

<asp:Parameter Name="City" />

<asp:Parameter Name="State" />

<asp:Parameter Name="Country" />

</InsertParameters>

<asp:Parameter Name="FirstName" />

<asp:Parameter Name="MiddleName" />

<asp:Parameter Name="LastName" />

<asp:Parameter Name="Desgination" />

<asp:Parameter Name="Address" />

<asp:Parameter Name="City" />

<asp:Parameter Name="State" />

<asp:Parameter Name="Country" />

<asp:Parameter Name="CustomerID" />

</UpdateParameters>

</asp:ObjectDataSource>

Hope you like this article regarding how to use Insert,Update and Delete using ObjectDataSource and GridView. I have added necessary images and explain code at regular point, still if you find problem don’t forget to reply me I will help you in understanding code. (For better understanding I attached Source Code and Script which you can download from link provided at top).

Published Sunday, October 11, 2009 6:14 PM by Manoj karkera

Filed under: ObjectDataSource

Comments

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Wednesday, November 04, 2009 2:50 AM by TH Yeoh

Can I move the insert column from FooterTemplate to become 1st row? Because my list is very long and is hard for user to scroll down and insert data.

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Wednesday, November 04, 2009 4:46 AM by Manoj karkera

Why don't you use Paging of 5 or 10 records. That should work for you. I have to give a try on that what you are expecting

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Tuesday, January 26, 2010 8:26 AM by ranu

good

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Friday, May 28, 2010 2:49 AM by Raj

Very Nice Article......

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Tuesday, June 01, 2010 6:37 AM by http://www.aspdotnetfaq.com/Faq/How-to-insert-row-in-GridView-with-SqlDataSource.aspx

www.aspdotnetfaq.com/.../How-to-insert-row-in-GridView-with-SqlDataSource.aspx

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Saturday, December 04, 2010 8:37 PM by sudheerpsg

Thanks....

I want to display new row only when user clicks "Add New Row" button...and i want to show the empty row not in the footer but at the end of last row(to be appeared like a part of gridview control)....Is it possible?

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Sunday, December 05, 2010 2:31 AM by Manoj karkera

check below link codeasp.net/.../adding-dynamic-rows-in-gridview-with-textboxes

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Friday, January 21, 2011 6:00 AM by santosh

Hi I am beginner in c # and I am trying above application but unable to get desired result...in design view you find the "" first last name as column name"". but I am unable to find it, I have configured gird view with object data source. and I have also done class file also with all method insert update delete, but even I can not get ...

I have download the code and checked out it is same but no result..

is there any need to creating table.... if how can we do in object data source...

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Friday, January 21, 2011 8:51 PM by Manoj karkera

This is actually advance level, try to get first basic. Search in google.

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Tuesday, January 25, 2011 1:02 AM by @Mohankumar.S

this code is good and i ll try this

thanks.

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Tuesday, January 25, 2011 10:45 AM by Manoj karkera

Welcome :)

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Friday, April 08, 2011 4:09 AM by naveen baghel

very helpful,thanks

# re: Insert,Update and Delete Using Grid View and ObjectDataSource

Monday, May 09, 2011 8:13 AM by tuyen

Code on run true. but with field is type double or float program is false with show :Object of type 'System.Double' cannot be converted to type 'System.String'. Pro can help me with false on!!?

Title (required)
Name (required)
Your URL (optional)
Comments (required)
Remember Me?

Manoj Karkera's Blog

Search

Tags

Sponsors

News

Navigation

Archives