After spending two days looking at the options in adding a row to a
gridview, I came up with my own that I'd like to share. I only can
share pseudo-code right now, but maybe in the future I'll write a full
fledged article.
I didn't like the options, such as putting your controls (really,
DUPLICATING your controls) in the Footer for a variety of reasons.
First, I don't like the idea of having to do things twice, including
any validation. 2nd, I really want to add a row to the TOP so the
user doesn't have to scroll down. So what to do?
Here's the idea:
--There is a separate button for the "Add New Row" function.
--The button will programmatically add a row to the top of the data
source of the GridView, rebind the control, place that row into edit
mode, and pre-populate any required fields. The primary key should be
set to a value that wouldn't ordinarily be used to identify this row
as "new". In my case, I set the PK to -1
--In the update command (there is no separate Insert command), we
check to see if the PK is -1. If so, we do an "insert into" else we
do an "update table..."
--The rest of the function of the GridView works the same, and
everything else is pretty much automatically handled.
----------------
I created a Business Object for my datasource, with an Update, Select,
and Delete command.
My SelectCommand gets passed a value, either 0 or 1. If 1, we're
going to add a blank row to the top of the datatable...
Shared Function SelectCommand(ByVal blankrow As Integer) As DataTable
Dim MyDataTable As New DataTable()
Dim MyConnection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("CS").ConnectionString)
Dim MyCommand As New SqlCommand()
Dim MyReader As SqlDataReader
Dim ms As String
ms = "SELECT ...... " ' put your select statement here with
parameters
MyCommand.CommandText = ms
MyCommand.Connection = MyConnection
MyCommand.Connection.Open()
MyReader =
MyCommand.ExecuteReader(CommandBehavior.CloseConne ction)
MyDataTable.Load(MyReader)
If blankrow Then
Dim myRow As DataRow
myRow = MyDataTable.NewRow
myRow.Item("field1") = ""
myRow.Item("membership_id") = -1 'this is my primary key
myRow.Item("field2") = 0
MyDataTable.Rows.InsertAt(myRow, 0)
End If
MyCommand.Dispose()
MyConnection.Dispose()
Return MyDataTable
End Function
--------------
Now, when a user click on the Add New Row button, we do the
following....
GridView1.Columns(0).Visible = False 'I like to hide the
delete column when in Edit Mode
GridView1.DataSource = SelectCommand(1) ' we reselect the
Selectcommand, passing a 1
GridView1.DataSourceID = Nothing
GridView1.DataBind()
GridView1.EditIndex = 0 'and we're going to edit the first
row
--------------
So the user click the Add New Row button, a blank row is added to the
top of the GridView, and is placed into Edit mode.
In the UpdateCommand, we do the following:
Dim mySql As String
Dim MyConnection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("cs").ConnectionString)
Dim MyCommand As New SqlCommand()
If membership_id = -1 Then
mySql = "Insert into table... set field1=@field1 "
Else
mySql = "Update table set field1=@field1 where pk=@pk"
End If
MyCommand.CommandText = mySql
MyCommand.Connection = MyConnection
With MyCommand.Parameters
.Add("@param1", SqlDbType.Int).Value = field1
'add all parameters
End With
Dim result As Integer = 0
Try
MyConnection.Open()
result = MyCommand.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
MyConnection.Close()
End Try
Return result
