Visual Basic Dot Net Tutorials For Beginners: Lessons
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.IO
Imports System.Management
Public Class Form1
Dim ComputerName As String = My.Computer.Name
Dim ServerName As String = "Server = " & ComputerName & "\SQLSERVER2014; Database = TestDB; Integrated Security = True"
Public SQLConn As New SqlConnection With {.ConnectionString = ServerName}
Public SQLComm As New SqlCommand With {.CommandType = CommandType.Text, .Connection = SQLConn}
Private Sub Btn_SelectPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_SelectPicture.Click
Try
'First of all Assign a Picture Number to the picture being newly added
SQLComm.CommandText = "Select Count (PicNo) from Pictures"
SQLComm.ExecuteNonQuery()
Dim HowManyPicturesAlready As Integer = SQLComm.ExecuteScalar
If HowManyPicturesAlready <= 0 Then
Tbx_PicNo.Text = 1
Else
Tbx_PicNo.Text = HowManyPicturesAlready + 1
'Now Select Picture to Add
End If
Catch ex As Exception
End Try
Try
If OpenFileDialog1.ShowDialog <> Windows.Forms.DialogResult.Cancel Then
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
End If
Catch ex As Exception
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
If SQLConn.State = ConnectionState.Closed Then
SQLConn.Open()
'MsgBox("Server is Connected", MsgBoxStyle.Information)
End If
Catch ex As Exception
End Try
End Sub
Private Sub Btn_Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Save.Click
Try
Dim mstream As New System.IO.MemoryStream()
PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim arrImage() As Byte = mstream.GetBuffer()
mstream.Close()
SQLComm.CommandText = "Insert into Pictures (PicNo,Picture)values ('" & Tbx_PicNo.Text & "',@File)"
With SQLComm
.Parameters.Clear()
.Parameters.AddWithValue("@File", arrImage)
'.ExecuteNonQuery()
If SQLComm.ExecuteNonQuery.ToString Then
MsgBox("Picture No. " & Tbx_PicNo.Text & "has been saved successfully", MsgBoxStyle.Information)
End If
End With
Catch ex As Exception
End Try
End Sub
Private Sub Btn_Load_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Load.Click
Try
If Tbx_FindPicture.Text = "" Then
MsgBox("Please select Picture No. to Find its Picture", MsgBoxStyle.Information)
Tbx_FindPicture.Focus()
Else
'First of All Find Picture of related Pic Number"
'-----------------------------------------
Dim adapter As New SqlDataAdapter
Dim Data As New DataTable
adapter = New SqlDataAdapter("Select Picture from Pictures where PicNo = '" & Tbx_FindPicture.Text & "'", SQLConn)
Dim CommandBuild As New SqlCommandBuilder(adapter)
adapter.Fill(Data)
Dim Picbyte() As Byte = Data.Rows(0).Item("Picture")
Dim pic As New System.IO.MemoryStream(Picbyte)
PictureBox2.Image = Image.FromStream(pic)
PictureBox2.SizeMode = PictureBoxSizeMode.StretchImage
pic.Close()
'------------------------------------------
End If
Catch ex As Exception
End Try
End Sub
Private Sub Btn_Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Update.Click
Try
'If SQLConn.State = ConnectionState.Closed Then
'SQLConn.Open()
'End If
Dim mstream As New System.IO.MemoryStream()
PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim arrImage() As Byte = mstream.GetBuffer
'mstream.Close
SQLComm.CommandText = "Update Pictures Set Picture = @File where PicNo = '" & Tbx_PicNo.Text & "'"
With SQLComm
.Parameters.Clear()
.Parameters.AddWithValue("@File", arrImage)
.ExecuteNonQuery()
MsgBox("Product Picture Updated successfully", MsgBoxStyle.Information)
End With
Catch ex As Exception
End Try
End Sub
End Class