mysql - 如何更新mysql数据库中的图片

Question

我在这里尝试使用此代码将图片从数据库插入并显示到图片框:stackoverflow.com/questions/5624760/store-picture-to-database-retrieve-from-db-into-picturebox

是的，它正在工作，但是当我尝试使用与插入相同的语法进行更新时，出现了此错误:

这是我使用的插入语法:

Dim FileSize As UInt32
Dim mstream As New System.IO.MemoryStream()
gambar.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)  
Dim arrImage() As Byte = mstream.GetBuffer()

FileSize = mstream.Length

mstream.Close()

call konek
strSQL = "insert into tbmahasiswa VALUES ('" & _
                    txtNIM.Text & "','" & _
                    txtNama.Text & "','" & _
                    Format(dtpTanggal.Value, "yyyy-MM-dd") & "','" & _
                    txtAlamat.Text & "','" & _
                    cboJurusan.Text & "',@gambar)"
com = New MySql.Data.MySqlClient.MySqlCommand(strSQL, conn)

With com
  .Parameters.AddWithValue("@gambar", arrImage)
  .ExecuteNonQuery()
End With

这是我用来将图片从数据库显示到图片框的代码:

Dim imgData As Byte()

call konek
strSQL = "select * from tbMahasiswa where NIM ='" & txtNIM.Text & "'"
com = New MySql.Data.MySqlClient.MySqlCommand(strSQL, conn)
Using rd = com.ExecuteReader

  rd.Read()
  If rd.HasRows Then
            txtNama.Text = rd.Item(1)
            dtpTanggal.Value = rd.Item(2)
            txtAlamat.Text = rd.Item(3)

            imgData = TryCast(rd.Item(5), Byte())

            If imgData IsNot Nothing Then

                Using ms As New MemoryStream(imgData)

                    gambar.Image = CType(Image.FromStream(ms), Image)

                End Using
            End If

        End If

    End Using

上面的插入和检索图片代码都可以工作! ，然后我使用此代码进行更新:

Dim FileSize As UInt32
Dim mstream As New System.IO.MemoryStream()
gambar.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)  
Dim arrImage() As Byte = mstream.GetBuffer()

FileSize = mstream.Length

mstream.Close()

call konek
strSQL = "update tbmahasiswa set Nama ='" & txtNama.Text & _
                     "', TglLahir ='" & Format(dtpTanggal.Value, "yyyy-MM-dd") & _
                     "', Alamat ='" & txtAlamat.Text & _
                     "', Jurusan ='" & cboJurusan.Text & _
                     "', gambar =' @gambar" & _
                     "' where NIM ='" & txtNIM.Text & "'"
            com = New MySql.Data.MySqlClient.MySqlCommand(strSQL, conn)

With com
  .Parameters.AddWithValue("@gambar", arrImage)
  .ExecuteNonQuery()
End With

然后我得到了如上图所示的错误，除了图片之外的所有其他数据都正确保存，它变成了一些未知的文件 blob 8 KB 大小。

我还是VB中插入、更新、删除图片的新手，请你告诉我更新语法代码有什么问题，它已经是真的但我遗漏了一些东西吗？或者语法完全错误？我需要你的指导...

更新:实际上代码中的“STRSQL”上面有“call konek”，“konek”有打开mysql连接的代码，我把它放在单独的模块中，这里是我的模块中的完整代码:

Module modKoneksi

Public conn As New MySql.Data.MySqlClient.MySqlConnection
Public rd As MySql.Data.MySqlClient.MySqlDataReader
Public com As MySql.Data.MySqlClient.MySqlCommand

Public strSQL As String

Public Sub konek()
    conn.Close()
    strSQL = "server='localhost';user='root';pwd='';database='dbsekolah';"
    Try
        conn.ConnectionString = strSQL
        conn.Open()

    Catch ex As MySql.Data.MySqlClient.MySqlException
        MsgBox(ex.Message)
        End
    End Try
End Sub
End Module

希望这能让您更轻松地解决我的问题

Answer 1

我知道已经晚了，但这是我的工作代码:

用于从数据库中检索带有图片的记录:

    Call konek() 'Call the connection module'
    strSQL = "select * from tbMahasiswa where ID ='" & txtID.Text & "'"
    com = New MySql.Data.MySqlClient.MySqlCommand(strSQL, conn)
    Using rd = com.ExecuteReader

        rd.Read()
        If rd.HasRows Then
            txtNIM.Text = rd.Item(1)
            txtNama.Text = rd.Item(2)
            dtpTanggal.Value = rd.Item(3)
            txtAlamat.Text = rd.Item(4)


            imgData = TryCast(rd.Item(6), Byte())

            If imgData IsNot Nothing Then


                Using ms As New MemoryStream(imgData)

                    gambar.Image = CType(Image.FromStream(ms), Image)

                End Using
            End If
            cboJurusan.SelectedIndex = cboJurusan.FindStringExact(rd.Item(5))
        End If

    End Using

用于将带有图片的记录插入数据库:

Call konek()

strSQL = "Insert Into tbmahasiswa Values ('" & txtID.Text & _
    "','" & txtNIM.Text & _
    "','" & txtNama.Text & _
    "','" & Format(dtpTanggal.Value, "yyyy-MM-dd") & _
    "','" & txtAlamat.Text & _
    "','" & cboJurusan.Text & _
    "',@gambar)"
com = New MySql.Data.MySqlClient.MySqlCommand(strSQL, conn)

With com
    If opdGambar.FileName = Nothing Then 'opdGambar is a PictureBox name'
        .Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter("@gambar", MySql.Data.MySqlClient.MySqlDbType.LongBlob)).Value = IO.File.ReadAllBytes("man-icon.png") 'Insert field gambar using an existing file in debug folder if file does not exist in PictureBox'
    Else
        .Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter("@gambar", MySql.Data.MySqlClient.MySqlDbType.LongBlob)).Value = IO.File.ReadAllBytes(opdGambar.FileName) 'Insert field gambar using an existing file in PictureBox'
    End If

    com.ExecuteNonQuery()
End With   

用于将记录更新到带有图片的数据库:

Call konek()
Dim adapter As New MySql.Data.MySqlClient.MySqlDataAdapter("select gambar from tbmahasiswa where ID='" & txtID.Text & "'", conn)

Dim dt As New DataTable("gambar")
adapter.Fill(dt)

strSQL = "update tbmahasiswa set NIM='" & txtNIM.Text & _
         "',Nama='" & txtNama.Text & _
         "',TglLahir='" & Format(dtpTanggal.Value, "yyyy-MM-dd") & _
         "',Alamat='" & txtAlamat.Text & _
         "',Jurusan='" & cboJurusan.Text & _
         "' ,Gambar=@gambar where id='" & txtID.Text & "'"
com = New MySql.Data.MySqlClient.MySqlCommand(strSQL, conn)

With com

    If opdGambar.FileName = Nothing Then 
        Dim row As DataRow = dt.Rows(0)
        Using ms As New IO.MemoryStream(CType(row(0), Byte()))
            Dim img As Image = Image.FromStream(ms)
            gambar.Image = img

            .Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter("@gambar", MySql.Data.MySqlClient.MySqlDbType.LongBlob)).Value = (CType(row(0), Byte())) 'field gambar will use the current existing file in database if PictureBox does not have a file'

        End Using
    Else
        .Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter("@gambar", MySql.Data.MySqlClient.MySqlDbType.LongBlob)).Value = IO.File.ReadAllBytes(opdGambar.FileName) 
    End If

    com.ExecuteNonQuery()

End With                       

我希望那些觉得其他答案有点令人困惑的人(像我一样)会发现这个答案很有帮助。