Save large MB of image to mysql database VB.NET

Question

0.00/5 (No votes)

See more:

this code is working but when i try to save 250 up 500 plus image MB it shows error the i rich the limit, can you help me guys to save large MB of image to MYSQL database using VB.net, Thank you..

What I have tried:

Private Sub SAVECATEGORY_Click(sender As Object, e As EventArgs) Handles SAVECATEGORY.Click

       Dim ms As New MemoryStream  '...........need to Imports System.IO from general
       PictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Png) ' ........save IMAGE

       If PictureBox1.Image Is Nothing Then
           MessageBox.Show("Please Insert image ")
           addcategoryclear()           'you call ddcategoryclear()  to clear textbox and picturebox
       ElseIf TextBox1.Text = "" Then
           MessageBox.Show("Please Insert Image Name")
           addcategoryclear()           'you call ddcategoryclear()  to clear textbox and picturebox
       Else
           '..............
           cn.Open()
           Dim command As New MySqlCommand("INSERT INTO `category`( `IMG`, `NAME`) VALUES (@IMG,@NAME)", cn)
           With command.Parameters

               .Add("@IMG", MySqlDbType.Blob).Value = ms.ToArray()
               .AddWithValue("NAME", TextBox1.Text)
           End With

           If command.ExecuteNonQuery() = 1 Then
               MessageBox.Show("You Successfully Save")
               addcategoryclear() 'you call ddcategoryclear()  to clear textbox and picturebox
               loadcategory()
           Else
               MessageBox.Show("Something Wrong Pleas Try again")
           End If
           cn.Close()
       End If
       Me.Refresh()
   End Sub

Posted 25-Dec-19 22:57pm

Member 13910887

Updated 1-Jan-20 15:25pm

Add a Solution

Comments

MadMyche 26-Dec-19 7:05am

What is the error message you receive?

Member 13910887 26-Dec-19 20:18pm

this is the error.
MySql.Data.MySqlClient.MySqlException: 'Packets larger than max_allowed_packet are not allowed.'

3 solutions

Solution 4

the error is the same, i dont think if i miss place the

SET SESSION max_allowed_packet=1024*1024*1024

Private Sub SAVECATEGORY_Click(sender As Object, e As EventArgs) Handles SAVECATEGORY.Click

       Dim ms As New MemoryStream  '...........need to Imports System.IO from general


       If PictureBox1.Image Is Nothing Then
           MessageBox.Show("Please Insert image ")
           addcategoryclear()           'you call ddcategoryclear()  to clear textbox and picturebox
       ElseIf TextBox1.Text = "" Then
           MessageBox.Show("Please Insert Image Name")
           addcategoryclear()           'you call ddcategoryclear()  to clear textbox and picturebox
       Else
           cn.Open()
           PictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Png) ' ........save IMAGE

           Dim command As New MySqlCommand("INSERT INTO `category`( `IMG`, `NAME`) VALUES (@IMG,@NAME)", cn)

           With command.Parameters
               ' Set SESSION max_allowed_packet=1024*1024*1024
               cmd.CommandText = "SET SESSION max_allowed_packet=32*1024*1024;"
               .Add("@IMG", MySqlDbType.Blob).Value = ms.ToArray()
               .AddWithValue("NAME", TextBox1.Text)
           End With

           If command.ExecuteNonQuery() = 1 Then
               MessageBox.Show("You Successfully Save")
               addcategoryclear() 'you call ddcategoryclear()  to clear textbox and picturebox
               loadcategory()
           End If
           MessageBox.Show("Something Wrong Pleas Try again")
       End If
       cn.Close()
       Me.Refresh()
   End Sub

Posted 1-Jan-20 15:13pm

Member 13910887

Solution 5

i also try this but the error is the same

Private Sub SAVECATEGORY_Click(sender As Object, e As EventArgs) Handles SAVECATEGORY.Click
     Dim ms As New MemoryStream  '...........need to Imports System.IO from general

     If PictureBox1.Image Is Nothing Then
         MessageBox.Show("Please Insert image ")
         addcategoryclear()           'you call ddcategoryclear()  to clear textbox and picturebox
     ElseIf TextBox1.Text = "" Then
         MessageBox.Show("Please Insert Image Name")
         addcategoryclear()           'you call ddcategoryclear()  to clear textbox and picturebox
     Else
         cn.Open()
         PictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Png) ' ........save IMAGE

         Dim command As New MySqlCommand("INSERT INTO `category`( `IMG`, `NAME`) VALUES (@IMG,@NAME)", cn)

         With command.Parameters
             ' Set SESSION max_allowed_packet=1024*1024*1024
             .Add("@IMG", MySqlDbType.Blob).Value = ms.ToArray()
             .AddWithValue("NAME", TextBox1.Text)
         End With
         '  cmd.CommandText = "SET SESSION max_allowed_packet=32*1024*1024;"
         If command.ExecuteNonQuery() = "SET SESSION max_allowed_packet=1024*1024*1024" Then
             MessageBox.Show("You Successfully Save")
             addcategoryclear() 'you call ddcategoryclear()  to clear textbox and picturebox
             loadcategory()
         End If
         MessageBox.Show("Something Wrong Pleas Try again")
     End If
     cn.Close()
     Me.Refresh()
 End Sub

Posted 1-Jan-20 15:25pm

Member 13910887

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)

MadMyche · Accepted Answer · 2019-12-26T14:33:00

Solution 1

Now that we know what the problem is, we can figure out what it means, the cause, and possible fixes.

Member 13910887
this is the error.
MySql.Data.MySqlClient.MySqlException:
Packets larger than max_allowed_packet are not allowed

So your query is too long; apparently the default limit is 1MB for the query statement, but this can be modified in various ways.
Read through this article and try it out.
Packets larger than max_allowed_packet are not allowed · MySqlBackupNET/MySqlBackup.Net Wiki · GitHub[^]

My recommendation would only be to do this when needed; if for some chance reason the server was compromised a smaller size limit means less can be injected via a single command.

Posted 26-Dec-19 14:33pm

MadMyche

Comments

Member 13910887 27-Dec-19 0:47am

can you help me where i should place that in my code
thanks

MadMyche 27-Dec-19 8:12am

I would run the SET SESSION max_allowed_packet=1024*1024*1024 as a separate command text and execute before your insert/update command

Member 13910887 1-Jan-20 20:13pm

would you check please if i'm wrong, i dont know, where i would place this code SET SESSION max_allowed_packet=1024*1024*1024

MadMyche 1-Jan-20 22:32pm

Try this out

Dim qry1 as String = "SET SESSION max_allowed_packet=32*1024*1024;"
Dim qry2 as String = "INSERT INTO `category`( `IMG`, `NAME`) VALUES (@IMG,@NAME)"

Dim command As New MySqlCommand(qry1 + qry2, cn)
With command.Parameters
  .Add("@IMG", MySqlDbType.Blob).Value = ms.ToArray()
  .AddWithValue("NAME", TextBox1.Text)
End With

Member 13910887 1-Jan-20 22:44pm

i try the code
the error is the same
at this part always the error box show
If command.ExecuteNonQuery() = 1 Then
MessageBox.Show("You Successfully Save")
addcategoryclear() 'you call ddcategoryclear() to clear textbox and picturebox
loadcategory()
End If
MessageBox.Show("Something Wrong Pleas Try again")
End If

MadMyche 1-Jan-20 23:56pm

You may want to check to see if you have permissions to make these changes- this should have worked

Member 13910887 2-Jan-20 1:16am

i what to determine how to fix the problem, to that i go jump to the net process

Member 13910887 2-Jan-20 20:37pm

i try to save image only, and remove other query but the error is the same

Member 13910887 2-Jan-20 22:43pm

Hi... I notice that the image that i want to save is not a part of (*.JPG;*.PNG;.*GIF;)|*.jpg;*.png;*.gif"), so that the image that, i trying to save is "jfif"
i search online and i convert the image to the following