Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

Imports System.Data.OleDb
Imports APNSoft.WebControls


Partial Public Class DataGrid_ColumnsTemplateDropDowns
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        'Apply event-handling method
        AddHandler myDataGrid.CallTheServer, AddressOf myDataGrid_CallTheServer

        'Create new DataTable
        Dim SqlQuery As String = "SELECT ProductID, ProductName, CategoryID FROM Products ORDER BY ProductID"

        'Set the data source
        myDataGrid.KeyFieldName = "ProductID"
        myDataGrid.DataSource = DataBase.GetDataTableOleDb(SqlQuery, "~/DataGrid/DataBases/Nwind.mdb")
        myDataGrid.DataBind()

        myDataGrid.Columns("CategoryID").Template = "~/DataGrid/Templates/Categories.ascx"
    End Sub


    'Server-side procedure for Update
    Private Sub myDataGrid_CallTheServer(ByVal sender As Object, ByVal e As APNSoft.WebControls.DataGridEventArgs)

        'Get the Row
        Dim myGridRow As GridRow = e.GridRow
        If myGridRow Is Nothing Then
            Return
        End If

        'Get cell value
        Dim CellValue As Integer = Integer.Parse(e.CellValue)

        'Declare db objects
        Dim conn As New OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            Server.MapPath("~/DataGrid/DataBases/Nwind.mdb")

        'Create SQL Query
        Dim SQL As String = "UPDATE Products SET CategoryID=@CategoryID WHERE ProductID=@ProductID"

        'Create command
        Dim cmd As New OleDbCommand(SQL, conn)

        'Add parameters
        cmd.Parameters.Add("CategoryID", OleDbType.Integer).Value = CellValue
        cmd.Parameters.Add("@ProductID", OleDbType.VarChar).Value = myGridRow.RowID

        'Execute the query
        conn.Open()
        Try
            cmd.ExecuteNonQuery()
        Finally
            cmd.Dispose()
            conn.Close()
        End Try

    End Sub

End Class