Use the DataList Control to Present and Edit Data PART 2

[Alex]

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Configuration

Public Class Main : Inherits Page

Private strConn As String =
ConfigurationSettings.AppSettings("ConnectionString")
Public dtlCustomers As DataList

Public Sub Page_Load(sender as Object, e as EventArgs)

If Not IsPostBack Then
BindTheData()
End If

End Sub

Private Sub BindTheData()

Dim objConn as new SqlConnection(strConn)
Dim strSQL as String
strSQL = "SELECT Top 5 * FROM Customers"
Dim sda as new SqlDataAdapter(strSQL, objConn)
Dim ds as new DataSet()
sda.Fill(ds,"Customers")
dtlCustomers.DataSource = ds.Tables("Customers").DefaultView
dtlCustomers.DataBind()

End Sub


Next are two short bits of code to handle the dtlCustomers_Edit and
dtlCustomers_Cancel subroutines. Remember in the aspx file above we
set several properties of the Datalist control to call events in the
code-behind file. Edit and Cancel were two of those. We set the
OnEditCommand property equal to "dtlcustomers_Edit". We also created a
button with a CommandName of "edit". The combination of the two brings
us to the edit subroutine presented below. We use the ItemIndex
property to know which row is to be edited. We also created a Cancel
button (and set the OnCancelCommand property) to get us out of edit
mode if we want to abandon changes rather than going ahead with the
update of the row. Cancel is handled easily simply by setting the
EditItemIndex property to -1.

Public Sub dtlCustomers_Edit(sender as Object, e as
DataListCommandEventArgs)

dtlCustomers.EditItemIndex = e.Item.ItemIndex
BindTheData()

End Sub

Public Sub dtlCustomers_Cancel(sender as Object, e as
DataListCommandEventArgs)

dtlCustomers.EditItemIndex = -1
BindTheData()

End Sub


The last section of code presented is the dtlCustomers_Update
subroutine and is by far the longest section. As you may recall in the
aspx page EditItemTemplate we created TextBoxes to present data for
editing. The value in those TextBox controls are used to change the
data. The values of the textboxes are gathered and placed in string
variables in the code immediately below using the FindControl method.
We now have the data after any editing that took place. Immediately
below that is our update statement which is constructed using
parameters for the column values. Below that we Add parameters to the
sqlCommand object and set the parameter values to the string variables
holding our edited data. Following that we simply do the update and
then rebind the DataList control.

Public Sub dtlCustomers_Update(sender As Object, e As
DataListCommandEventArgs)

Dim strCompanyName, strContactName, strContactTitle, strCustomerID
As String
Dim strAddress, strCity, strPostalCode, strCountry, strPhone,
strFax As String

strCompanyName = CType(e.Item.FindControl("txtCompanyName"),
TextBox).Text
strContactName = CType(e.Item.FindControl("txtContactName"),
TextBox).Text
strContactTitle = CType(e.Item.FindControl("txtContactTitle"),
TextBox).Text
strAddress = CType(e.Item.FindControl("txtAddress"), TextBox).Text
strCity = CType(e.Item.FindControl("txtCity"), TextBox).Text
strPostalCode = CType(e.Item.FindControl("txtPostalCode"),TextBox).Text
strCountry = CType(e.Item.FindControl("txtCountry"),TextBox).Text
strPhone = CType(e.Item.FindControl("txtPhone"),TextBox).Text
strFax = CType(e.Item.FindControl("txtFax"),TextBox).Text
strCustomerID = CType(e.Item.FindControl("lblCustomerID"),
Label).Text

Dim strSQL As String
strSQL = "Update Customers " _
& "Set CompanyName = @CompanyName," _
& "ContactName = @ContactName," _
& "ContactTitle = @ContactTitle, " _
& "Address = @Address, " _
& "City = @City, " _
& "PostalCode = @PostalCode, " _
& "Country = @Country, " _
& "Phone = @Phone, " _
& "Fax = @Fax " _
& "WHERE CustomerID = @CustomerID"

Dim objConn As New SqlConnection(strConn)
Dim cmdSQL As New SqlCommand(strSQL, objConn)
cmdSQL.Parameters.Add(new SqlParameter("@CompanyName",
SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@CompanyName").Value = strCompanyName
cmdSQL.Parameters.Add(new SqlParameter("@ContactName",
SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@ContactName").Value = strContactName
cmdSQL.Parameters.Add(new SqlParameter("@ContactTitle",
SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@ContactTitle").Value = strContactTitle
cmdSQL.Parameters.Add(new SqlParameter("@Address",
SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@Address").Value = strAddress
cmdSQL.Parameters.Add(new SqlParameter("@City",
SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@City").Value = strCity
cmdSQL.Parameters.Add(new SqlParameter("@PostalCode",
SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@PostalCode").Value = strPostalCode
cmdSQL.Parameters.Add(new SqlParameter("@Country",
SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@Country").Value = strCountry
cmdSQL.Parameters.Add(new SqlParameter("@Phone",
SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@Phone").Value = strPhone
cmdSQL.Parameters.Add(new SqlParameter("@Fax", SqlDbType.NVarChar,
24))
cmdSQL.Parameters("@Fax").Value = strFax
cmdSQL.Parameters.Add(new SqlParameter("@CustomerID",
SqlDbType.NChar, 5))
cmdSQL.Parameters("@CustomerID").Value = strCustomerID

objConn.Open()
cmdSQL.ExecuteNonQuery()
objConn.Close()

dtlCustomers.EditItemIndex = -1
BindTheData()

End Sub

End Class






Conclusion: You have seen a lot coding necessary to presenting a
DataList, placing it in edit mode, and then canceling or updating the
data after making changes. If you take the code one section at a time
and see what each section actually does, I believe you will find that
you can finely tune how your data is presented both for viewing and
for editing, and how to accomplish the update. Best of luck!