DataStream
LANGUAGES: VB
TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server
Edit Within a DataGrid, Part III
Learn how to insert new rows into your DataGrid.
By Wayne S. Freeze
In the first two parts of this series, you learned how to delete and update rows in a DataGrid. This time, you'll learn how to add a row to the DataGrid and use its normal editing tools to let your user enter their information.
The insert process begins when your user presses the Add New Customer button (see Figure 1). When the button is pressed, the program calls a stored procedure, which adds a new, blank row to the table. Then the EditItemIndex property is set to the new row so your user can edit the contents of the row using the normal DataGrid facilities (see Figure 2).
Figure 1. Adding a new row to the
database begins when the user clicks on the Add New Customer button on the Web
page.
Figure 2. The user now edits the row
using the normal DataGrid facilities. Note that the user is forced to scroll
from side to side to view all the fields that can be edited and to access the
editing buttons at the end of the row.
Prepare the Database
The real trick to making this approach work is to use a table with an identity column. The identity column creates a unique identifier automatically for each row in the table when the row is added, which is always larger than the previous value. Then you can use a stored procedure similar to the one in Figure 3, which inserts a row with the appropriate default values and returns the identifier of the new row.
CREATE Procedure NewCustomer
@CustomerId Int Out
As
Insert Into Customers(Name, City, State, ZipCode, Phone,
EMailAddress,MailingList)
Values('', '', '', '', '', '', 0)
Set @CustomerId = @@Identity
Figure 3. The NewCustomer stored procedure inserts a new row into the Customers table and returns the CustomerId value for the new row.
The stored procedure begins by declaring the parameter @CustomerId as type Int. Notice that the definition also includes the keyword Out, which means whatever value you assign the parameter while in the stored procedure is returned to the calling program.
The Insert statement specifies a list of column names and the default values that will be inserted into the table. Note that the CustomerId column is not included in the list because it is the identity column. SQL Server assigns a value to this column automatically whenever a row is inserted into the table.
After the Insert statement completes, calling the @@Identity system function returns the unique value assigned to the identity column. This value is saved in the @CustomerId parameter, which is returned to the calling program.
Prepare the DataGrid
Clicking on the Insert New Customer button triggers the InsertButton_Click event (see Figure 4). This routine begins with a Try statement that traps any errors that might occur.
Sub InsertButton_Click(sender As Object, e As EventArgs)
Dim ConnectionStr As String = ConfigurationSettings.AppSettings("ConnStr")
Dim cmd As SqlCommand
Dim conn As SqlConnection
Try
conn = New SqlConnection(ConnectionStr)
cmd = New SqlCommand("NewCustomer", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@CustomerId", SqlDbType.Int)
cmd.Parameters("@CustomerId").Direction = ParameterDirection.Output
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
RefreshData()
DataGrid1.CurrentPageIndex = DataGrid1.PageCount -1
DataGrid1.DataSource = Session("Customers")
DataGrid1.DataBind()
DataGrid1.EditItemIndex = DataGrid1.Items.Count -1
UpdatePageLocation(DataGrid1.PageCount-1)
Label1.Text = "CustomerId # " & _
cmd.Parameters("@CustomerId").Value.ToString & _
" added. Press Update when complete."
Catch ex As SqlException
Label1.Text = "Database error: " & ex.message
If cmd.Connection.State <> ConnectionState.Closed Then
cmd.Connection.Close()
End If
Catch ex As Exception
Label1.Text = "General error: " & ex.message
If cmd.Connection.State <> ConnectionState.Closed Then
cmd.Connection.Close()
End If
End Try
End Sub
Figure 4. The InsertButton_Click event inserts a row into the table and prepares the DataGrid so the user may enter information into the new row.
Next, a SqlConnection object is created using the connection string extracted from the web.config file. This value is used to create a new SqlCommand object that references the NewCustomer stored procedure. After defining the @CustomerId parameter, the parameter's Direction property is set to Output. This corresponds to the Out keyword in the stored procedure definition and means the program can read this value after the stored procedure is executed.
After executing the stored procedure, the RefreshData routine is called to get a fresh copy of the data from the database, including the new row. Because the newly added row is always the last row displayed in the DataGrid, setting CurrentPageIndex to the last page and rebinding the DataGrid forces the last page to be generated.
The EditItemIndex should be set to the last row displayed in the DataGrid. The easiest place to get this value is from the Count property of the DataGrid's Items collection. Then, a call to UpdatePageLocation specifying the last page in the DataGrid is all it takes to redraw the DataGrid with the last row displayed in edit mode.
Finally, a message is displayed to the user specifying the new customer's CustomerId value, and it instructs them to fill in the appropriate values and press the Update button to save the information to the database.
This article's sample code is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.
