BLOB & MySQL problem

[nhunter98]

I am recieving an error when I am trying to write a blob file to MySQL. Can anyone help me figure out what I am doing wrong.

Here is my code:

Dim ScreenFileSize As UInt32 = 0
Dim ScreenFile() As Byte
Dim ScreenStream As FileStream

ScreenStream = New FileStream(ScreenImageLocation, FileMode.Open, FileAccess.Read)
ScreenFileSize = ScreenStream.Length
ScreenFile = New Byte(ScreenFileSize) {}
ScreenStream.Read(ScreenFile, 0, ScreenFileSize)
ScreenStream.Close()

Try
MySQLQuery = "UPDATE " & MySQLDatabase & ".btt (ScreenShot) VALUES(?FileUpload) WHERE Ticket='" & strTicketNo & "'"
MySQLCommand = New MySql.Data.MySqlClient.MySqlCommand(MySQLQuery, MySQLConnection)

MySQLConnection.Open()
MySQLCommand.CommandText = MySQLQuery
MySQLCommand.Prepare()
MySQLCommand.Parameters.Clear()

MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)

MySQLCommand.ExecuteNonQuery()
MySQLConnection.Close()

Catch ex As Exception

End Try
End If




Here is the first part of the error I recieve:
MySql.Data.MySqlClient.MySqlException was caught
ErrorCode=-2147467259
Message="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 '(ScreenShot) VALUES(_binary 'ÿØÿà\0JFIF\0\0`\0`\0\0ÿÛ\0C\0

' at line 1"
Number=1064
Source="MySql.Data"

[Shuja Ali]

The code looks ok to me. What version of MySQL are you using?

Also try doin git like this while adding a parameter
Dim imageFile As MySQLParameter
imageFile = New MySqlParameter(”?File”, ScreenFile)
MySQLCommand.Parameters.Add(imageFile)

[sotoasty]

It looks to me like you are mixing UPDATE and INSERT SQL Statements.

Insert should be.

MySQLQuery = "INSERT INTO " & MySQLDatabase & ".btt (ScreenShot) VALUES(?FileUpload);"



Update should be

MySQLQuery = "UPDATE " & MySQLDatabase & ".btt SET ScreenShot = ?FileUpload WHERE Ticket=?TN"

MySQLCommand.Prepare()
MySQLCommand.Parameters.Clear()
MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
MySQLCommand.Parameters.Add("?TN", strTicketNo)

[sotoasty]

The code looks ok to me. What version of MySQL are you using?

Also try doin git like this while adding a parameter
Dim imageFile As MySQLParameter
imageFile = New MySqlParameter(”?File”, ScreenFile)
MySQLCommand.Parameters.Add(imageFile)

This is a very good point. It appears from you code that you are using the

MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)

If this actually works, then you are using a very old version of the MySQL connector. I would recommend you upgrade. The statement in the new connector is...

MySQLCommand.Parameters.AddWithValue("?FileUpload", ScreenFile)

Or do it as Shuja Ali has suggested.

[nhunter98]

I have tried all of the above. All result in the same error.

I am using
MySQL version 5.1.30
MySql-connector-net-5.0.9
VB.NET 2008

Any other ideas??

[sotoasty]

put

messagebox.show(ex.message)

in the catch part and show us the error.

ETA: And can you show us how MySQLConnection is initialized.

[nhunter98]

here is the error:

+ ex {"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 'VALUES(_binary '‰PNG \0\0\0 IHDR\0\0\0\0\0
________________________________________
\0 \0\0\0¾“ôC\0\0\0sRGB\0®Î é\0' at line 1"} System.Exception


Here is the connection:
Friend strBTTConnString As String = "server=link_server;" & "uid=User;" & "pwd=password;" & "database=data;"

[Shuja Ali]

Remove these two statements and then try MySQLCommand.Prepare()
MySQLCommand.Parameters.Clear()

[sotoasty]

here is the error:

+ ex {"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 'VALUES(_binary '‰PNG \0\0\0 IHDR\0\0\0\0\0
________________________________________
\0 \0\0\0¾“ôC\0\0\0sRGB\0®Î é\0' at line 1"} System.Exception


Here is the connection:
Friend strBTTConnString As String = "server=link_server;" & "uid=User;" & "pwd=password;" & "database=data;"

This statement is telling you that it is a problem with your SQL Statement. It is telling you right where the issue is also. Everything upto the "Values" keyword is correct. Could you please post which SQL statement you are using. You seem to be confusing the UPDATE syntax with the INSERT syntax. If your query is the update statement, you need to use the update syntax I posted above. ie.

UPDATE TableName SET FieldToUpdate=Valueforthefield WHERE KeyFieldName=KeyFieldValue.

If you have not done so, you should download and install the MySQL gui tools. This has a query browser where you can try out your queries.

[nhunter98]

I tried removing:
modMySql.MySQLCommand.Prepare()
modMySql.MySQLCommand.Parameters.Clear()
this did not help.


this is the update statement I am using:
UPDATE btt.btt SET ScreenShot=VALUES(?FileUpload) WHERE Ticket='72'

[nhunter98]

I have also tried the code below which you asked me to do and get the same result.

modMySql.MySQLCommand.Parameters.AddWithValue("?FileUpload", ScreenFile)

[nhunter98]

here is all my code:

Dim ScreenFileSize As UInt32 = 0
Dim ScreenFile() As Byte
Dim ScreenStream As FileStream


ScreenStream = New FileStream(ScreenImageLocation, FileMode.Open, FileAccess.Read)
ScreenFileSize = ScreenStream.Length
ScreenFile = New Byte(ScreenFileSize) {}
ScreenStream.Read(ScreenFile, 0, ScreenFileSize)
ScreenStream.Close()

Try
modMySql.MySQLQuery = "UPDATE " & modMySql.MySQLDatabase & ".btt SET ScreenShot=VALUES(?FileUpload) WHERE Ticket='" & strTicketNo & "'"
modMySql.MySQLCommand = New MySql.Data.MySqlClient.MySqlCommand(modMySql.MySQLQuery, modMySql.MySQLConnection)

modMySql.MySQLConnection.Open()
modMySql.MySQLCommand.CommandText = modMySql.MySQLQuery
modMySql.MySQLCommand.Prepare()
modMySql.MySQLCommand.Parameters.Clear()

modMySql.MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)

modMySql.MySQLCommand.ExecuteNonQuery()
modMySql.MySQLConnection.Close()

Catch ex As Exception

End Try

[nhunter98]

we have progress, I found that I missed a post from above. My new code is:

modMySql.MySQLQuery = "UPDATE " & modMySql.MySQLDatabase & ".btt SET ScreenShot = ?FileUpload WHERE Ticket=?TN"

modMySql.MySQLConnection.Open()
modMySql.MySQLCommand.CommandText = modMySql.MySQLQuery
modMySql.MySQLCommand.Prepare()
modMySql.MySQLCommand.Parameters.Clear()
modMySql.MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
modMySql.MySQLCommand.Parameters.Add("?TN", strTicketNo)

My new error is:

+ ex {"Data too long for column 'ScreenShot' at row 52"} System.Exception


Any ideas on this error? Thanks again for all your help.

[nhunter98]

ok here is another update. I changed my column in my db to LONGBLOB instead of BLOB and my data is entered into the db.

So here is my next problem. How do you retrieve this file (it is a png file) from the db and display in an Picturebox? Can someone point me in the right direction.

Thanks again for all who have helped.

[nhunter98]

thanks everyone for your help. I have all my answers. You all have been a huge help. Thanks again.