[2005] Image or Photo in database

[kiwis]

Can i store a image in a DB without just saving it's location??? ie. if I have just a .mdb and I want to access this or send this file via email can someone view a photo?? if so how?

[jmcilhinney]

See the appropriate link in my signature.

[kiwis]

Great, what type of MDB field do i need?

[kiwis]

This is my code plus my error... what is wrong the DB has number field with a byte size...

 Try
            OpenFileDialog1.Filter = "Image Files (*.jpg)|*.jpg"
            OpenFileDialog1.ShowDialog()
            PictureBox1.ImageLocation = OpenFileDialog1.FileName


            Dim connection As New Data.OleDb.OleDbConnection(My.Settings.Database1ConnectionString)
            Dim sqlqry As New OleDb.OleDbCommand("UPDATE Contacts SET Picture = @Picture WHERE ID = " & TextBox8.Text, connection)

            Using picture As Image = Image.FromFile(PictureBox1.ImageLocation)
                Using stream As New IO.MemoryStream
                    picture.Save(stream, Imaging.ImageFormat.Jpeg)
                    sqlqry.Parameters.Add("@Picture", OleDb.OleDbType.VarBinary).Value = stream.GetBuffer()
                    'SqlDbType.Image).Value = stream.GetBuffer()
                End Using
            End Using

          
            connection.Open()
            sqlqry.ExecuteNonQuery()
            connection.Close()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

[jmcilhinney]

I'll run a few tests myself with Access and get back to you.

[kiwis]

Cheers! I look forward to your reply.

[jmcilhinney]

I just used your code and it worked fine for me.

[kiwis]

What type of DB did you use and what field type with size???

[jmcilhinney]

MDB, OLE Object. There aren't any other column types that could be construed as applicable to image content.

[kiwis]

yeah thanks for the reply, I just got it working with that a moment ago, so to delete a picture how do you update with no object?

I mean this will not work but what does one do?

Try
           
            Dim connection As New Data.OleDb.OleDbConnection(My.Settings.Database1ConnectionString)
            Dim sqlqry As New OleDb.OleDbCommand("UPDATE Contacts SET Picture = "" WHERE ContactID = " & TextBox8.Text, connection)


            connection.Open()
            sqlqry.ExecuteNonQuery()
            connection.Close()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

[jmcilhinney]

To set any database field to null from VB code you assign DBNull.Value to the field or parameter. That means you'd use exactly the same SQL code as before but you'd assign DBNull.Value to the paarmeter instead of a Byte array.

[kiwis]

You're a legend Works fine thanks again.