none
problem when insert empty imag to database RRS feed

  • Question

  • hi

    The following code is used to add an image to the database
    A problem when there is no image in the Picturebox cod does not work
    If there is a picture in the
    Picturebox, the code works well

      Dim ms As New MemoryStream
                PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
                Dim img() As Byte
                img = ms.ToArray()
                UPDATE_COM.Parameters.AddWithValue("@WEBSERVERPAYED_IMAG", img)


    try

       If Me.PictureBox1.Image Is Nothing Then
                    img = CObj(DBNull.Value)
    
                Else
                    img = ms.ToArray()
    
                End If
    

    • Edited by ahmeddc Tuesday, October 2, 2018 7:01 AM
    Tuesday, October 2, 2018 6:29 AM

Answers

  • Unfortunately this field type you can't set a default value which is allowed in SQL-Server.

    What you can do (and I really don't care for this) is the following. What is wrong here is that to now test to see if there is an image in a record you need to test for the length of the returning byte array, is it 0? then there is no image, otherwise there is an image.

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        ''' <summary>
        ''' Default our connection to a database in the executable folder
        ''' </summary>
        Public Sub New()
            Builder.DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NorthWind.accdb")
        End Sub
        Public Sub Update()
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    Dim img(-1) As Byte
                    cmd.CommandText = "UPDATE Categories SET Picture = ? WHERE CategoryID = 1"
                    cmd.Parameters.AddWithValue("?", img)
                    cn.Open()
                    Dim results = cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    
    

    Here is what I did to validate the above.

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim ops = New DatabaseOperations
            ops.Update()
        End Sub
    
    End Class
    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        ''' <summary>
        ''' Default our connection to a database in the executable folder
        ''' </summary>
        Public Sub New()
            Builder.DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NorthWind.accdb")
        End Sub
        Public Sub Update()
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    Dim img(-1) As Byte
                    cmd.CommandText = "UPDATE Categories SET Picture = ? WHERE CategoryID = 1"
                    cmd.Parameters.AddWithValue("?", img)
                    cn.Open()
                    Dim results = cmd.ExecuteNonQuery() ' did it work?
    
                    cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID = 1"
                    Dim test = cmd.ExecuteScalar() ' double check
    
                    cmd.CommandText = "SELECT Picture FROM Categories"
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader())
                    ' inspect the length
                    For Each row As DataRow In dt.Rows
                        Console.WriteLine(row.Field(Of Byte())("Picture").Length)
                    Next
    
                    Console.WriteLine()
                End Using
            End Using
    
        End Sub
    End Class
    
    
    

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Tuesday, October 2, 2018 2:25 PM
    Tuesday, October 2, 2018 12:25 PM
    Moderator
  • If you use a dataadapter and bind the picbox accordingly you shouldnt run into this problem. Here is an access example

    Imports System.Data.OleDb
    
    Public Class AccessConn
        Dim dt As New DataTable
        Dim bs As New BindingSource
        Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    DA.Fill(dt)
                    bs.DataSource = dt
                    DataGridView1.DataSource = bs
    
                    PictureBox1.DataBindings.Add("Image", bs, "image", True)
                End Using
            End Using
    
        End Sub
    
        Private Sub Btn_BrowseForFile_Click(sender As Object, e As EventArgs) Handles Btn_BrowseForFile.Click
            Dim fb As New OpenFileDialog
            fb.InitialDirectory = "c:\"
            fb.Filter = "jpg files (*.jpg)|*.jpg|BMP files (*.bmp)|*.bmp"
            fb.RestoreDirectory = True
            If fb.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(fb.FileName)
            End If
        End Sub
    
        Private Sub Btn_Update_Click(sender As Object, e As EventArgs) Handles Btn_Update.Click
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"
                    DA.Update(dt)
                End Using
            End Using
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by ahmeddc Tuesday, October 2, 2018 2:25 PM
    Tuesday, October 2, 2018 1:02 PM

All replies

  • Seems like the logical path is if there is no image in the PictureBox then don't include that field in the UPDATE. The best way to do this is having two update statements, one without the image, the other with the image.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, October 2, 2018 9:50 AM
    Moderator
  • Consider this:

       Dim img() As Byte = Nothing

       If Me.PictureBox1.Image IsNot Nothing Then

          Dim ms As New MemoryStream

          PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)

          img = ms.ToArray()

       End If

       UPDATE_COM.Parameters.AddWithValue("@WEBSERVERPAYED_IMAG", img)

       . . .

    The database column should allow nulls.




    • Edited by Viorel_MVP Tuesday, October 2, 2018 11:01 AM
    Tuesday, October 2, 2018 11:01 AM
  • hi Viorel_<abbr class="affil"></abbr>

    code not work

    add only mnual way

     com.Parameters.AddWithValue("@WEBSERVERPAYED_IMAG", DBNull.Value)

    Tuesday, October 2, 2018 11:18 AM
  • Unfortunately this field type you can't set a default value which is allowed in SQL-Server.

    What you can do (and I really don't care for this) is the following. What is wrong here is that to now test to see if there is an image in a record you need to test for the length of the returning byte array, is it 0? then there is no image, otherwise there is an image.

    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        ''' <summary>
        ''' Default our connection to a database in the executable folder
        ''' </summary>
        Public Sub New()
            Builder.DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NorthWind.accdb")
        End Sub
        Public Sub Update()
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    Dim img(-1) As Byte
                    cmd.CommandText = "UPDATE Categories SET Picture = ? WHERE CategoryID = 1"
                    cmd.Parameters.AddWithValue("?", img)
                    cn.Open()
                    Dim results = cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    
    

    Here is what I did to validate the above.

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim ops = New DatabaseOperations
            ops.Update()
        End Sub
    
    End Class
    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        ''' <summary>
        ''' Default our connection to a database in the executable folder
        ''' </summary>
        Public Sub New()
            Builder.DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NorthWind.accdb")
        End Sub
        Public Sub Update()
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    Dim img(-1) As Byte
                    cmd.CommandText = "UPDATE Categories SET Picture = ? WHERE CategoryID = 1"
                    cmd.Parameters.AddWithValue("?", img)
                    cn.Open()
                    Dim results = cmd.ExecuteNonQuery() ' did it work?
    
                    cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID = 1"
                    Dim test = cmd.ExecuteScalar() ' double check
    
                    cmd.CommandText = "SELECT Picture FROM Categories"
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader())
                    ' inspect the length
                    For Each row As DataRow In dt.Rows
                        Console.WriteLine(row.Field(Of Byte())("Picture").Length)
                    Next
    
                    Console.WriteLine()
                End Using
            End Using
    
        End Sub
    End Class
    
    
    

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Tuesday, October 2, 2018 2:25 PM
    Tuesday, October 2, 2018 12:25 PM
    Moderator
  • If you use a dataadapter and bind the picbox accordingly you shouldnt run into this problem. Here is an access example

    Imports System.Data.OleDb
    
    Public Class AccessConn
        Dim dt As New DataTable
        Dim bs As New BindingSource
        Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    DA.Fill(dt)
                    bs.DataSource = dt
                    DataGridView1.DataSource = bs
    
                    PictureBox1.DataBindings.Add("Image", bs, "image", True)
                End Using
            End Using
    
        End Sub
    
        Private Sub Btn_BrowseForFile_Click(sender As Object, e As EventArgs) Handles Btn_BrowseForFile.Click
            Dim fb As New OpenFileDialog
            fb.InitialDirectory = "c:\"
            fb.Filter = "jpg files (*.jpg)|*.jpg|BMP files (*.bmp)|*.bmp"
            fb.RestoreDirectory = True
            If fb.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(fb.FileName)
            End If
        End Sub
    
        Private Sub Btn_Update_Click(sender As Object, e As EventArgs) Handles Btn_Update.Click
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"
                    DA.Update(dt)
                End Using
            End Using
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by ahmeddc Tuesday, October 2, 2018 2:25 PM
    Tuesday, October 2, 2018 1:02 PM