VB.Net - Insert, Update, Delete, Search Image In SQL

How To Insert Update Delete Search Display Images In SQL Database Using VB.Net

vb.net insert update delete search display image in database


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.


                     =>   Part 2

                      =>  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:


vb.net insert update delete search display image from sql server database




Share this

Related Posts

Previous
Next Post »