Send picture in VB application to SQL database

Question

0.00/5 (No votes)

See more:

Hi, Im using visual studio 2017 to create a Visual basic application. I have been trying to upload an image to an sql database, all set up correctly, but cant for the life of me get it to work. The basics of the overall code is that a user uploads a members photo, and the code will update the image column of a selected member.

What I have tried:

I have tried everything on this website (before you link to older ones/ask why im submitting this) to no avail. YouTube, google, Microsoft community pages etc but no matter what I always get the same result in the database row/column :

VB

0x40696D67

I have even gone and mutilated my code a couple of times, using bytes to send, converting my image to another file format both inside and outside of the program and still nothing.
Here is the code I have been using, as far as I can tell this is what it should be with no problems.

VB

Try

               Dim connection As New SqlClient.SqlConnection(My.Settings.Memberinfo)
               Dim command As New SqlClient.SqlCommand("update " & My.Settings.currentaccessread & " set img='@img' where [First Name]= '" & TextBox2.Text & "' AND [LAST NAME]= '" & TextBox4.Text & "'", connection)

               Dim ms As New MemoryStream
               PictureBox1.BackgroundImage.Save(ms, pictureBox1.BackgroundImage.RawFormat)

               command.Parameters.Add("@img", SqlDbType.Image).Value = ms.ToArray()

               connection.Open()
               If command.ExecuteNonQuery() = 1 Then
                   MsgBox(TextBox2.Text & " has an updated image for this event.")
               Else
                   MsgBox("Unable to upload image to server for " & TextBox2.Text)
               End If

               connection.Close()


           Catch exa As Exception
               MsgBox(exa.ToString)
           End Try

any help would bre gratefully appreciated as I have no idea anymore. Thanks

Posted 15-Apr-18 8:53am

Member 10972113

Updated 15-Apr-18 9:27am

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 · Answer 1 · 2018-04-15T09:28:00

Solution 1

Never do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Posted 15-Apr-18 9:28am

OriginalGriff

Comments

Member 10972113 15-Apr-18 16:16pm

Hi, thanks for your response. And thanks for bringing that to my attention. I'm a design/mechatronics engineering student but have a hobby of playing with making programs, never used SQL before! As this is a self-learning project security isn't that much of an issue right now, and of course only know enough to make a database, and get it communicating with my application to do what I want it to do. Just want to get some results then build up to things such as security measures. But still always happy to learn!

Member 10972113 15-Apr-18 16:55pm

Just as a further to your response, would I be right in saying that this is now a parameterized query(bar the my.settings.accessread)? or is it something completely different.

Dim connection As New SqlClient.SqlConnection(My.Settings.Memberinfo)

Dim command As New SqlClient.SqlCommand("update " & My.Settings.currentaccessread & " set img='@img' where [First Name]= '@Fisrstname' AND [LAST NAME]= '@Lastname'", connection)

command.Parameters.Add("@Firstname", SqlDbType.VARCHAR(MAX)).Value = TextBox2.ToString()
command.Parameters.Add("@Lastname", SqlDbType.VARCHAR(MAX)).Value = TextBox4.ToString()

OriginalGriff 16-Apr-18 4:52am

Damn it! I already typed this once ... "our servers are a little overworked" ... grrrr ...

No, quotes delimit strings, so this:
set img='@img' where ...
tries to set the img column to a literal string '@img' instead of the content of the parameter. Remove the quotes!
You will get the same problem with @Firstname and @Lastname - again, those will be the literal strings.

Plus, TextBox2.ToString will give you a string like this:
System.Forms.TextBox, Text: the content of the Textbox
instead of the first or last names. Use the .Text property instead.

And I'd use Parameters.AddWithValue instead of Parameters.Add - it's a lot cleaner and more readable!