With Asp.Net 2.0, the GridView control is amazingly powerful for displaying and editing tabular data on a web page. However, its main weakness is the lack of an mechanism to allow the user to add new recodrs directly on the GridView. Luckily, with a little modification, we can have that functionality with the GridView.
First of all is the select statement... It need to be structured somehow that when the query runs, the returned datatable contains an empty row at row index 0. So we just select an empty row and union with our normal select. The empty row must have the same schema as the table.
Execute the query and bind the data to your gridview just as you normally do.
'Note that all the '' within the statement is 2 single quotes (on screen they look like a double quote)
sqlSelect = "SELECT '' As [fieldA], '' As [fieldB], '' As [fieldC] UNION Select [fieldA], [fieldB], [fieldC] FROM [tableName]"
Now we need to handle the RowDataBound event of the GridView. Since we know that hte empty row is on the very top (row zero), so we mainly interested in that row. We check if row(0)'s rowstate = DataControlRowState.Normal then we intercept it, change the text on the Edit linkbutton to "Add", and remove any other controls in the cell. When removing controls, we need to remove them backwards so that the controls index sequence won't get messed up.
Now, we have to handle the gridview's RowUpdating event. In this event handler, we read the data of each field and store them in a HashTable that has the column names as the keys, and the value is the value for that field.
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
If e.Row.RowIndex = 0 Then
If e.Row.RowState = DataControlRowState.Normal Then
Dim lnkBtn0 As LinkButton = CType(e.Row.Cells(0).Controls(0), LinkButton)
lnkBtn0.Text = "Add" 'Change the text of Edit LinkButton to "Add"
e.Row.Cells(0).Controls.RemoveAt(2) 'Remove the Delete LinkButton
e.Row.Cells(0).Controls.RemoveAt(1) 'Remove the space LiteralControl between Edit and Delete
'Add confirmation on delete
Dim ctrl As Control = e.Row.Cells(0).Controls(2)
Dim delBtn As LinkButton = CType(ctrl, LinkButton)
If delBtn.Text = "Delete" Then
delBtn.Attributes.Add("onclick", "return confirm('WARNING: This action will permanently delete the record. Continue?');")
After that, we used the hash table to update our database.
And that's all there is to it....
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
If e.RowIndex = 0 Then
'Creating a hashtable and store data to it
Dim hsh As New System.Collections.Hashtable
For Each x As System.Collections.DictionaryEntry In e.NewValues
hsh(x.Key.ToString) = x.Value
'Now that we have the data to update our DB in a hashtable.
'You just need to write a method to update your database using the hash table.
'As in this code, I have a Sub named AddStoreRecord which takes a hastable as argument and insert a new record to DB.