Use the DataList Control to Present and Edit Data PART 2

Discussion in 'ASP .Net Web Controls' started by Alex, Dec 7, 2003.

  1. Alex

    Alex Guest

    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!
     
    Alex, Dec 7, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nevyn Twyll
    Replies:
    8
    Views:
    1,889
  2. Ralf Wahner
    Replies:
    5
    Views:
    636
    Bob Foster
    Dec 24, 2003
  3. Alex

    Use the DataList Control to Present and Edit Data

    Alex, Dec 7, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    174
  4. Alex

    Use the DataList Control to Present and Edit Data PART 2

    Alex, Dec 7, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    110
  5. Alex

    Use the DataList Control to Present and Edit Data

    Alex, Dec 7, 2003, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    182
Loading...

Share This Page