Inserting multiple images into SQL Server

Question

First of all, I'd like to point out that I'm very new to programming and still learning.

I'm trying to insert multiple images into my table with a click of a button. I have my images stored in a folder and the file names of these images match with ID column in my SQL Server table.

My table has 30 columns and 5000 rows. I'm able to connect my table but I just don't know how to insert all images in this folder to my table with matching ID (i.e if ID=123456 I want to insert an image file named 123456.jpg to the column Photo).

Below is my code and any help would be appreciated. By the way, column Photo is of datatype varbinary(max).

Dim con As SqlConnection
Dim cmd As SqlCommand

Dim ds As New DataSet

con = New SqlConnection("server=MY-PC; Initial Catalog=Northwind;Integrated Security=SSPI")
cmd = New SqlCommand()
con.Open()
cmd.Connection = con

BindingSource1.DataSource = 

cmd.CommandText = "SELECT * FROM Northwind WHERE ID LIKE '" + TextBox1.Text + "'"
' dr = cmd.ExecuteReader
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable
adapter.Fill(table)

BindingSource1.DataSource = table
DataGridView1.DataSource = BindingSource1

I tried:

Dim BS As New BindingSource 'assumes this is bound to your table already
PictureBox1.DataBindings.Add("Image", BS, "ImageCol")

For Each DrowView As DataRowView In BS
        PictureBox1.Image = Image.FromFile("PathToImages\" & DrowView("NameID") & ".jpg")

        Try
            Using SqlConn As New SqlConnection("server=MY-PC; Initial Catalog=Northwind;Integrated Security=SSPI")
                Using NorthwindDA As New SqlDataAdapter("SELECT * FROM Northwind Where ID like '" + TextBox1.Text + "'", SqlConn)
                    Using NorthwindCB As New SqlCommandBuilder(NorthwindDA)
                        NorthwindDA.Update(table)
                    End Using
                End Using
            End Using
        Catch ex As Exception
            'Handle exception
        End Try
Next

I'm getting an error

Cannot bind to the property or column Image on the DataSource. Parameter name: dataMember

Look here... https://www.sqlshack.com/upload-multiple-images-sql-server/ and here... https://stackoverflow.com/questions/15659835/how-to-store-image-in-sql-server-database-tables-column hopefully these help. — SS_DBA, Apr 29 '19 at 16:55
Thank you for the reply but I just don't know how to do it for 5000 times. I know I need a loop but how? — tcoder, Apr 29 '19 at 16:58
You would need to Select the rows from the database that has the path name where your files are located, then each file = key, then update the Photo field with that image. Loop through the array list of images. — SS_DBA, Apr 29 '19 at 17:02
So I'm able to add a openfiledialog to my form and get the folder path to X. From here I tried ; For each Rows in DataGridWiev1.Rows cmd.Commandtext="INSERT INTO Northwind Where Image=" +ID+".jpg" Next But still no results — tcoder, Apr 29 '19 at 17:07
You won't want to `INSERT`, since the rows are already there... You want to `Update`. See Mr. Tripodi's answer for solution. — SS_DBA, Apr 29 '19 at 17:14
Sory, @tcoder, The answer I provided assumed you understood your own code. — Mr. Tripodi, Apr 29 '19 at 18:20

score 0 · Answer 1 · answered Apr 29 '19 at 17:08

Since you're using a bindingsource you could iterate the list items, set a PictureBox.Image property to the image from file, which is also databound to the bindingsource then update the table. Otherwise you're going to get into ByteArrays, Streams, Etc..

    Dim BS As New BindingSource 'assumes this is bound to your table already
    PictureBox1.DataBindings.Add("Image", BS, "ImageCol")

    For Each DrowView As DataRowView In BS
        PictureBox1.Image = Image.FromFile("PathToImages\" & DrowView("NameID") & ".jpg")

        Try
            Using SqlConn As New SqlConnection("server=MY-PC; Initial Catalog=Northwind;Integrated Security=SSPI")
                Using NorthwindDA As New SqlDataAdapter("SELECT * FROM Northwind Where ID like '" + TextBox1.Text + "'", SqlConn)
                    Using NorthwindCB As New SqlCommandBuilder(NorthwindDA)
                        NorthwindDA.Update(table)
                    End Using
                End Using
            End Using
        Catch ex As Exception
            'Handle exception
        End Try

    Next

I'm getting the error ; Cannot bind to the property or column Image on the DataSource. Parameter name: dataMember" — tcoder, Apr 29 '19 at 18:05

Inserting multiple images into SQL Server

1 Answers1