How To Insert Update Delete Search Display Images In SQL Database Using VB.Net
In This VB.NET Tutorial We Will See How To :
- Add Image Into SQL Server Database
- Edit Selected Image From SQL Server Database
- Delete Selected Image From SQL Server Database
- Search Image By ID From SQL Server Database
- Show Image From SQL Server Database Into Datagridview
Using Visual Basic .NET Programming Language.
- Add Image Into SQL Server Database
- Edit Selected Image From SQL Server Database
- Delete Selected Image From SQL Server Database
- Search Image By ID From SQL Server Database
- Show Image From SQL Server Database Into Datagridview
Using Visual Basic .NET Programming Language.
=> Part 3
=> Part 4
=> Part 5
=> Part 6
=> Part 7
Project Source Code:
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging
Public Class VbNet_Insert_Update_Delete_Search_Display_Images_From_SQL
Dim connection As New SqlConnection("Server= SAMSNG-PC; Database = TestDB; Integrated Security = true")
Private Sub VbNet_Insert_Update_Delete_Search_Display_Images_From_SQL_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim command As New SqlCommand("select * from table_images", connection)
Dim adapter As New SqlDataAdapter(command)
Dim table As New DataTable()
adapter.Fill(table)
DataGridView1.AllowUserToAddRows = False
DataGridView1.RowTemplate.Height = 100
Dim imgc As New DataGridViewImageColumn
DataGridView1.DataSource = table
imgc = DataGridView1.Columns(3)
imgc.ImageLayout = DataGridViewImageCellLayout.Stretch
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim opf As New OpenFileDialog
opf.Filter = "Choose Image(*.JPG;*.PNG;*.GIF)|*.jpg;*.png;*.gif"
If opf.ShowDialog = Windows.Forms.DialogResult.OK Then
PictureBox1.Image = Image.FromFile(opf.FileName)
End If
Dim ms As New MemoryStream
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
End Sub
Private Sub DataGridView1_Click(sender As Object, e As EventArgs) Handles DataGridView1.Click
Dim img As Byte()
img = DataGridView1.CurrentRow.Cells(3).Value
Dim ms As New MemoryStream(img)
PictureBox1.Image = Image.FromStream(ms)
TextBoxID.Text = DataGridView1.CurrentRow.Cells(0).Value
TextBoxName.Text = DataGridView1.CurrentRow.Cells(1).Value
TextBoxDesc.Text = DataGridView1.CurrentRow.Cells(2).Value
End Sub
Public Sub ExecuteMyQuery(MyCommand As SqlCommand, MyMessage As String)
connection.Open()
If MyCommand.ExecuteNonQuery = 1 Then
MessageBox.Show(MyMessage)
Else
MessageBox.Show("Query Not Executed")
End If
connection.Close()
populateDatagridview("")
End Sub
Private Sub BTN_INSERT_Click(sender As Object, e As EventArgs) Handles BTN_INSERT.Click
Dim ms As New MemoryStream
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
Dim img() As Byte
img = ms.ToArray()
Dim insertQuery As String = "INSERT INTO Table_Images(name,description,the_image) VALUES('" & TextBoxName.Text & "','" & TextBoxDesc.Text & "', @img )"
Dim command As New SqlCommand(insertQuery, connection)
command.Parameters.Add("@img", SqlDbType.Image).Value = img
ExecuteMyQuery(command, " IMage Inserted ")
End Sub
Private Sub BTN_UPDATE_Click(sender As Object, e As EventArgs) Handles BTN_UPDATE.Click
Dim ms As New MemoryStream
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
Dim img() As Byte
img = ms.ToArray()
Dim updateQuery As String = "UPDATE Table_Images SET name = '" & TextBoxName.Text & "',description = '" & TextBoxDesc.Text & "',the_image = @img WHERE id = " & TextBoxID.Text
Dim command As New SqlCommand(updateQuery, connection)
command.Parameters.Add("@img", SqlDbType.Image).Value = img
ExecuteMyQuery(command, " IMage Updated ")
End Sub
Private Sub BTN_DELETE_Click(sender As Object, e As EventArgs) Handles BTN_DELETE.Click
Dim deleteQuery As String = "DELETE FROM Table_Images WHERE id = " & TextBoxID.Text
Dim command As New SqlCommand(deleteQuery, connection)
ExecuteMyQuery(command, " IMage Deleted ")
End Sub
Public Sub populateDatagridview(valueToSearch As String)
Dim searchQuery As String = "SELECT * From Table_Images WHERE CONCAT(name,description) like '%" & valueToSearch & "%'"
Dim command As New SqlCommand(searchQuery, connection)
Dim adapter As New SqlDataAdapter(command)
Dim table As New DataTable()
adapter.Fill(table)
DataGridView1.AllowUserToAddRows = False
DataGridView1.RowTemplate.Height = 100
Dim imgc As New DataGridViewImageColumn
DataGridView1.DataSource = table
imgc = DataGridView1.Columns(3)
imgc.ImageLayout = DataGridViewImageCellLayout.Stretch
End Sub
Private Sub TextBoxSearch_TextChanged(sender As Object, e As EventArgs) Handles TextBoxSearch.TextChanged
populateDatagridview(TextBoxSearch.Text)
End Sub
Private Sub BTN_FIND_Click(sender As Object, e As EventArgs) Handles BTN_FIND.Click
Dim command As New SqlCommand("select * from table_images WHERE id = @id ", connection)
command.Parameters.Add("@id", SqlDbType.Int).Value = TextBoxID.Text
Dim adapter As New SqlDataAdapter(command)
Dim table As New DataTable()
adapter.Fill(table)
If table.Rows.Count() <= 0 Then
MessageBox.Show("No Data Found")
Else
TextBoxID.Text = table.Rows(0)(0).ToString()
TextBoxName.Text = table.Rows(0)(1).ToString()
TextBoxDesc.Text = table.Rows(0)(2).ToString()
Dim img() As Byte
img = table.Rows(0)(3)
Dim ms As New MemoryStream(img)
PictureBox1.Image = Image.FromStream(ms)
End If
End Sub
End Class
///////////////OUTPUT:
if you want the source code click on the download button below
disclaimer: you will get the source code without the database script.
Download Projects Source Code