Public Class FormBooks
Dim Conn As SqlConnection = New System.Data.SqlClient.SqlConnection()
Dim ds As DataSet = New DataSet()
Dim cmd As SqlCommand = New SqlCommand()
Dim rowCount As Integer = 0
Dim positionn As Integer = 0
Private Sub FormBooks_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim strConn As String = "Data Source=PRIKII;" & "Initial Catalog=Books;Integrated Security=True"
With Conn
If .State = ConnectionState.Open Then
.Close()
.ConnectionString = strConn
.Open()
End If
End With
ReadData()
FillListBox()
Bindings() OpenFileDialog1.FileName = ""
End Sub
Private Sub ReadData()
Dim sql As String = "SELECT * FROM Books"
cmd = New SqlCommand(sql, Conn)
Dim adapter As New SqlDataAdapter(sql, Conn)
Dim ds As New DataSet()
adapter.Fill(ds, "book")
rowCount = ds.Tables("book").Rows.Count
End Sub
Private Sub Bindings()
TbxID.DataBindings.Add("Text", ds, "cust.CustomerID")
TbxName.DataBindings.Add("Text", ds, "cust.CustomerName")
Tbxprice.DataBindings.Add("Text", ds, "cust.Address")
Me.BindingContext(ds, "book").Position = 0
End Sub
Private Sub FillListBox()
Dim bookname As String = ""
ListBox1.Items.Clear()
For i = 0 To ds.Tables("book").Rows.Count - 1
bookname = ds.Tables("book").Rows(i)("BookName")
ListBox1.Items.Add(bookname)
Next
If (ListBox1.Items.Count > 0) Then
ListBox1.SelectedIndex = 0
End If
End Sub
Private Sub ShowPicture(ByRef index As Integer)
Dim imgByte() As Byte = ds.Tables("book").Rows(index)("Picture")
Dim imgstream As New MemoryStream(imgByte)
If (imgstream.Length > 0) Then
PictureBox1.Image = Image.FromStream(imgstream)
Else
PictureBox1.Image = Nothing
End If
End Sub
Private Sub FormCustomers_FormClosed(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.FormClosed
Conn.Close()
End Sub
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles ListBox1.SelectedIndexChanged
Dim index As Integer = ListBox1.SelectedIndex
Me.BindingContext(ds, "book").Position = index
positionn = index
If (TabControl1.SelectedIndex = 0) Then
ShowPicture(index)
End If
UpdateStatus()
End Sub
Private Sub UpdateStatus()
ToolStripStatusLabel1.Text = (positionn + 1) & " of " & rowCount
End Sub
Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles TabControl1.SelectedIndexChanged
If (TabControl1.SelectedIndex = 0) Then
ReadData()
If (rowCount = 0) Then
Return
End If
Bindings()
BtnDelete.Enabled = True
ListBox1.Enabled = True
ListBox1.SetSelected(0, True)
ElseIf (TabControl1.SelectedIndex = 1) Then
ClearBindings()
BtnDelete.Enabled = False
ListBox1.Enabled = False
End If
End Sub
Private Sub ClearBindings()
For Each c As Object In GroupBox1.Controls
If (TypeOf c Is TextBox) Then
c.Text = ""
c.DataBindings.Clear()
End If
Next
PictureBox1.Image = Nothing
End Sub
Private Sub LnkPic_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) _
Handles LnkPic.LinkClicked
With OpenFileDialog1
.Filter = "Image File (*.gif;*.jpg)|*.gif;*.jpg"
.FileName = ""
If (.ShowDialog() = DialogResult.OK) Then
PictureBox1.Image = Image.FromFile(.FileName)
End If
End With
End Sub
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles BtnSave.Click
If (TbxName.Text = "") Then
MsgBox("กรุณาใส่ชื่อสินค้า !")
Return
End If
If Not (IsNumeric(Tbxprice.Text)) Then
MsgBox("กรุณาใส่เป็นตัวเลข")
Return
End If
Dim sql As String = ""
If (TbxID.Text = "") Then
sql = "INSERT INTO Books(BookName, Price, "
sql &= "Picture) VALUES(@nm, @prc, @pic)"
Else
sql = "UPDATE Books SET "
sql &= "BookName = @nm, Price = @prc, "
If (OpenFileDialog1.FileName <> "") Then
sql &= ", Picture = @pic "
End If
sql &= "WHERE BookID = " & TbxID.Text
End If
cmd = New SqlCommand(sql, Conn)
cmd.Parameters.AddWithValue("nm", TbxName.Text)
cmd.Parameters.AddWithValue("prc", Tbxprice.Text)
Dim pic() As Byte = {}
If (OpenFileDialog1.FileName <> "") Then
Dim fiStream As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim binReader As New BinaryReader(fiStream)
pic = binReader.ReadBytes(fiStream.Length)
cmd.Parameters.AddWithValue("pic", pic)
Else
cmd.Parameters.AddWithValue("pic", pic)
End If
Dim affectedRow As Integer = cmd.ExecuteNonQuery()
If (affectedRow < 1) Then
ToolStripStatusLabel1.Text = "เกิดข้อผิดพลาดในการบันทึกข้อมูล"
Else
ToolStripStatusLabel1.Text = "ข้อมูลถูกจัดเก็บแล้ว"
Dim name As String = TbxName.Text
ReadData()
FillListBox()
ClearBindings()
If (TabControl1.SelectedIndex = 0) Then
Bindings()
End If
Dim idx As Integer = ListBox1.FindStringExact(name)
If (idx > -1) Then
ListBox1.SetSelected(idx, True)
End If
UpdateStatus()
End If
End Sub
Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
If (TbxID.Text = "") Then
Return
End If
If (MsgBox("ต้องการลบข้อมูล ?", MsgBoxStyle.OkCancel) = MsgBoxResult.Cancel) Then
Return
End If
Dim sql As String
sql = "DELETE FROM Books WHERE BookUD = " & TbxID.Text
cmd = New SqlCommand(sql, Conn)
Dim r As Integer = cmd.ExecuteNonQuery()
If (r > 0) Then
ToolStripStatusLabel1.Text = "ข้อมูลถูกลบแล้ว"
End If
Dim idx0 As Integer = ListBox1.SelectedIndex
ClearBindings()
ReadData()
FillListBox()
Bindings()
If (idx0 > 0) Then
ListBox1.SetSelected(idx0 - 1, True)
End If
End Sub
End Class
ใช้ vb2008 กับ ms sql2008
ช่วยดูให้หน่อยค่ะว่าทำไมตัวที่เน้นสีแดงถึง error ขอบคุณมากค่ะ
Load balance : Server 01
