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 programmaticall y 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(B yVal blankrow As Integer) As DataTable
Dim MyDataTable As New DataTable()
Dim MyConnection As New
SqlConnection(C onfigurationMan ager.Connection Strings("CS").C onnectionString )
Dim MyCommand As New SqlCommand()
Dim MyReader As SqlDataReader
Dim ms As String

ms = "SELECT ...... " ' put your select statement here with
parameters

MyCommand.Comma ndText = ms
MyCommand.Conne ction = MyConnection
MyCommand.Conne ction.Open()

MyReader =
MyCommand.Execu teReader(Comman dBehavior.Close Connection)
MyDataTable.Loa d(MyReader)

If blankrow Then
Dim myRow As DataRow
myRow = MyDataTable.New Row

myRow.Item("fie ld1") = ""
myRow.Item("mem bership_id") = -1 'this is my primary key
myRow.Item("fie ld2") = 0

MyDataTable.Row s.InsertAt(myRo w, 0)
End If

MyCommand.Dispo se()
MyConnection.Di spose()

Return MyDataTable
End Function
--------------
Now, when a user click on the Add New Row button, we do the
following....

GridView1.Colum ns(0).Visible = False 'I like to hide the
delete column when in Edit Mode
GridView1.DataS ource = SelectCommand(1 ) ' we reselect the
Selectcommand, passing a 1
GridView1.DataS ourceID = Nothing
GridView1.DataB ind()
GridView1.EditI ndex = 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(C onfigurationMan ager.Connection Strings("cs").C onnectionString )
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.Comma ndText = mySql
MyCommand.Conne ction = MyConnection

With MyCommand.Param eters
.Add("@param1", SqlDbType.Int). Value = field1
'add all parameters
End With
Dim result As Integer = 0

Try
MyConnection.Op en()
result = MyCommand.Execu teNonQuery()
Catch ex As Exception
Throw ex
Finally
MyConnection.Cl ose()

End Try

Return result