'--------------------------------------------------------------------------------

'                      Using ADO 2.0 Datasources With Formatting
'                                        Created 5/13/08
'                                           Ron Kessler



'--------------------------------------------------------------------------------

'UPDATED 5/13/08


'This page shows how to format our grid columns as currency


Imports System.Data
Imports System.Data.SqlClient


Partial Class WithFormatting
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        SelectCustomer()

    End Sub

    Private Sub SelectCustomer()

        '---by using the the DataSource Class, we do not need Connection Objects, DataAdapter, or DataSet controls
        If Not IsPostBack Then
            Try
                Dim connString As String = ConfigurationManager.ConnectionStrings("customers-05").ConnectionString

                Dim dsCustomers As New SqlDataSource

                With dsCustomers
                    .ConnectionString = connString
                    .DataSourceMode = SqlDataSourceMode.DataReader
                    .SelectCommand = "Select * From Customers ORDER BY Name"
                End With

                With ddlNames
                    .DataSource = dsCustomers
                    .DataTextField = "Name"
                    .DataValueField = "CustID"
                    .AutoPostBack = True
                End With

                '---make it so!
                ddlNames.DataBind()

            Catch ex As Exception
                Response.Redirect("errorpage.aspx")
            Finally


            End Try
        End If

    End Sub

    Private Sub FillMyGrid()

        Dim connString As String = ConfigurationManager.ConnectionStrings("customers-05").ConnectionString
        Dim myDataSource As New SqlDataSource

        With myDataSource
            .ConnectionString = connString
            .DataSourceMode = SqlDataSourceMode.DataSet
            .SelectCommand = "SELECT * FROM Orders WHERE CustID = @CustID"
            .SelectParameters.Add("CustID", ddlNames.SelectedValue)
            .DataBind()
        End With



        With GridView1
            .AutoGenerateColumns = False
            .DataSource = myDataSource
            .AllowSorting = True
            .AllowPaging = True
            .Caption = "Customer Orders Summary"
            .CaptionAlign = TableCaptionAlign.Top
        End With

        Dim currencyFormat As String = "{0:n2}"    'no $


        '---add our fields here
        '---custID
        Dim Field As New BoundField
        Field.DataField = "CustID"                  'db field name
        Field.HeaderText = "Customer #"
        Dim Col As DataControlField = Field
        GridView1.Columns.Add(Col)

        '---DateOfSale
        Field = New BoundField
        Field.DataField = "DateofSale"
        Field.HeaderText = "Date"
        Field.DataFormatString = "{0:d}"      'short date format
        Col = Field
        GridView1.Columns.Add(Col)

        '---ProductName
        Field = New BoundField
        Field.DataField = "ProductName"
        Field.HeaderText = "Description"
        Col = Field
        GridView1.Columns.Add(Col)

        '---ProductID
        Field = New BoundField
        Field.DataField = "ProductID"
        Field.HeaderText = "Product #"
        Col = Field
        GridView1.Columns.Add(Col)

        '---Price
        Field = New BoundField
        Field.DataField = "Price"
        Field.HeaderText = "Price"
        Field.DataFormatString = currencyFormat
        Col = Field
        GridView1.Columns.Add(Col)

        '---Quantity
        Field = New BoundField
        Field.DataField = "Quantity"
        Field.HeaderText = "Quantity"
        Col = Field
        GridView1.Columns.Add(Col)

        '---Tax
        Field = New BoundField
        Field.DataField = "Tax"
        Field.HeaderText = "Tax"
        Field.DataFormatString = currencyFormat
        Col = Field
        GridView1.Columns.Add(Col)

        '---TotalSale
        Field = New BoundField
        Field.DataField = "TotalSale"
        Field.HeaderText = "Total Sale"
        Field.DataFormatString = currencyFormat
        Col = Field
        GridView1.Columns.Add(Col)


        '---make all column formats here
        For Each Col In GridView1.Columns
            Col.ItemStyle.HorizontalAlign = HorizontalAlign.Right
            Col.ItemStyle.ForeColor = Drawing.Color.Blue
            Col.ItemStyle.Font.Name = "Verdana"
        Next

        GridView1.DataBind()


    End Sub


    Protected Sub ddlNames_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlNames.SelectedIndexChanged
        FillMyGrid()
    End Sub

End Class