Saving picture in database(mssql)

[aishapot]

I need to save pictures in my database, my database is MSSQL
as of now, my code for getting the picture is this:

Private Sub browsepic_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles browsepic.Click
With dialogpic 'Open File Dialog sa toolbox
.InitialDirectory = "C:\Users\CAMILLE\Pictures"
.Filter = "JPG Files|*.jpg|PNG Files|*.png|GIF Files|*.gif"
.FilterIndex = 2
End With

If dialogpic.ShowDialog() = DialogResult.OK Then
With studentpic
.Image = Image.FromFile(dialogpic.FileName)
.SizeMode = PictureBoxSizeMode.StretchImage
.BorderStyle = BorderStyle.Fixed3D
End With
End If
End Sub

Can you help me on how to save the picture and how to retrieve it?

[catherine sea]

check out the following thread:
http://stackoverflow.com/questions/5033634/vb-net-insert-retreive-picture-from-mysql-database-directly-to-from-a-pictureb

[aishapot]

thank you, i'll try it now and hope this works.

:)

[aishapot]

I tried this code from the site you suggested.

Dim filename As String = txtName.Text + ".jpg"
Dim FileSize As UInt32

conn.Close()

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

FileSize = mstream.Length
Dim sqlcmd As New MySqlCommand
Dim sql As String
mstream.Close()

sql = "insert into [your table] (picture, filename, filesize)
VALUES(@File, @FileName, @FileSize)"

Try
conn.Open()
With sqlcmd
.CommandText = sql
.Connection = conn
.Parameters.AddWithValue("@FileName", filename)
.Parameters.AddWithValue("@FileSize", FileSize)
.Parameters.AddWithValue("@File", arrImage)

.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try

When i tried to save the pic the error said The parameter data type of UInt32 is invalid What do i do?

[GeekByChoiCe]

try: Dim FileSize As Integer

[aishapot]

Now it says String or binary data would be truncated. The statement has been terminated

[GeekByChoiCe]

Ok two questions:

1. sql = "insert into [your table] (picture, filename, filesize) VALUES (@File, @FileName, @FileSize)"
Is the table name really "your table"???

2. Could you please post the column definitions of your table?

[Netcode]

This would help you convert the image in your picture box to binary and save it in sql server.

'//declarations to handle face capture and its conversion to binary

   Dim myfilelocation As String = lblfilePath.Text
   Dim param As New SqlParameter("@ImageData", SqlDbType.VarBinary)
   Dim ImageData As Byte() = IO.File.ReadAllBytes(myfilelocation)
   param.Value = ImageData

  '//end declaration 


  Dim dc As SqlCommand = New SqlCommand

      With dc
      .Connection = con  'assuming 'con' is your connectionstring 
      .CommandText = "INSERT INTO TableName" & _
           "(ImageColumn)" + _
              "VALUES (@imagedata)"

                .Parameters.Add(param)
      End With

	con.Open()
        dc.ExecuteNonQuery()
        con.Close()

[aishapot]

Ok two questions:

1. sql = "insert into [your table] (picture, filename, filesize) VALUES (@File, @FileName, @FileSize)"
Is the table name really "your table"???

2. Could you please post the column definitions of your table?

no. i changed that into the table that i'm using. i'm just pasting the code that someone suggested

[aishapot]

This would help you convert the image in your picture box to binary and save it in sql server.

'//declarations to handle face capture and its conversion to binary

   Dim myfilelocation As String = lblfilePath.Text
   Dim param As New SqlParameter("@ImageData", SqlDbType.VarBinary)
   Dim ImageData As Byte() = IO.File.ReadAllBytes(myfilelocation)
   param.Value = ImageData

  '//end declaration 


  Dim dc As SqlCommand = New SqlCommand

      With dc
      .Connection = con  'assuming 'con' is your connectionstring 
      .CommandText = "INSERT INTO TableName" & _
           "(ImageColumn)" + _
              "VALUES (@imagedata)"

                .Parameters.Add(param)
      End With

	con.Open()
        dc.ExecuteNonQuery()
        con.Close()

ok i'll try it. thank you i hope it works

[Netcode]

It sure works, just make all necessary substitutions correctly (e.g. your connection-string, table-name, column-name)

Please give feedback

[aishapot]

where did lblfilePath.Text came from? I don't have that

[aishapot]

I tried the code from this site, i just edited some parts and now it's working :D

http://www.codeguru.com/vb/vb_internet/database/article.php/c7427

thank you for all the help!
now i'll the retrieve part. :)