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_RowInsertUpdateDelete
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        'Apply server-side events
        AddHandler myDataGrid.InsertCommand, AddressOf myDataGrid_InsertCommand
        AddHandler myDataGrid.UpdateCommand, AddressOf myDataGrid_UpdateCommand
        AddHandler myDataGrid.DeleteCommand, AddressOf myDataGrid_DeleteCommand

        'Define SQL query
        Dim SqlQuery As String = "SELECT CustomerID, CompanyName, ContactName, Address, PostalCode " & _
            "FROM Customers ORDER BY CustomerID"

        'Get DataTable (MS Access Database)
        Dim myDataSource As DataTable = DataBase.GetDataTableOleDb(SqlQuery, "~/DataGrid/DataBases/Nwind.mdb")

        'Add column for Update/Delete links
        Dim UpdateDelete As New DataColumn()
        UpdateDelete.ColumnName = "UpdateDelete"
        UpdateDelete.DataType = System.Type.GetType("System.String")
        UpdateDelete.DefaultValue = ""
        myDataSource.Columns.Add(UpdateDelete)

        'Set the data source
        myDataGrid.KeyFieldName = "CustomerID"
        myDataGrid.DataSource = myDataSource
        myDataGrid.DataBind()

        'Set properties for the UpdateDelete column
        myDataGrid.Columns("UpdateDelete").HeaderText = ""
        myDataGrid.Columns("UpdateDelete").Template = "~/DataGrid/Templates/UpdateDelete.html"
        myDataGrid.Columns("UpdateDelete").Sortable = False
        myDataGrid.Columns("UpdateDelete").Width = 83

    End Sub


    'Server-side procedure for Insert
    Private Sub myDataGrid_InsertCommand(ByVal sender As Object, ByVal e As DataGridEventArgs)
        'Declare database 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 = "INSERT INTO Customers " & _
            "(CustomerID, CompanyName, ContactName, Address) " & _
            "VALUES " & _
            "(@CustomerID, @CompanyName, @ContactName, @Address)"

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

        'Add parameters
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = e.EditFormFields("txtCustomerID").ToString()
        cmd.Parameters.Add("@CompanyName", OleDbType.VarChar).Value = e.EditFormFields("txtCompanyName").ToString()
        cmd.Parameters.Add("@ContactName", OleDbType.VarChar).Value = e.EditFormFields("txtContactName").ToString()
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = e.EditFormFields("txtAddress").ToString()

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

        'Should be specified to select created record
        e.GridRowKeyValue = e.EditFormFields("txtCustomerID").ToString()
    End Sub



    'Server-side procedure for Update
    Private Sub myDataGrid_UpdateCommand(ByVal sender As Object, ByVal e As DataGridEventArgs)
        'Get the row
        Dim myGridRow As GridRow = e.GridRow
        If myGridRow Is Nothing Then
            Return
        End If

        '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 Customers SET " & _
            "CompanyName=@CompanyName, ContactName=@ContactName, Address=@Address " & _
            "WHERE CustomerID=@CustomerID"

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

        'Add parameters
        cmd.Parameters.Add("@CompanyName", OleDbType.VarChar).Value = e.EditFormFields("txtCompanyName").ToString()
        cmd.Parameters.Add("@ContactName", OleDbType.VarChar).Value = e.EditFormFields("txtContactName").ToString()
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = e.EditFormFields("txtAddress").ToString()
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = e.EditFormFields("txtCustomerID").ToString()

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



    'Server-side procedure for Delete
    Private Sub myDataGrid_DeleteCommand(ByVal sender As Object, ByVal e As DataGridEventArgs)
        'Get the row
        Dim myGridRow As GridRow = e.GridRow
        If myGridRow Is Nothing Then
            Return
        End If

        'Declare database 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 = "DELETE * FROM Customers WHERE CustomerID = @CustomerID"

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

        'Add parameters
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = myGridRow.Cells("CustomerID").Value

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