Option Explicit

Private Sub cmdmodify_Click()       '修改記錄按鈕
Dim sqlstr As String
Dim rst1 As New ADODB.Recordset
Dim param As ADODB.Parameter
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set param = New ADODB.Parameter

sqlstr = "select * from book where [圖書編碼]= '" & Trim(Text1.Text) & "'"
  With param
    .Direction = adParamInput
    .Type = adBSTR
    .Size = 8
    .Value = Text1.Text
    End With
   cmd.Parameters.Append param
   cmd.CommandText = sqlstr
   cmd.CommandType = adCmdText
   Set cmd.ActiveConnection = con
   Set rst1 = cmd.Execute
   
  If rst1.RecordCount > 0 Then
     frmMsg.Show
       frmMsg.notice.Visible = True
    frmMsg.Text1.Text = "此書號已經存在!"
       rst.Cancel
       Exit Sub
   End If
    rst.Update
   frmMsg.Show
   frmMsg.info.Visible = True
  frmMsg.Text1.Text = "修改成功!"
End Sub

Private Sub Command1_Click()
Dim sql As String
Dim param As ADODB.Parameter
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set param = New ADODB.Parameter
sql = "select * from book where [圖書編碼]= ? "

If Command1.Caption = "添加記錄" Then
   Command1.Caption = "確   定"
   Command2.Enabled = False          '屏蔽刪除、修改、下一記錄、上一記錄按鈕，避免出現數據庫錯誤
   Command3.Enabled = False
   Command4.Enabled = False
   cmdmodify.Enabled = False
   rst.AddNew
Else
     If Text1.Text = "" Then
    frmMsg.Show
    frmMsg.notice.Visible = True
  frmMsg.Text1.Text = "圖書編號不能為空!"
       Command2.Enabled = True
       Command3.Enabled = True
       Command4.Enabled = True
       Command1.Caption = "添加記錄"
      Exit Sub
     End If
    With param
    .Direction = adParamInput
    .Type = adBSTR
    .Size = 8
    .Value = Text1.Text
    End With
   cmd.Parameters.Append param
   cmd.CommandText = sql
   cmd.CommandType = adCmdText
   Set cmd.ActiveConnection = con
   Set rst1 = cmd.Execute
   
  If rst1.RecordCount > 0 Then
      frmMsg.Show
      frmMsg.notice.Visible = True
  frmMsg.Text1.Text = "此書號已經存在!"
       rst.Cancel
     Command2.Enabled = True
     Command3.Enabled = True
     Command4.Enabled = True
      cmdmodify.Enabled = True
     Command1.Caption = "添加記錄"
     Command2.Enabled = True
       Exit Sub
   End If

   rst.Update
   Command2.Enabled = True                  '數據庫更新結束后才可以點擊其他幾個按鈕
   Command3.Enabled = True
   Command4.Enabled = True
    cmdmodify.Enabled = True
   Command1.Caption = "添加記錄"
 
End If
End Sub

Private Sub Command2_Click()                  '刪除記錄
Dim ans As String
ans = Msgbox("確定要刪除此記錄嗎？", vbYesNo, "提示")
If ans = vbYes Then
   rst.Delete
   rst.Requery
Else
 Exit Sub
End If
End Sub

Private Sub Command3_Click()                  '上一記錄
If rst.BOF Then
   frmMsg.Show
   frmMsg.info.Visible = True
  frmMsg.Text1.Text = "已經到達記錄頂端!"
   Exit Sub
 Else
   rst.MovePrevious
End If
 
End Sub

Private Sub Command4_Click()              '下一記錄
If rst.EOF Then
  frmMsg.Show
  frmMsg.info.Visible = True
  frmMsg.Text1.Text = "已經到達記錄底端! "
   Exit Sub
 Else
   rst.MoveNext
End If
End Sub

Private Sub Command5_Click()
Unload Me
End Sub

Private Sub Form_Load()

Dim sql1 As String
Set rst = New ADODB.Recordset
sql1 = "select * from book"

Call connect_db
rst.Open sql1, con, adOpenDynamic, adLockOptimistic
Set Text1.DataSource = rst
Set Text2.DataSource = rst
Set Text3.DataSource = rst
Set Text4.DataSource = rst
Set Text5.DataSource = rst

Text1.DataField = "圖書編碼"
Text2.DataField = "圖書名稱"
Text3.DataField = "作者"
Text4.DataField = "出版社"
Text5.DataField = "入庫時間"

End Sub

Private Sub Image1_Click()

End Sub

Private Sub xptopbuttons1_Click()
Unload Me
End Sub

Private Sub xptopbuttons2_Click()
Me.WindowState = 1
End Sub