Imports System.Data.OleDb
Public Class WebForm1
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents txtType_ID As System.Web.UI.WebControls.TextBox
Protected WithEvents txtDetail As System.Web.UI.WebControls.TextBox
Protected WithEvents txtGR As System.Web.UI.WebControls.TextBox
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents cmdEdit As System.Web.UI.WebControls.Button
Protected WithEvents cmdAdd As System.Web.UI.WebControls.Button
Protected WithEvents DDL As System.Web.UI.WebControls.DropDownList
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents txtSearch As System.Web.UI.WebControls.TextBox
Protected WithEvents cmdFind As System.Web.UI.WebControls.Button
Protected WithEvents lblNotFound As System.Web.UI.WebControls.Label
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
With DDL
.Items.Add("รหัสประกันภัย")
.Items.Add("รายละเอียด")
.Items.Add("Group")
End With
End Sub
#End Region
Dim DT As DataView
Dim m_DataFound As Boolean = True
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
'Dim XPath as string = "c:\inetpub\wwwroot\RmWeb\RmServer\Insur_Db.mdb"
Dim XPath As String = Server.MapPath("RmServer\Insur_Db.mdb")
Dim sql As String = "Select TypeInsur_ID, Detail_Insur, Gr From TypeInsur"
Dim c1 As New MISAccess(XPath)
c1.Password = "WANGNOI2520"
DT = c1.GetDataTable(sql).DefaultView
Session.Add("data", DT)
Me.MyGridBind()
Else
DT = Session("data")
End If
End Sub
Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
End Sub
Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
End Sub
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
DataGrid1.CurrentPageIndex = e.NewPageIndex
Me.MyGridBind()
End Sub
Public Sub MyGridBind()
DataGrid1.DataSource = DT
DataGrid1.DataBind()
End Sub
Property DataFound() As Boolean
Get
Return m_DataFound
End Get
Set(ByVal Value As Boolean)
m_DataFound = Value
If m_DataFound = True Then
DataGrid1.Visible = True
lblNotFound.Visible = False
Else
DataGrid1.Visible = False
lblNotFound.Visible = True
lblNotFound.Text = "ไม่พบรายการที่ค้นหา ในฐานข้อมูล"
End If
End Set
End Property
Private Sub cmdFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFind.Click
Dim X As String = txtSearch.Text 'กำจัดการใส่ เครื่องหมาย ' และ ""
X = Replace(X, "'", "")
X = Replace(X, """", "")
txtSearch.Text = X
Select Case DDL.SelectedIndex
Case 0
DT.RowFilter = "TypeInsur_ID Like '" & txtSearch.Text & "%'"
Case 1
DT.RowFilter = "Detail_Insur Like '" & txtSearch.Text & "%'"
Case 2
DT.RowFilter = "GR Like '" & txtSearch.Text & "%'"
End Select
If DT.Count > 0 Then
Me.DataFound = True
DataGrid1.CurrentPageIndex = 0
DataGrid1.EditItemIndex = -1
Me.MyGridBind()
Else
Me.DataFound = False
End If
End Sub
Private Sub DataGrid1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGrid1.SelectedIndexChanged
End Sub
Private Sub DataGrid1_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemCreated
If e.Item.ItemType = ListItemType.Pager Then
Dim L1 As New Literal
L1.Text = "<div align=right style='color:brown'>จำนวนหน้าทั้งหมด : " & DataGrid1.PageCount & " หน้า</div>"
e.Item.Cells(0).Controls.Add(L1)
Dim L2 As New Literal
L2.Text = "หน้า : "
e.Item.Cells(0).Controls.AddAt(0, L2)
End If
If e.Item.ItemType = ListItemType.Footer Then
e.Item.Cells(0).Text = "<b>ประเภทประกันภัย</b>"
Dim i As Integer
For i = 3 To 1 Step -1
e.Item.Controls.RemoveAt(i)
Next
e.Item.Cells(0).ColumnSpan = 4
e.Item.Cells(0).HorizontalAlign = HorizontalAlign.Center
End If
If e.Item.ItemType = ListItemType.EditItem Then
e.Item.Cells(0).BackColor = Color.LightCyan
Dim i As Integer
For i = 1 To 3
Dim T1 As TextBox = CType(e.Item.Cells(i).Controls(0), TextBox)
T1.CssClass = "ts2"
Select Case i
Case 1
T1.Width = Unit.Pixel(30)
T1.ReadOnly = True
T1.BackColor = Color.LightGray
Case 2
T1.Width = Unit.Pixel(180)
T1.MaxLength = 50
Case 3
T1.Width = Unit.Pixel(60)
T1.MaxLength = 50
End Select
Next
End If
End Sub
Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.EditCommand
DataGrid1.EditItemIndex = e.Item.ItemIndex
Me.MyGridBind()
End Sub
Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.CancelCommand
lblNotFound.Visible = False
DataGrid1.EditItemIndex = -1
Me.MyGridBind()
End Sub
Function CheckEmpty(ByVal X1 As String) As Object
If X1.Trim = "" Then
Return DBNull.Value
Else
Return X1
End If
End Function
Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand
Dim drv As DataRowView
drv = DT.Item(e.Item.DataSetIndex)
Dim T1 As TextBox = e.Item.Cells(2).Controls(0)
If T1.Text.Trim = "" Then
lblNotFound.Text = "คุณจำเป็นต้องป้อนรายละเอียดประเภทประกัน"
lblNotFound.Visible = True
Exit Sub
Else
lblNotFound.Visible = False
End If
drv("Detail_Insur") = T1.Text
Dim T2 As TextBox = e.Item.Cells(3).Controls(0)
drv("Gr") = Me.CheckEmpty(T2.Text)
Me.UpdateMyData(drv)
DataGrid1.EditItemIndex = -1
Me.MyGridBind()
End Sub
Public Sub UpdateMyData(ByRef Drv As DataRowView)
Dim Strsql As String = "update TypeInsur set Detail_Insur ='" & Drv("Detail_Insur") & "' , Gr ='" & Drv("Gr") & "' where TypeInsur_ID = '" & Drv("TypeInsur_ID") & "' " ' "update TypeInsur set Detail_Insur = @P1 , Gr =@P2 where TypeInsur_ID = @P3 "
Dim c1 As New MISAccess(Server.MapPath("RmServer\Insur_Db.mdb"))
c1.Password = "WANGNOI2520"
Dim cmd As OleDb.OleDbCommand = c1.CreateCommand(Strsql)
c1.CreateParam(cmd, "TTT")
cmd.Parameters("@P1").Value = Drv("Detail_Insur")
cmd.Parameters("@P2").Value = Drv("Gr")
cmd.Parameters("@P3").Value = Drv("TypeInsur_ID")
Dim Y As Integer = c1.Execute(Strsql)
If Y = -1 Then
lblNotFound.Text = "ไม่สามารถ Update ลงฐานข้อมูลได้"
txtDetail.Text = Strsql
lblNotFound.Visible = True
Else
DT.Table.AcceptChanges()
End If
End Sub
End Class
อันนี้เป็น class ชื่อ MISAccess
Imports System.Data.OleDb
Public Class MISAccess
Public Strcon As String
Dim PV As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Dim m_DBPath As String = "c:\inetpub\wwwroot\RmWeb\RmServer\Insur_Db.mdb"
Dim m_Password As String = ""
Public Sub New()
If Dir(m_DBPath) = "" Then
Err.Raise(60001, , "Database File : " & m_DBPath & " not found")
Exit Sub
End If
Strcon = PV & "data source=" & m_DBPath
End Sub
Public Sub New(ByVal DBPath As String)
If Dir(DBPath) = "" Then
Err.Raise(60001, , "Database File : " & DBPath & " not found")
Exit Sub
End If
m_DBPath = DBPath
Strcon = PV & "data source=" & m_DBPath
End Sub
Public Property Password() As String
Get
Return m_Password
End Get
Set(ByVal Value As String)
m_Password = Value
Strcon = PV & "data source=" & m_DBPath & ";Jet OLEDB:Database Password=" & m_Password
End Set
End Property
Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String = "Dataset1", _
Optional ByVal TableName As String = "Table1") As DataSet
Dim DA As New OleDbDataAdapter(Strsql, Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS, TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function
Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName As String = "Table1") As DataTable
Dim DT As DataTable
Dim DS As DataSet = Me.GetDataset(Strsql, , TableName)
DT = DS.Tables(0)
Return DT
End Function
Public Function CreateCommand(ByVal Strsql As String) As OleDbCommand
Dim cmd As New OleDbCommand(Strsql)
Return cmd
End Function
Public Function Execute(ByVal Strsql As String) As Integer
Dim cmd As New OleDbCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function
Public Function Execute(ByRef Cmd As OleDbCommand) As Integer
Dim Cn As New OleDbConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X = Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function
Public Sub CreateParam(ByRef Cmd As OleDbCommand, ByVal StrType As String)
'T:Text, M:Memo, Y:Currency, D:Datetime, I:Integer, S:Single, B:Boolean
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j = UCase(Mid(StrType, i, 1))
Dim P1 As New OleDbParameter()
P1.ParameterName = "@P" & i
Select Case j
Case "T"
P1.OleDbType = OleDbType.VarChar
Case "M"
P1.OleDbType = OleDbType.LongVarChar
Case "Y"
P1.OleDbType = OleDbType.Currency
Case "D"
P1.OleDbType = OleDbType.Date
Case "I"
P1.OleDbType = OleDbType.Integer
Case "S"
P1.OleDbType = OleDbType.Decimal
Case "B"
P1.OleDbType = OleDbType.Boolean
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class
Server Error in '/RmWeb' Application.
--------------------------------------------------------------------------------
Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query
Load balance : Server 03
