Retrieving picture from database

Question

aishapot -3 Junior Poster in Training

12 Years Ago

Hello again! So now i can save the picture in the database, now i have to retrieve it.
this is my code for the saving of the picture,

Dim ms As New MemoryStream()
        studentpic.Image.Save(ms, studentpic.Image.RawFormat)

        Dim arrImage() As Byte = ms.GetBuffer
        ms.Close()

        Dim strFilename As String =   lblfilePath.Text.Substring(lblfilePath.Text.LastIndexOf("\"))
        Dim cnn As New SqlConnection(connectionString:="data source=.\sqlexpress; integrated security=true; attachdbfilename=|datadirectory|\WAIS.mdf; user instance=true;")

        Dim strSQL As String = "INSERT INTO ImagesStore (OriginalPath, ImageData)" & "VALUES (@OriginalPath, @ImageData)"
        Dim cmd As New SqlCommand(strSQL, cnn)

        With cmd

            .Parameters.Add(New SqlParameter("@OriginalPath", SqlDbType.NVarChar, 50)).Value = strFilename
            .Parameters.Add(New SqlParameter("@ImageData", SqlDbType.Image)).Value = arrImage

        End With

        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()

i saw on the net that i have to the reverse of the pic, i have to convert byte into image. can you please help me. thank you

mssql vb.net visual-basic

4 Contributors
10 Replies
3K Views
3 Weeks Discussion Span
Latest Post 12 Years Ago Latest Post by prathapsv3

M.Waqas Aslam 67 Posting Pro in Training

12 Years Ago

hello !aishapot
i think you should take multiple picture boxes, and then databind them with your data source , may b your code look like this

dim mydataset as new dataset
dim myconn as new sqlconnection(___your connection string)
dim da as new sqldataadapter(your query,mycon)
mydataset.table("MyImages").clear
da.fill(mydataset,"myimages")
bindingsource1.datasource = mydataset("myimages")
picturebox1.databing.add("Image",bindingsource1,"_dbfieldname",true)
.
.
.
.
mycon.close()

and so on ,
hope this will help u , and if u dont knw the no of students and you want to perform all the dynamically then i think you have to create multiple picture boxes using any loop , for example

dim i as int64
for i = 0 to bindingsource -1 
  Me.PictureBox(i) = New System.Windows.Forms.PictureBox
next

Best Regards

M.Waqas Aslam

Edited 12 Years Ago by M.Waqas Aslam because: spelling mistake

sirasingh 0 Newbie Poster

12 Years Ago

Hi Aishapot try this code

Try
        Dim cn As New OleDb.OleDbConnection
        Dim cmd As OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Ram Ayodhya Singh\My Documents\Picture.mdb"
        cn.Open()
        cmd = cn.CreateCommand()
        cmd.CommandText = "select * from picroll where sno = '" & sEmpID & "'"
        dr = cmd.ExecuteReader
        If dr.Read Then
            Dim bytImage() As Byte
            Try
                bytImage = CType(dr(2), Byte())
                 Dim ms As New System.IO.MemoryStream(bytImage)
                 Dim bmImage As New Bitmap(ms)
                 ms.Close()
                 PbNewImage.Image = bmImage
                 PbNewImage.Refresh()
             Catch ex As Exception
                 MsgBox(ex.ToString)
            End Try
         End If
          dr.Close()
         cn.Close()
          cmd.Dispose()
         cn.Dispose()
     Catch ex As Exception

        MsgBox(ex.ToString)

    End Try
end sub

and for detail kindly visit http://www.ram-a-singh.blogspot.com/2011/10/how-to-retrieve-photo-from-ms-access.html

Edited 12 Years Ago by pritaeas because: Added code tags.

prathapsv3 0 Newbie Poster

12 Years Ago

Solved

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
 
 Public Class Form1
    Dim str As String = "Data Source=NET3\SQLEXPRESS;Initial Catalog=RestPos;Persist Security Info=True;User ID=sa;Password=password"
    Dim con As New SqlClient.SqlConnection

    'To open an image from computer
    '-------------------------------
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        OpenFileDialog1.Title = "Please select a file"
        OpenFileDialog1.InitialDirectory = "c:temp"
        OpenFileDialog1.ShowDialog()
        TextBox2.Text = OpenFileDialog1.FileName.ToString  '--->To Show the file path in textbox2
        PictureBox1.ImageLocation = TextBox2.Text  '--->To show selected image in picturebox
    End sub
   
   'To insert selected image into database
   'The datatype of the column in table to store image should be <image>  
   '--------------------------------------------------------------------
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        con.ConnectionString = str
        Dim ms As New IO.MemoryStream()
        PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
        Dim arrimage() As Byte = ms.GetBuffer
        Dim cmd As New SqlCommand("insert into image (Emp_Image)values(@picture)", con)
        cmd.Parameters.Add(New SqlParameter("@Picture", SqlDbType.Image)).Value = arrimage
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
    
    'We have successfully inserted the image into database.
    'Now we want to Retrieve the image from database.
    '-------------------------------------------------
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim stream As New IO.MemoryStream()
        con.Open()
        Dim command As New SqlCommand("select Emp_Image from Image where Emp_Id='" +  TextBox3. Text + "'", con)  '--->You can give Emp_id instead of Textbox value.
        Dim image As Byte() = DirectCast(command.ExecuteScalar(), Byte())
        stream.Write(image, 0, image.Length)
        con.Close()
        Dim bitmap As New Bitmap(stream)
        PictureBox2.Image = bitmap '--->I have used another picturebox to display image from database.
    End Sub
    'Thats all, You can place a linklabel below the picturebox if you to change photo and update it in database.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

aishapot -3 Junior Poster in Training · Answer 1 · 2011-12-14T22:49:00+00:00

I have the code for retrieving, but my problem is that, it's only retrieving one pic.
i have multiple pictures for multiple students. I have a Listview wherein the students is shown, and when you click on a student a new form will show where the information of the students is also shown. what i need to do is that, when i click a student their picture should also be displayed together with the information not just the first picture in the database. - was that clear? just tell me if it's not. :)

here's the code

Sub retrievepic()

        Dim conn As New SqlConnection("data source=.\sqlexpress; integrated security=true; attachdbfilename=|datadirectory|\WAIS.mdf; user instance=true;")
        Dim cmd As New SqlCommand("select ImageData from ImagesStore where ImageId=@ImageId", conn)

        cmd.Parameters.AddWithValue("@ImageId", 3)

        Try

            conn.Open()
            PictureBox1.Image = Image.FromStream(New IO.MemoryStream(CType(cmd.ExecuteScalar, Byte())))

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()

        End Try

    End Sub

aishapot -3 Junior Poster in Training · Answer 2 · 2011-12-19T10:02:49+00:00

aishapot -3 Junior Poster in Training

12 Years Ago

anyone?

aishapot -3 Junior Poster in Training · Answer 3 · 2012-01-08T01:52:02+00:00

I've been so busy, I almost forgot about this post.
I'm going to try this NOW! I hope it will work! I'll let you know immediately!

aishapot -3 Junior Poster in Training · Answer 4 · 2012-01-08T23:59:10+00:00

I tried saving the Field Image in the same table with the student info, it's saving but i've noticed that it saves two times, one it saves the picture without the image, the surname and other info is null, and then the second save would be without the picture.

what is the better solution? should i separate the image field with the student info?
i really need your brilliant minds. we only have til february to finish this. thank you

aishapot -3 Junior Poster in Training · Answer 5 · 2012-01-09T00:34:51+00:00

Solved

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
 
 Public Class Form1
    Dim str As String = "Data Source=NET3\SQLEXPRESS;Initial Catalog=RestPos;Persist Security Info=True;User ID=sa;Password=password"
    Dim con As New SqlClient.SqlConnection

    'To open an image from computer
    '-------------------------------
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        OpenFileDialog1.Title = "Please select a file"
        OpenFileDialog1.InitialDirectory = "c:temp"
        OpenFileDialog1.ShowDialog()
        TextBox2.Text = OpenFileDialog1.FileName.ToString  '--->To Show the file path in textbox2
        PictureBox1.ImageLocation = TextBox2.Text  '--->To show selected image in picturebox
    End sub
   
   'To insert selected image into database
   'The datatype of the column in table to store image should be <image>  
   '--------------------------------------------------------------------
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        con.ConnectionString = str
        Dim ms As New IO.MemoryStream()
        PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
        Dim arrimage() As Byte = ms.GetBuffer
        Dim cmd As New SqlCommand("insert into image (Emp_Image)values(@picture)", con)
        cmd.Parameters.Add(New SqlParameter("@Picture", SqlDbType.Image)).Value = arrimage
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
    
    'We have successfully inserted the image into database.
    'Now we want to Retrieve the image from database.
    '-------------------------------------------------
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim stream As New IO.MemoryStream()
        con.Open()
        Dim command As New SqlCommand("select Emp_Image from Image where Emp_Id='" +  TextBox3. Text + "'", con)  '--->You can give Emp_id instead of Textbox value.
        Dim image As Byte() = DirectCast(command.ExecuteScalar(), Byte())
        stream.Write(image, 0, image.Length)
        con.Close()
        Dim bitmap As New Bitmap(stream)
        PictureBox2.Image = bitmap '--->I have used another picturebox to display image from database.
    End Sub
    'Thats all, You can place a linklabel below the picturebox if you to change photo and update it in database.

There was an error under con.Open() it says The ConnectionString property has not been initialized.

aishapot -3 Junior Poster in Training · Answer 6 · 2012-01-09T00:55:35+00:00

Nevermind, it's working properly now! :) I just removed the con.open & con.close under the retrieve code. Thank You so much prathapsv3!

prathapsv3 0 Newbie Poster · Answer 7 · 2012-01-09T09:52:32+00:00

prathapsv3 0 Newbie Poster

12 Years Ago

You are Welcome!!