MySQL syntax error on image upload

Question

I have a small file upload on a website that is supposed to upload a selected image and place it in a mysql database. The image is stored as type a varbinary (8000 length). My code is:

 Public Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    If fileupload1.HasFile Then

        Dim pimage As Byte() = fileupload1.FileBytes
        Dim pid As String = partnum.Text.ToString
        Dim sConnection As String = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=inteva; UID=root; PASSWORD=root; OPTION=3"
        Dim connectme As New OdbcConnection(sConnection)
        Dim sInsertInto As String = "INSERT INTO images(serial, image1) VALUES(?pserial, ?ppimage)"
        Dim com As New OdbcCommand(sInsertInto, connectme)

        com.Parameters.Add("pserial", OdbcType.VarChar).Value = pid
        com.Parameters.Add("?ppimage", OdbcType.VarBinary).Value = pimage

        connectme.Open()
        Dim result As Integer = com.ExecuteNonQuery()
        connectme.Close()

        If result > 0 Then
            lblimageup.Text = "Image saved."
        End If

    Else
        lblimageup.Text = "Please select an image file"

    End If
End Sub

When I execute this, I get a sytax error as follows:

ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.6.11]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pserial, 'ÿØÿá/þExif\0\0MM\0*\0\0\0\0\0\0\0\0\0\0\0’\0\0\0\0   \0\' at line 1

I can't seem to find any syntax error. Any ideas? Could it be the image file have something in it breaking the syntax?

Damith · Accepted Answer · 2013-06-04 17:16:32Z

1

try with below code, use @paramName in both SQL and Patameters.Add

Dim sInsertInto As String = "INSERT INTO images(serial, image1) VALUES (@pserial, @ppimage)"
Dim com As New OdbcCommand(sInsertInto, connectme)

com.Parameters.Add("@pserial", OdbcType.VarChar).Value = pid
com.Parameters.Add("@ppimage", OdbcType.VarBinary).Value = pimage

answered Jun 4, 2013 at 17:16

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

E Crux Over a year ago

This corrected the syntax error, but now I get an error saying "serial cannot be null" as it is my primary key in this database. There is a value being passed to pid, but I guess the parameter isn't picking this up. Why not?

Damith Over a year ago

@ECrux do you have value for pid? what id the data type in db?

E Crux Over a year ago

I do have the value in the textbox. The data type is varchar

Damith Over a year ago

@ECrux can you generate the create table script for your table and update the question with that? and also debug and see the value you get as pid before ExecuteNonQuery

keaton_fu · Accepted Answer · 2013-06-04 16:41:06Z

0

This doesn't look consistent; I think you wanted to do "?pserial".

com.Parameters.Add("pserial", OdbcType.VarChar).Value = pid
com.Parameters.Add("?ppimage", OdbcType.VarBinary).Value = pimage

answered Jun 4, 2013 at 16:41

keaton_fu

4422 silver badges9 bronze badges

1 Comment

E Crux Over a year ago

Ah, sorry I pasted the wrong param. I had already corrected the param to ?pserial. I still have the same error.

Collectives™ on Stack Overflow

MySQL syntax error on image upload

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related