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
|