Inserting With GridView
We all are familiar with ASP.NET 2.0 Gridview,very useful listing control ie widely used in all ASP.NET application.By default our Gridview is capable of Editing,Deleting and Selecting,which works very fine.But many Web Developer's find a difficulty with this gridview that is its not capable of Data inserting by default.Microsoft provides controls like Detailsview for inserting,but it displays only 1 data at a time.Many Web users find easiness of inline data entering into the listing control like Gridview.Because of this incapability of Gridview many developers go for third party list controls for inline Data Entering.
Here is a small Example for altering the ASP.NET 2.0 GridView to make it useful for Inserting Data.In order to understand this post the reader must have a simple knowledge of Gridview.Here i dont provide information for by default capabilities like Editing,Deleting,Seleting.So lets have a trick with Gridview for make it capable of Inserting.
Get a view on the DataBase,which is a very simple Product-Category Scenario.
ie Products table do a have Foreign Key(FK_CategoryID) relation with Categories.
Lets starts with Categories table Data insertion using Gridview.Because Categories table dont have any foreign key column,a very simple one.
1. Drag a Gridview to your Page.Configure a Sqldatasource for Categories table with Insert,Delete,Update,Select Queries/Stored Procedures.
2. Make "ShowFooter" Property of Gridview to True.
3.Enable Edit/Delete or Add a Command Field.
4.Turn all Fields to Templates(Go through Edit Columns Option of Gridview Smart Tag.Convert Fields to Template Fields.)
5.Utilise FooterTemplates of TemplateFields to make input controls for Insert.Go through the image given below.
6.In the Category GridView i have placed a linkbutton(lbInsert) in Command Field's Footer Template.its CommandName Property is Set to 'Insert'.I placed a TextBox named txtCategoryName in Categoryname TemplateField's FooterTemplate and a textbox txtDescription in Corresponding field's FooterTemplate.
7.In short Gridview will look this.
8.The above steps is all about setting the GridView.Next i will show you how to code for it.
In the Code behind we want to handle Sqldatasource's Inserting event and Gridview's RowCommand Event.
Protected Sub sdsCategories_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) _
Handles sdsCategories.Inserting
Dim txtcategoryname As New TextBox
Dim txtdescription As New TextBox
txtcategoryname = CType(GridView1.FooterRow.FindControl("txtCategoryName"), TextBox)
txtdescription = CType(GridView1.FooterRow.FindControl("txtDescription"), TextBox)
e.Command.Parameters("@CategoryName").Value = txtcategoryname.Text
e.Command.Parameters("@Description").Value = txtdescription.Text
End Sub
In the above event we are giving values to the insert parameters of Sqldatasource from values entered in Controls of GridView's Footer using FindControl() Method of the FooterRow.
Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
If (e.CommandName = "Insert") Then
sdsCategories.Insert()
End If
End Sub
We are handling RowCommand Event of gridview because we have set CommandName Property of insert linkbutton to "Insert". In RowCommand We are using Insert() Method of SqlDatasource to insert values entered in Controls in Footer of GridView.
Insertion When a Foreign Key Column is Present
This scenario comes in the Product table,here Category is Refered as foreign Key from
Categories table.Here there is a little change needed.So in Footer Template of FKCategoryID Column of the Product GridView is Placed with a dropDownList(ddlcategories) or any other simple list Control as Shown in Image.
Here categories DropDownList is Configured with another datasource and it is set with categoryId as Data Key Field and Category name as Data Text Field.
Only Difference in code is Value for Insert parameter of FKCategoryID is Supplied from SelectedValue property of Category DropDownList in Footer.Code is shown below.
Protected Sub sdsProducts_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sdsProducts.Inserting
Dim txtProductname As New TextBox
Dim ddlCategories As New DropDownList
Dim txtDescription As New TextBox
Dim txtManufacture As New TextBox
Dim txtSKU As New TextBox
Dim txtStock As New TextBox
txtProductname = CType(GridView1.FooterRow.FindControl("txtProductName"), TextBox)
ddlCategories = CType(GridView1.FooterRow.FindControl("ddlCategories"), DropDownList)
txtDescription = CType(GridView1.FooterRow.FindControl("txtDescription"), TextBox)
txtManufacture = CType(GridView1.FooterRow.FindControl("txtManufacture"), TextBox)
txtSKU = CType(GridView1.FooterRow.FindControl("txtSKU"), TextBox)
txtStock = CType(GridView1.FooterRow.FindControl("txtStock"), TextBox)
e.Command.Parameters("@ProductName").Value = txtProductname.Text
e.Command.Parameters("@FK_CategoryID").Value=Convert.ToInt32(ddlCategories.SelectedValue)
e.Command.Parameters("@Description").Value = txtDescription.Text
e.Command.Parameters("@Manufacture").Value = txtManufacture.Text
e.Command.Parameters("@SKU").Value = txtSKU.Text
e.Command.Parameters("@Stock").Value = txtStock.Text
End Sub
