'2006/12/05 增加查詢功能
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
