Insert,Update and Delete Using Grid View and ObjectDataSource

Sunday, October 11, 2009

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).

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.

TH Yeoh - Wednesday, November 4, 2009 7:50:43 AM

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

Manoj karkera - Wednesday, November 4, 2009 9:46:52 AM

Very Nice Article......

Raj - Friday, May 28, 2010 6:49:44 AM

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?

sudheerpsg - Sunday, December 5, 2010 1:37:25 AM

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?

sudheerpsg - Sunday, December 5, 2010 1:37:35 AM

check below link http://codeasp.net/articles/adding-dynamic-rows-in-gridview-with-textboxes/67/adding-dynamic-rows-in-gridview-with-textboxes

Manoj karkera - Sunday, December 5, 2010 7:31:39 AM

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...

santosh - Friday, January 21, 2011 11:00:32 AM

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

Manoj karkera - Saturday, January 22, 2011 1:51:53 AM

this code is good and i ll try this

thanks.

@Mohankumar.S - Tuesday, January 25, 2011 6:02:53 AM

Welcome :)

Manoj karkera - Tuesday, January 25, 2011 3:45:09 PM

very helpful,thanks

naveen baghel - Friday, April 8, 2011 8:09:35 AM

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!!?

tuyen - Monday, May 9, 2011 12:13:10 PM

12 Comments