資料庫 SQL+VB

Public Class Form190

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.IPodTableAdapter.Fill(Me.IPodsDataSet.iPod)
ShowRecord(0)
Button4.Enabled = False : Button5.Enabled = False
End Sub

Private Sub lstiPods_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstiPods.SelectedIndexChanged
ShowRecord(lstiPods.SelectedIndex)
End Sub

Sub ShowRecord(ByVal pos As Integer)
Dim objRow As DataRow
If pos >= 0 Then
' 取得DataRow物件的記錄
objRow = Me.IPodsDataSet.iPod.Rows(pos)
' 顯示記錄資料
txtModelNo.Text = objRow("ModelNo") : txtName.Text = objRow("Name")
txtPrice.Text = objRow("Price") : txtStorage.Text = objRow("Storage")
txtBatteryLife.Text = objRow("BatteryLife") : txtStockDate.Text = objRow("StockDate")

Dim path As String
path = "images\" & objRow("ModelNo") & ".jpg"
Dim bmp = New Bitmap(path)
ptbOutput.Image = bmp

Button2.Enabled = True : Button3.Enabled = True
End If
End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
txtModelNo.ReadOnly = False : Button2.Enabled = False
Button3.Enabled = False : Button4.Enabled = True
Button5.Enabled = True
txtModelNo.Text = "" : txtName.Text = ""
txtStorage.Text = "" : txtBatteryLife.Text = ""
txtPrice.Text = "" : txtStockDate.Text = ""
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim strSQL As String
If MsgBox("確定刪除記錄:" & txtModelNo.Text,MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
strSQL = "DELETE FROM iPod WHERE ModelNo ='"
strSQL &= txtModelNo.Text & "'"
ExecuteSQL(strSQL) ' 執行SQL
Me.IPodTableAdapter.GetData()
Me.IPodTableAdapter.Fill(Me.IPodsDataSet.iPod)
End If
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim strSQL As String
' 建立SQL敘述更新資料庫記錄
strSQL = "UPDATE iPod SET "
strSQL &= "Name='" & txtName.Text & "',"
strSQL &= "Storage='" & txtStorage.Text & "',"
strSQL &= "Price=" & txtPrice.Text & ","
strSQL &= "BatteryLife=" & txtBatteryLife.Text & ","
strSQL &= "StockDate=#" & txtStockDate.Text & "#"
strSQL &= " WHERE ModelNo='" & txtModelNo.Text & "'"
ExecuteSQL(strSQL) ' 執行SQL
Me.IPodTableAdapter.GetData()
Me.IPodTableAdapter.Fill(Me.IPodsDataSet.iPod)
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim strSQL As String
' 建立SQL敘述新增一筆資料表記錄
strSQL = "INSERT INTO iPod (ModelNo, Name" & _
",Storage, BatteryLife, Price, StockDate) "
strSQL &= "VALUES ('" & txtModelNo.Text & "','"
strSQL &= txtName.Text & "','"
strSQL &= txtStorage.Text & "',"
strSQL &= txtBatteryLife.Text & ","
strSQL &= txtPrice.Text & ","
strSQL &= "#" & txtStockDate.Text & "#)"
ExecuteSQL(strSQL) ' 執行SQL
txtModelNo.ReadOnly = True ' 重設狀態
Button4.Enabled = False : Button5.Enabled = False
Me.IPodTableAdapter.GetData()
Me.IPodTableAdapter.Fill(Me.IPodsDataSet.iPod)
End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
txtModelNo.ReadOnly = True ' 重設狀態
Button4.Enabled = False : Button5.Enabled = False
ShowRecord(lstiPods.SelectedIndex)
End Sub
End Class
模組1
Imports System.Data.OleDb
Module Module1
Function ExecuteSQL(ByVal strSQL As String) As Integer
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim intRowsAffected As Integer
Dim strDbCon As String
strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=iPods.mdb"
Try
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
objCmd = New OleDbCommand(strSQL, objCon)
' 執行SQL指令
intRowsAffected = objCmd.ExecuteNonQuery()
objCon.Close()
Catch ex As Exception
MsgBox(strSQL)
End Try
Return intRowsAffected
End Function
End Module