A better way to add a row to a gridview

[Larry Bud]

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.ConnectionStrings("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.CloseConnection)
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.ConnectionStrings("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