任何人都可以帮助将图像更新到数据库 [英] Can anyone help to update an image to database

问题描述

大家好



我有一个表格AddEdit。插入功能工作正常，但当我需要更新联系人时，它会让我错误的照片。如果做了更新，我重新连接照片工作正常。每次进行更新时，我都不想重新附加照片。

以下是代码：

Hi guys

I have a form AddEdit. The insert function works fine but when I need to update the contact it gives me error with the photo. If a made an update and I reattach the photo works fine. I want not to reattach the photo everytime I make an update.
Here is the code:

Imports System.Data.SqlClient
Imports System.IO

Public Class frmAddEdit
    Dim cmd As SqlCommand = New SqlCommand()

    Private Sub ButtonCancel_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles ButtonCancel.Click
        Close()
    End Sub

    Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles ButtonSave.Click
        If txtLname.Text = "" Or txtFtname.Text = "" Or _
            txtBdate.Text = "" Or txtCity.Text = "" Or txtBrigate.Text = "" Or txtCompany.Text = "" Then
            MsgBox("Παρακαλώ συμπληρώστε τα απαιτούμενα * πεδία....", MsgBoxStyle.Critical, "Προσοχή...")
        End If
        If frUpdate = True Then
            Dim cmd As New SqlCommand()
            cmd.CommandText = "UPDATE Staff SET Lastname = @Lastname, Firstname = @Firstname, Fathername = @Fathername, Birthday = @Birthday, City = @City, Photo = @Photo WHERE StaffID = @StaffID"
            With cmd.Parameters
                .Add(New SqlParameter("@Lastname", txtLname.Text))
                .Add(New SqlParameter("@Firstname", txtFname.Text))
                .Add(New SqlParameter("@Fathername", txtFtname.Text))
                .Add(New SqlParameter("@Birthday", txtBdate.Text))
                .Add(New SqlParameter("@City", txtCity.Text))
                .Add(New SqlParameter("@StaffID", txtAA.Text))
                Dim picphoto As New SqlClient.SqlParameter("@Photo", SqlDbType.Image)
                picphoto.Value = ConvertImageToByte(pictBox.Image)
                .Add(picphoto)
            End With
            cmd.Connection = clsMSSQL.con
            cmd.ExecuteNonQuery()
            MsgBox("Επιτυχημένη Ενημέρωση!", MsgBoxStyle.Information, "Πληροφορία")
            frmList.cmdExcel.PerformClick()
            Exit Sub
        Else
            Dim cmd As New SqlCommand()
            cmd.CommandText = "INSERT INTO Staff(Lastname,Firstname,Fathername,Birthday,City,Photo)" & _
                                 "VALUES (@Lastname,@Firstname,@Fathername,@Birthday,@City,@Photo)"
            With cmd.Parameters
                .Add(New SqlParameter("@Lastname", txtLname.Text))
                .Add(New SqlParameter("@Firstname", txtFname.Text))
                .Add(New SqlParameter("@Fathername", txtFtname.Text))
                .Add(New SqlParameter("@Birthday", txtBdate.Text))
                .Add(New SqlParameter("@City", txtCity.Text))
                Dim picphoto As New SqlClient.SqlParameter("@Photo", SqlDbType.Image)
                picphoto.Value = ConvertImageToByte(pictBox.Image)
                .Add(picphoto)
            End With
            cmd.Connection = clsMSSQL.con
            cmd.ExecuteNonQuery()
            MsgBox("Επιτυχημένη Αποθήκευση!", MsgBoxStyle.Information, "Πληροφορία")
            Call LoadID()
        End If
        frmList.cmdExcel.PerformClick()
        Exit Sub
    End Sub

    Private Sub frmAddEdit_FormClosed(ByVal sender As Object, ByVal e As FormClosedEventArgs) Handles Me.FormClosed
        frUpdate = False
    End Sub

    Private Sub frmAddEdit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If clsMSSQL.con.State = ConnectionState.Open Then
            clsMSSQL.con.Close()
        End If
        clsMSSQL.con.Open()
        picshow()
    End Sub

    Private Sub LoadID()
        If frUpdate = False Then
            txtAA.Text = ""
            txtLname.Enabled = True
            txtLname.Text = ""
            txtFname.Text = ""
            txtFtname.Text = ""
            txtBdate.Text = ""
            txtCity.Text = ""
            pictBox.Image = Nothing
            cmd.CommandText = "Select * from Staff"
        End If
    End Sub

    Private Sub BBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BBrowse.Click
        Dim dlg As OpenFileDialog
        Dim img As Image
        Try
            dlg = New OpenFileDialog
            dlg.Filter = "All Pictures|*.bmp;*.gif;*.jpg;*.png|" & _
             "Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg|PNGs|*.png"
            If dlg.ShowDialog = DialogResult.OK Then
                Me.Cursor = Cursors.WaitCursor
                pictBox.Image = New Bitmap(dlg.FileName)
            End If
        Catch ex As Exception
            MsgBox("Error", MsgBoxStyle.Critical, "error")
        Finally
            img = Nothing
            dlg = Nothing
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Public Function ConvertImageToByte(ByVal Img As Image) As Byte()
        Dim ms As MemoryStream = New MemoryStream()
        Img.Save(ms, Img.RawFormat)
        Dim data(CInt(ms.Length)) As Byte
        ms.Position = 0
        ms.Read(data, 0, CInt(ms.Length))
        ms.Close()
        ms = Nothing
        ConvertImageToByte = data
    End Function

    Public Sub picshow()
        Using cmd As New SqlCommand("Select * From Staff where StaffID=''" & txtAA.Text & "''", clsMSSQL.con)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                Using dt As New Data.DataTable
                    dt.Load(dr)
                    Try
                        Dim row As DataRow = dt.Rows(0)
                        Using ms As New IO.MemoryStream(CType(row("Photo"), Byte()))
                            Dim img As Image = Image.FromStream(ms)
                            pictBox.Image = img
                        End Using
                    Catch ex As Exception
                        GoTo err
                    End Try
                    Exit Sub
                End Using
            End Using
        End Using
        clsMSSQL.con.Close()
err:
    End Sub
End Class

提前致谢

Thanks in advance

推荐答案

替换

Replace

............
Dim picphoto As New SqlClient.SqlParameter("@Photo", SqlDbType.Image)
                picphoto.Value = ConvertImageToByte(pictBox.Image)
                .Add(picphoto)

...............

with

with

dim ByteImage as byte() = ConvertImageToByte(pictBox.image)

.Add(New SqlParameter("@Photo", SqlDbType.Image, 2147483647, ParameterDirection.Input, True, 0, 0, "", DataRowVersion.Proposed, IIf(IsNothing(ByteImage), DBNull.Value, ByteImage )))

函数获取immage的字节

function get Byte of immage

Private Function ConvertImageToByte(byVal _MyImage As Bitmap ) As Byte()
       Dim buffer() As Byte
       Using ms As New IO.MemoryStream()
           _MyImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
           buffer = ms.GetBuffer
           ms.Close()
       End Using
       GC.Collect()
       GC.Collect()
       Return buffer
   End Function

这篇关于任何人都可以帮助将图像更新到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！