Solved

MSSQL - VB

Posted on 2000-03-08
5
541 Views
Last Modified: 2012-05-04
I am using MSSQL to store an image/tiff file, under the image datatype. I want to retrieve the image file from the database for viewing through VB. The codes below is to retrieve the image file, which is rather correct if I am not mistaken.

ChunkSize = objRS("Fax").ActualSize
ImageFile = (objRS("Fax").GetChunk(ChunkSize))

The problem is if the actual image size is 10000 bytes, all I get to retrieve is 5000 bytes, 50% of the actual file size. I tried it using different file formats but it is still the same. When I edited the retrieved image file's contents... I found garbled characters different from the actual image file's contents. Anybody know the answer to this and the way to solve it. Please any ideas... I tried MS Knowledgebase and Technet but I cant find the solution.
0
Comment
Question by:LawrenceY
  • 3
5 Comments
 
LVL 4

Expert Comment

by:wqw
ID: 2595661
the error is in

ImageFile = (objRS("Fax").GetChunk(ChunkSize))

should be

ImageFile = objRS("Fax").GetChunk(ChunkSize)

and ImageFile must be

Dim ImageFile() As Byte

The parentheses convert the byte array to string which is the intrinsic way in VB to convert from Unicode to Ascii. so you get 1 byte for every 2 bytes :-))

Let me know if this works for you.

HTH,

</wqw>
0
 

Author Comment

by:LawrenceY
ID: 2599071
The codes cant work. Below is my actual program codes.

objRS.MoveFirst (my database)
    For i = 1 To numrecords (total records)
        ChunkSize = objRS("Image").ActualSize
        FaxMailData = objRS("Image").GetChunk(ChunkSize)
        Image2 = Image        ChunkSize2 = Len(Image2)
        MsgBox "ActualSize=" & ChunkSize & " | " & "ConvertedSize=" & ChunkSize2    'Debug
        MsgBox FaxMailData()    'Debug
        objRS.MoveNext
    Next i

Find anything wrong with it? Please anybody tell me. Thanks.    
0
 
LVL 4

Expert Comment

by:wqw
ID: 2599612
give me the Dim's. each variable declaration

</wqw>
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2599794
You should use getchunk in a loop to retrieve the data in pieces rather than by trying to get the whole lot. There are restrictions on the number of bytes that can be extracted from a field in one go, this is defaulted to 5K unless you use settextsize etc which can prove a real pain!

This example gets the data in 1K blocks from the field.

   conChunkSize = 1000
   lngLogoSize = rstPubInfo!logo.ActualSize
   Do While lngOffset < lngLogoSize
      varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
      varLogo = varLogo & varChunk
      lngOffset = lngOffset + conChunkSize
   Loop
   
0
 
LVL 4

Accepted Solution

by:
wqw earned 150 total points
ID: 2656720
sorry for the late call. check this code.

i created a table first in northwind db with this sql:

create table images(id int identity, blob image)

you need a form in VB with 2 command buttons, 2 picture boxes and a common dialog control (ctr+t - components dialog). leave default names. reference ado 2.1 too. paste this code in your form

Option Explicit

Private Const CONN_STR = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind"

Private Sub Command1_Click()
    Dim rs As New Recordset, oPic As StdPicture, oBag As New PropertyBag
    rs.CursorLocation = adUseClient
    rs.Open "images", CONN_STR, adOpenKeyset, adLockOptimistic
    CommonDialog1.Filter = "Graphics (*.bmp;*.gif;*.jpg)|*.bmp;*.gif;*.jpg|All files (*.*)|*.*"
    CommonDialog1.ShowOpen
    If CommonDialog1.FileName <> "" Then
        Set oPic = LoadPicture(CommonDialog1.FileName)
        oBag.WriteProperty "FileName", CommonDialog1.FileName
        oBag.WriteProperty "Image", oPic
        rs.AddNew
        rs!Blob = oBag.Contents
        rs.Update
        Set Picture1.Picture = oPic
    End If
End Sub

Private Sub Command2_Click()
    Dim rs As New Recordset, oBag As New PropertyBag
    rs.CursorLocation = adUseClient
    rs.Open "images", CONN_STR, adOpenKeyset, adLockOptimistic
    If Not rs.EOF Then
        rs.MoveLast
        oBag.Contents = rs!Blob.Value
        Set Picture2.Picture = oBag.ReadProperty("Image")
    End If
End Sub

basicly this code save images to image field. unfortunately uncompressed. may i suppose it's datatype problem that you have -- binary field instead of image. binary field can take up to 8060 bytes of data.

let me know if this is working for you.

HTH,

</wqw>
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL export CSV & schedule It 9 44
c# code 19 61
Convert char to decimal in a SQL Server View 14 23
Caste datetime 2 22
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now