none
Save image to SQL with VB.Net 2017 RRS feed

  • Question

  • I've seen many samples to accomplish saving an image to SQL, and I tried duplicating the codes and I get error messages.
    Monday, March 25, 2019 10:58 PM

Answers

  • Hello,

    The following code samples uses an image selected by a OpenFileDialog and inserts the selected image into a SQL-Server database table.

    Notes

    In the class DataOperations, the following part you will not have so comment it out unless you install the package mentioned in the comments and the link in my signature, they are responsible for creating a connection string to open the database, you should already have a connection to use any ways.

    ConnectionString is also from the NuGet package.

    The following method does the INSERT.

    Imports System.Data.SqlClient
    Imports BaseConnectionLibrary.ConnectionClasses
    ''' <summary>
    ''' Connection made with this NuGet package
    ''' https://www.nuget.org/packages/BaseConnectionLibrary/1.0.3
    ''' 
    ''' DatabaseServer and DefaultCatalog are part of the above package
    ''' </summary>
    Public Class DataOperations
        Inherits SqlServerConnection
    
        Public Function InsertImage(imageBytes As Byte(), description As String) As Boolean
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = " INSERT INTO dbo.ImageData (ImageData, [Description]) VALUES (@img, @description); "
    
                    cmd.Parameters.Add("@img", SqlDbType.Image).Value = imageBytes
    
                    cmd.Parameters.Add("@description", SqlDbType.Text).Value =
                        If(String.IsNullOrWhiteSpace(description), "None", description)
    
                    Try
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
        End Function
        Public Sub New()
            DatabaseServer = "KARENS-PC"
            DefaultCatalog = "NORTHWND_NEW.MDF"
        End Sub
    End Class

    Form code, requires one button, one OpenFileDialog.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            OpenFileDialog1.InitialDirectory = "C:\Dotnet\VS2017\Images"
            If OpenFileDialog1.ShowDialog = DialogResult.OK Then
                Dim fileBytes As Byte() = IO.File.ReadAllBytes(OpenFileDialog1.FileName)
                Dim ops As New DataOperations
                If ops.InsertImage(fileBytes, Now.ToShortTimeString) Then
                    MessageBox.Show("Image added")
                Else
                    MessageBox.Show("Failed to add image")
                End If
            End If
        End Sub
    End Class

    Here is the schema for the table used above.

    CREATE TABLE [dbo].[ImageData]( 
        [ImageID] [INT] IDENTITY(1,1) NOT NULL, 
        [ImageData] [IMAGE] NULL, 
        [Description] [NVARCHAR](MAX) NULL, 
     CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED  
    ( 
        [ImageID] ASC 
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     
    If you run into issues come back with specific details.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Tuesday, March 26, 2019 12:53 AM
    Moderator
  • Hi,

    try the code,insert and read images from the database

    Imports System.Data.SqlClient
    Imports System.IO
    
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim sda As SqlDataAdapter
        Dim ds As DataSet
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using conn = New SqlConnection(constr)
                conn.Open()
                cmd = New SqlCommand("insert into Image (Id,Image) values (1,@blobdata)", conn)
                Dim picturePath As String = "D:\test.jpg"
                Dim fs As FileStream = New FileStream(picturePath, FileMode.Open, FileAccess.Read)
                Dim mybyte As Byte() = New Byte(fs.Length - 1) {}
                fs.Read(mybyte, 0, mybyte.Length)
                fs.Close()
                Dim prm As SqlParameter = New SqlParameter("@blobdata", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, mybyte)
                cmd.Parameters.Add(prm)
                cmd.ExecuteNonQuery()
            End Using
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("select image from Image", conn)
                ds = New DataSet()
                sda.Fill(ds, "Image")
    
            End Using
    
            Dim mybyte As Byte() = New Byte(-1) {}
            mybyte = CType((ds.Tables("Image").Rows(0)("Image")), Byte())
            Dim ms As MemoryStream = New MemoryStream(mybyte)
                PictureBox1.Image = Image.FromStream(ms)
    
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MosheS Singer Tuesday, March 26, 2019 3:48 PM
    Tuesday, March 26, 2019 2:11 AM
  • I am still getting the same message

    System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'Where'.'

    Here is the sample code showing exactly how I used the code as you showed:

    Using SqlConct As New SqlConnection(SQLKT)
                            SqlConct.Open()
                            Dim stri As String = "insert into ListNames (photo) VALUES(@Photo) Where ID = @TID"
                            Dim cmdS As New SqlCommand(stri, SqlConct)
                            Dim fs As FileStream = New FileStream(strFileName, FileMode.Open, FileAccess.Read)
                            Dim mybyte As Byte() = New Byte(fs.Length - 1) {}
                            fs.Read(mybyte, 0, mybyte.Length)
                            fs.Close()
                            Dim prm As SqlParameter = New SqlParameter("@Photo", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, mybyte)
                            cmdS.Parameters.Add(prm)
                            cmdS.Parameters.AddWithValue("@TID", Me.TID.Text)
                            cmdS.ExecuteNonQuery()
                        End Using

    Forget what I said before, there is no WHERE clause for an INSERT. But there is a WHERE clause on a UPDATE statement. Sorry I'm bouncing between work and here.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by MosheS Singer Tuesday, March 26, 2019 6:05 PM
    Tuesday, March 26, 2019 6:01 PM
    Moderator

All replies

  • Hello,

    The following code samples uses an image selected by a OpenFileDialog and inserts the selected image into a SQL-Server database table.

    Notes

    In the class DataOperations, the following part you will not have so comment it out unless you install the package mentioned in the comments and the link in my signature, they are responsible for creating a connection string to open the database, you should already have a connection to use any ways.

    ConnectionString is also from the NuGet package.

    The following method does the INSERT.

    Imports System.Data.SqlClient
    Imports BaseConnectionLibrary.ConnectionClasses
    ''' <summary>
    ''' Connection made with this NuGet package
    ''' https://www.nuget.org/packages/BaseConnectionLibrary/1.0.3
    ''' 
    ''' DatabaseServer and DefaultCatalog are part of the above package
    ''' </summary>
    Public Class DataOperations
        Inherits SqlServerConnection
    
        Public Function InsertImage(imageBytes As Byte(), description As String) As Boolean
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = " INSERT INTO dbo.ImageData (ImageData, [Description]) VALUES (@img, @description); "
    
                    cmd.Parameters.Add("@img", SqlDbType.Image).Value = imageBytes
    
                    cmd.Parameters.Add("@description", SqlDbType.Text).Value =
                        If(String.IsNullOrWhiteSpace(description), "None", description)
    
                    Try
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
        End Function
        Public Sub New()
            DatabaseServer = "KARENS-PC"
            DefaultCatalog = "NORTHWND_NEW.MDF"
        End Sub
    End Class

    Form code, requires one button, one OpenFileDialog.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            OpenFileDialog1.InitialDirectory = "C:\Dotnet\VS2017\Images"
            If OpenFileDialog1.ShowDialog = DialogResult.OK Then
                Dim fileBytes As Byte() = IO.File.ReadAllBytes(OpenFileDialog1.FileName)
                Dim ops As New DataOperations
                If ops.InsertImage(fileBytes, Now.ToShortTimeString) Then
                    MessageBox.Show("Image added")
                Else
                    MessageBox.Show("Failed to add image")
                End If
            End If
        End Sub
    End Class

    Here is the schema for the table used above.

    CREATE TABLE [dbo].[ImageData]( 
        [ImageID] [INT] IDENTITY(1,1) NOT NULL, 
        [ImageData] [IMAGE] NULL, 
        [Description] [NVARCHAR](MAX) NULL, 
     CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED  
    ( 
        [ImageID] ASC 
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
     
    If you run into issues come back with specific details.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Tuesday, March 26, 2019 12:53 AM
    Moderator
  • Hi,

    try the code,insert and read images from the database

    Imports System.Data.SqlClient
    Imports System.IO
    
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim sda As SqlDataAdapter
        Dim ds As DataSet
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using conn = New SqlConnection(constr)
                conn.Open()
                cmd = New SqlCommand("insert into Image (Id,Image) values (1,@blobdata)", conn)
                Dim picturePath As String = "D:\test.jpg"
                Dim fs As FileStream = New FileStream(picturePath, FileMode.Open, FileAccess.Read)
                Dim mybyte As Byte() = New Byte(fs.Length - 1) {}
                fs.Read(mybyte, 0, mybyte.Length)
                fs.Close()
                Dim prm As SqlParameter = New SqlParameter("@blobdata", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, mybyte)
                cmd.Parameters.Add(prm)
                cmd.ExecuteNonQuery()
            End Using
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("select image from Image", conn)
                ds = New DataSet()
                sda.Fill(ds, "Image")
    
            End Using
    
            Dim mybyte As Byte() = New Byte(-1) {}
            mybyte = CType((ds.Tables("Image").Rows(0)("Image")), Byte())
            Dim ms As MemoryStream = New MemoryStream(mybyte)
                PictureBox1.Image = Image.FromStream(ms)
    
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MosheS Singer Tuesday, March 26, 2019 3:48 PM
    Tuesday, March 26, 2019 2:11 AM
  • Thank you both for your responses. There is just something that I don't quite understand.

    Can you please explain what is the source of 

    @blobdata

    How do I refer to the location of of the image in my form?

    What is the meaning of this symbol @ in the update query?

    Tuesday, March 26, 2019 3:19 PM
  • Thank you both for your responses. There is just something that I don't quite understand.

    Can you please explain what is the source of 

    @blobdata

    How do I refer to the location of of the image in my form?

    What is the meaning of this symbol @ in the update query?

    In my example below @ is a parameter marker for SQL-Server query. See this in Microsoft docs.

     cmd.Parameters.Add("@img", SqlDbType.Image).Value = imageBytes
    Now for MS-Access ? is used while for Oracle : is used.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 26, 2019 3:34 PM
    Moderator
  • Imports BaseConnectionLibrary.ConnectionClasses

    is making a error

    Tuesday, March 26, 2019 5:08 PM
  • Imports BaseConnectionLibrary.ConnectionClasses

    is making a error

    As mentioned in my first post, this import is not needed as you already have a connection string.

    For the record as mentioned earlier this is BaseConnectionLibrary.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 26, 2019 5:25 PM
    Moderator
  • error message

    System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'Where'.'

    Here is my sample of what I am trying to do

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If strdY = False Then Exit Sub
            Dim strFileName As String
            openFD.InitialDirectory = "C:\"
            openFD.Title = "Please Select a File"
            openFD.Filter = "jpegs|*.jpg|JPEG|*.jpg|gifs|*.gif|Bitmaps|*.bmp"
            Dim DidWork As Integer = openFD.ShowDialog()
            'ts Try
            If DidWork <> DialogResult.Cancel Then
                strFileName = openFD.FileName.ToString
                Me.Photo.Image = Image.FromFile(strFileName)
                Me.Photo.Visible = True
                openFD.Reset()

    Using SqlConct As New SqlConnection(SQLKT)
                            SqlConct.Open()
                            'Photo = Me.Photo.Image
                            Dim stri As String = "insert into ListNames (photo) VALUES(@Photo) Where ListNames.ID = " & Me.TID.Text
                            Dim cmdS As New SqlCommand(stri, SqlConct)
                            Dim fs As FileStream = New FileStream(strFileName, FileMode.Open, FileAccess.Read)
                            Dim mybyte As Byte() = New Byte(fs.Length - 1) {}
                            fs.Read(mybyte, 0, mybyte.Length)
                            fs.Close()
                            Dim prm As SqlParameter = New SqlParameter("@Photo", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, mybyte)
                            cmdS.Parameters.Add(prm)
                            cmdS.ExecuteNonQuery()
                        End Using

    And I am getting this error message:

    'Incorrect syntax near the keyword 'Where'.'

    What could be the problem?

    Tuesday, March 26, 2019 5:29 PM
  • I assume this line

    Dim stri As String = "insert into ListNames (photo) VALUES(@Photo) Where ListNames.ID = " & Me.TID.Text

    The first thing I would do is change the query to

    Dim stri As String = "insert into ListNames (photo) VALUES(@Photo) Where ID = @TID

    Then after the parameter added for photo add this

    cmdS.Parameter.AddWithValue("@TID",Me.TID.Text)

    Bottom line is every single value going to the INSERT INTO should use a parameter else problem like this happen.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 26, 2019 5:37 PM
    Moderator
  • I am still getting the same message

    System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'Where'.'

    Here is the sample code showing exactly how I used the code as you showed:

    Using SqlConct As New SqlConnection(SQLKT)
                            SqlConct.Open()
                            Dim stri As String = "insert into ListNames (photo) VALUES(@Photo) Where ID = @TID"
                            Dim cmdS As New SqlCommand(stri, SqlConct)
                            Dim fs As FileStream = New FileStream(strFileName, FileMode.Open, FileAccess.Read)
                            Dim mybyte As Byte() = New Byte(fs.Length - 1) {}
                            fs.Read(mybyte, 0, mybyte.Length)
                            fs.Close()
                            Dim prm As SqlParameter = New SqlParameter("@Photo", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, mybyte)
                            cmdS.Parameters.Add(prm)
                            cmdS.Parameters.AddWithValue("@TID", Me.TID.Text)
                            cmdS.ExecuteNonQuery()
                        End Using

    Tuesday, March 26, 2019 5:56 PM
  • I am still getting the same message

    System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'Where'.'

    Here is the sample code showing exactly how I used the code as you showed:

    Using SqlConct As New SqlConnection(SQLKT)
                            SqlConct.Open()
                            Dim stri As String = "insert into ListNames (photo) VALUES(@Photo) Where ID = @TID"
                            Dim cmdS As New SqlCommand(stri, SqlConct)
                            Dim fs As FileStream = New FileStream(strFileName, FileMode.Open, FileAccess.Read)
                            Dim mybyte As Byte() = New Byte(fs.Length - 1) {}
                            fs.Read(mybyte, 0, mybyte.Length)
                            fs.Close()
                            Dim prm As SqlParameter = New SqlParameter("@Photo", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, mybyte)
                            cmdS.Parameters.Add(prm)
                            cmdS.Parameters.AddWithValue("@TID", Me.TID.Text)
                            cmdS.ExecuteNonQuery()
                        End Using

    Forget what I said before, there is no WHERE clause for an INSERT. But there is a WHERE clause on a UPDATE statement. Sorry I'm bouncing between work and here.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by MosheS Singer Tuesday, March 26, 2019 6:05 PM
    Tuesday, March 26, 2019 6:01 PM
    Moderator
  • Hi,
    try this SQL string:

    Dim stri As String = "insert into ListNames (photo) VALUES(@Photo)"


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Tuesday, March 26, 2019 6:09 PM