簡單的新增刪除修改查詢的程式

Public Class SampleForm2
Enum uEditMode '列舉值
Insert = 0
Edit = 1
View = 2
Delete = 3
End Enum

Dim cn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\db1.mdb;Persist Security Info=False")
Dim da As New OleDb.OleDbDataAdapter
Dim cb As New OleDb.OleDbCommandBuilder
Dim dt As New Data.DataTable("Demo")
Dim lngEditMode As uEditMode
Dim strCaption() As String
Dim WithEvents myBindingManagerBase As BindingManagerBase

Private Sub BindingManagerBase_PositionChanged(ByVal sender As Object, ByVal e As EventArgs) Handles myBindingManagerBase.PositionChanged
RcdToScr()
End Sub

'Private Sub MoveNext()
' myBindingManagerBase.Position += 1
'End Sub

'Private Sub MovePrevious()
' myBindingManagerBase.Position -= 1
'End Sub

'Private Sub MoveFirst()
' myBindingManagerBase.Position = 0
'End Sub

'Private Sub MoveLast()
' myBindingManagerBase.Position = myBindingManagerBase.Count - 1
'End Sub


Private Sub SampleForm2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
InitComm()
DataBind()
End Sub

Private Sub SetCaption()
Try
strCaption = Split("員工編號,員工姓名,到職日期,地址,備註", ",")
lblEmpNo.Text = strCaption(0) '設定Label 的Caption
lblEmpName.Text = strCaption(1)
lblEntryDate.Text = strCaption(2)
lblAddress.Text = strCaption(3)
lblNote.Text = strCaption(4)

lblFindEmpNo.Text = strCaption(0) '設定Label 的Caption
lblFindEmpName.Text = strCaption(1)
lblFindEntryDate.Text = strCaption(2)
lblFindAddress.Text = strCaption(3)
lblFindNote.Text = strCaption(4)

For i As Int32 = 0 To UBound(strCaption) '設定DataGrid 的Caption
dgdData.Columns(i + 1).HeaderText = strCaption(i)
Next
Catch ex As Exception
ErrHandle(ex)
End Try
End Sub

'加入錯誤處理
Private Sub ErrHandle(ByVal ex As Exception)
MsgBox("[程式錯誤]-錯誤原因:" & ex.Message, vbCritical, "提示")
End Sub

Private Sub InitComm()
ChangeMode(uEditMode.View)
End Sub

'清空資料
Private Sub NewRcd()
Try
txtEmpNo.Clear()
txtEmpName.Clear()
txtEntryDate.Clear()
txtAddress.Clear()
txtNote.Clear()
txtEmpNo.Focus()
Catch ex As Exception
ErrHandle(ex)
End Try
End Sub

Private Sub RcdToScr()
Try
With dt
If myBindingManagerBase.Position < 0 Then Exit Try txtEmpNo.Text = .Rows(myBindingManagerBase.Position).Item("EmpNo").ToString txtEmpName.Text = .Rows(myBindingManagerBase.Position).Item("EmpName").ToString txtEntryDate.Text = .Rows(myBindingManagerBase.Position).Item("EntryDate").ToString txtAddress.Text = .Rows(myBindingManagerBase.Position).Item("Address").ToString txtNote.Text = .Rows(myBindingManagerBase.Position).Item("Note").ToString End With Catch ex As Exception ErrHandle(ex) End Try End Sub Private Sub ScrToRcd() Try Dim objRow As DataRow = Nothing If lngEditMode = uEditMode.Insert Then objRow = dt.NewRow '如果為新增模式則新增一筆資料 Else objRow = dt.Rows(myBindingManagerBase.Position) End If With objRow .Item("EmpNo") = GetValue(txtEmpNo.Text) .Item("EmpName") = GetValue(txtEmpName.Text) .Item("EntryDate") = GetValue(txtEntryDate.Text) .Item("Address") = GetValue(txtAddress.Text) .Item("Note") = GetValue(txtNote.Text) End With dt.Rows.Add(objRow) da.Update(dt) Catch ex As Exception ErrHandle(ex) End Try End Sub Private Function GetValue(ByVal Value As Object) As System.Object Dim tmpValue As Object = Nothing If Value.ToString.Length = 0 Then tmpValue = Convert.DBNull Else tmpValue = Value End If Return tmpValue End Function Private Sub DataBind() '繫結資料 Try Dim strWhere As String = "" OpenData() cb = New Data.OleDb.OleDbCommandBuilder(da) cb.QuotePrefix = "[" cb.QuoteSuffix = "]" dt.Clear() da.Fill(dt) myBindingManagerBase = Me.BindingContext(dt) Me.dgdData.DataSource = dt Me.dgdData.Refresh() If dt.Columns.Count > 0 Then dt.Columns("KeyNo").ReadOnly = True
If dgdData.Columns.Count > 0 Then dgdData.Columns("KeyNo").Visible = False
SetCaption()

Catch ex As Exception
ErrHandle(ex)
End Try

BindingManagerBase_PositionChanged(Me, System.EventArgs.Empty)

End Sub

Private Sub OpenData()
da = New Data.OleDb.OleDbDataAdapter
Dim cmd As New OleDb.OleDbCommand

Dim strWhere As String = ""
If Me.txtFindEmpNo1.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.Decimal).Value = txtFindEmpNo1.Text
strWhere &= " And [EmpNo] >= ?"
End If
If Me.txtFindEmpNo2.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.Decimal).Value = txtFindEmpNo2.Text
strWhere &= " And [EmpNo] <= ?" End If If Me.txtFindEmpName1.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.BSTR, 10).Value = txtFindEmpName1.Text
strWhere &= " And [EmpName] >= ?"
End If
If Me.txtFindEmpName2.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.BSTR, 10).Value = txtFindEmpName2.Text
strWhere &= " And [EmpName] <= ?" End If If Me.txtFindEntryDate1.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.Date).Value = txtFindEntryDate1.Text
strWhere &= " And [EntryDate] >= ?"
End If
If Me.txtFindEntryDate2.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.Date).Value = txtFindEntryDate2.Text
strWhere &= " And [EntryDate] <= ?" End If If Me.txtFindNote.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.BSTR, 10).Value = txtFindNote.Text
strWhere &= " And [Note] like '%' + ? + '%'"
End If
If Me.txtFindAddress.Text.Length > 0 Then
cmd.Parameters.Add("EmpNo1", OleDb.OleDbType.BSTR, 10).Value = txtFindAddress.Text
strWhere &= " And [Address] like '%' + ? + '%'"
End If

cmd.CommandText = "Select [KeyNo],[EmpNo],[EmpName],[EntryDate],[Address],[Note] From [Demo] "
If strWhere.Length > 0 Then cmd.CommandText &= " Where " & strWhere.Substring(4)
cmd.Connection = cn
da.SelectCommand = cmd
End Sub

Private Sub dtnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
If Not IsDataOk() Then Exit Sub
ScrToRcd()
If lngEditMode = uEditMode.Insert Then
btnAdd_Click(Me, System.EventArgs.Empty)
Else
ChangeMode(uEditMode.View)
End If
End Sub

Private Function IsDataOk() As Boolean '撰寫檢查的條件
Try

Catch ex As Exception

End Try
Return True
End Function

Private Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExit.Click
If lngEditMode = uEditMode.View Then
Me.Dispose()
Else
DataBind()
End If
End Sub

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Dispose()
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Call ChangeMode(uEditMode.Insert)
NewRcd()

End Sub

Private Sub ChangeMode(ByVal lngMode As uEditMode)
Try
Dim blnFlag As Boolean
lngEditMode = lngMode
Select Case lngMode
Case uEditMode.Insert '新增
blnFlag = True
btnExit.Text = "取消(&X)"
Case uEditMode.Edit '修改
blnFlag = True
btnExit.Text = "取消(&X)"
Case uEditMode.View '顯示
blnFlag = False
btnExit.Text = "結束(&X)"
End Select
gbxData.Enabled = blnFlag
dgdData.Enabled = Not blnFlag
btnAdd.Enabled = Not blnFlag
btnEdit.Enabled = Not blnFlag
btnSave.Enabled = blnFlag
btnDelete.Enabled = Not blnFlag
btnPrint.Enabled = Not blnFlag

Catch ex As Exception
ErrHandle(ex)

End Try
End Sub

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
Call ChangeMode(uEditMode.Edit)

End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If MessageBox.Show("確定要刪除??", "提示!!", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
dt.Rows(myBindingManagerBase.Position).Delete()
da.Update(dt)
BindingManagerBase_PositionChanged(Me, System.EventArgs.Empty)
End If
End Sub

Private Sub lblEmpNo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblEmpNo.Click

End Sub

Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
DataBind()
End Sub
End Class