locked
Retrieving Image from Sql-Server using vb.net RRS feed

  • Question

  • Hi?
    I am facing a problem when i am retrieving image from sql-server's database. I am using this code

    Dim cn As SqlConnection

            cn = New SqlConnection
            cn.ConnectionString = "Data Source=UMAR\UMAR;Initial Catalog=DMCHS;Integrated Security=True"

    Dim cmd As New System.Data.SqlClient.SqlCommand("select D1 from DBFile where mem_no=2")
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
            Dim ds As New DataSet()
            da.Fill(ds)

     Dim bits As Byte() = CType(ds.Tables(0).Rows(0).Item("D1"), Byte())
            Dim strfn As String = Convert.ToString(DateTime.Now.ToFileTime())
            Dim fs As New FileStream(strfn, FileMode.CreateNew, FileAccess.Write)
            fs.Write(bits, 0, bits.Length)
            fs.Flush()
            fs.Close()
            PictureBox1.Image = Image.FromFile(strfn.ToString)

     cmd.Connection.Close()
     
    but an error are displaying as:
    OutOfMemoryException was Unhandled
    Friday, February 19, 2010 9:30 PM

Answers

  • What type of field is the D1 in the db Image or varbinary?

    Also it looks like you are just getting one image you should
    use .ExecuteScalar instead of a DataAdaptor and a dataset.
    Unless you need to save the Image on the hard drive use a
    memory stream and send it straight to the picture box.

    It may be a rights issue trying to save the image to a file first.

    Although I cant test with out your db something like this should work.

            Dim cn As SqlConnection
    
            cn = New SqlConnection
            cn.ConnectionString = "Data Source=UMAR\UMAR;Initial Catalog=DMCHS;Integrated Security=True"
    
            Dim cmd As New System.Data.SqlClient.SqlCommand("select D1 from DBFile where mem_no=2")
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            
            Dim ImgStream As New IO.MemoryStream(CType(cmd.ExecuteScalar, Byte()))
    
            PictureBox1.Image = Image.FromStream(ImgStream)
    
            ImgStream.Dispose()
    
            cmd.Connection.Close()
    
    • Marked as answer by Jeff Shan Friday, February 26, 2010 2:04 AM
    Saturday, February 20, 2010 12:02 AM
  • Check this also, use varbinary instead image data type


    Arjun Paudel
    • Marked as answer by Jeff Shan Friday, February 26, 2010 2:05 AM
    Saturday, February 20, 2010 5:23 AM
  • Hi Umar,

    You can do the streaming also do in memory.

    I've made endless samples for that.

    One of those:

    http://www.vb-tips.com/DataSetImage.aspx
    Success
    Cor
    Success
    Cor
    • Marked as answer by Jeff Shan Friday, February 26, 2010 2:05 AM
    Monday, February 22, 2010 11:28 AM

All replies

  • What type of field is the D1 in the db Image or varbinary?

    Also it looks like you are just getting one image you should
    use .ExecuteScalar instead of a DataAdaptor and a dataset.
    Unless you need to save the Image on the hard drive use a
    memory stream and send it straight to the picture box.

    It may be a rights issue trying to save the image to a file first.

    Although I cant test with out your db something like this should work.

            Dim cn As SqlConnection
    
            cn = New SqlConnection
            cn.ConnectionString = "Data Source=UMAR\UMAR;Initial Catalog=DMCHS;Integrated Security=True"
    
            Dim cmd As New System.Data.SqlClient.SqlCommand("select D1 from DBFile where mem_no=2")
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            
            Dim ImgStream As New IO.MemoryStream(CType(cmd.ExecuteScalar, Byte()))
    
            PictureBox1.Image = Image.FromStream(ImgStream)
    
            ImgStream.Dispose()
    
            cmd.Connection.Close()
    
    • Marked as answer by Jeff Shan Friday, February 26, 2010 2:04 AM
    Saturday, February 20, 2010 12:02 AM
  • Check this also, use varbinary instead image data type


    Arjun Paudel
    • Marked as answer by Jeff Shan Friday, February 26, 2010 2:05 AM
    Saturday, February 20, 2010 5:23 AM
  • Arjun,

    Image is the same type.

    It has to be streamed like you see in your solution, and it can be done in memory.

    I've made endless samples for that.

    One of those:

    http://www.vb-tips.com/DataSetImage.aspx
    Success
    Cor
    I dont know when that happens but read this

    ntexttext, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Usenvarchar(max)varchar(max), and varbinary(max) instead.

     

    Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

    Arjun Paudel
    Saturday, February 20, 2010 8:44 AM
  • Hello
    You dont need all this things, just do it:
    Dim cn As SqlConnection
    cn = New SqlConnection
    cn.ConnectionString = "Data Source=UMAR\UMAR;Initial Catalog=DMCHS;Integrated Security=True;

    Dim cmd As New System.Data.SqlClient.SqlCommand(“select D1 from DBFile where mem_no=2”)

    cmd.Connection = cn
    cmd.CommandType = CommandType.Text
    Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)

    Dim ds As New DataTable()
    da.Fill(ds)

    Dim bindingsource as New BindingSource
    bindingsource.DataSource=ds.DefaultView
    Me.PictureBox1.DataBindings.Add(“Image”, bindingsource, “PictureMe”,True) 

    cmd.Connection.Close()

     

     


    Just Be Humble Malange!
    Saturday, February 20, 2010 2:07 PM
  • Hi

    How about the issue now? If you still have any doubt and concern about this issue, please let us know.

    Regards
    Jeff Shan


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, February 22, 2010 8:06 AM
  • Hi Umar,

    You can do the streaming also do in memory.

    I've made endless samples for that.

    One of those:

    http://www.vb-tips.com/DataSetImage.aspx
    Success
    Cor
    Success
    Cor
    • Marked as answer by Jeff Shan Friday, February 26, 2010 2:05 AM
    Monday, February 22, 2010 11:28 AM