How do I dynamically upload images to SQL server database?

Question

0.00/5 (No votes)

See more:

The code I have works for making columns and adding images to the datagridview, but now I am moving on towards saving these images into a database.

What I do know is that columns for images must be added, so I have this portion of code:

Try
            Dim sqlcon As New SqlConnection("mysqlstatement")
            Dim sqladapt = New SqlDataAdapter("Select * from [Table]", sqlcon)

            sqlcon.Open()
            Dim cmd As SqlClient.SqlCommand
            Dim sql As String = "ALTER TABLE [table] ADD IMG image"
            cmd = New SqlClient.SqlCommand(sql, sqlcon)

            cmd.ExecuteNonQuery()
            sqlcon.Close()

            MessageBox.Show(sql.ToString)
        Catch ex As Exception


        End Try

then I need to tie this into what I already have which is:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

<pre>ofd.Multiselect = True
        If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then

            Dim fileCount = ofd.FileNames.Length
            Dim images(fileCount - 1) As Image


            For i As Integer = 0 To fileCount - 1
                If i >= DataGridView1.Columns.Count Then
                    Dim col As New DataGridViewImageColumn()

                    DataGridView1.Columns.Add(col)


                End If

                'adds images to panel control
                images(i) = Image.FromFile(ofd.FileNames(i))


            Next

            DataGridView1.Rows.Add(images)


        End If

The problem is I am unsure how to make multiple columns dynamically with an sql statement. After the dynamic sql column statement, then how do I input images at the same time, without making another sql statement? Figured with the right guidance I could learn something new and expand from there.

What I have tried:

Tried to tie in the columns created with the

Dim col As New DataGridViewImageColumn()

and tried to string that, but I am assuming the reason that it wasn't working was because the columns were the same name and SQL does not like that.

I also thought about using a for each statement for the pictures being uploaded into the gridview, but was unsure how to write that in combination of the sql statement.

So I am a little lost when it comes to something this complex, or at least to me its complex.

Posted 6-May-17 21:08pm

Member 11856456

Updated 6-May-17 22:33pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-05-06T22:33:00

Don't even try to modify the table: that's the wrong approach.
I assume you are trying to add columns because you have an unknown number of images related to a specific row, and think that adding a column for each will do what you want. It won't. When you add a column, you add it to every row in the table - so if you have one row which needs space for 20 images then every row gets that space, even if they have only one image.

Instead, don't have any rows in the table for storing images at all!
Lets assume these are product pictures, and that a product can have a variable number of images - just as something for sale on eBay or Amazon might.
You have your Products table:

ID              INT, IDENTITY (or UNIQUEIDENTIFIER, depending on your preference)
Title           NVARCHAR(255)
Description     NVARCHAR(MAX)
Price           DECIMAL(8,2)
Stock           INT

But instead of adding IMAGE columns, you create a second table Images:

ID              INT, IDENTITY (Or UNIQUEIDENTIFIER)
ProductID       Same as ID in the Products table, FOREIGN KEY to Products.ID
ImageData       IMAGE

You can then add as many images as you need to a Product, and retrieve them using the Product ID when you need them.