Reading image from MS-Access database in Classic ASP

Question

I am trying to read JPG images from MS-Access database using the following code in classic ASP:

Response.Expires = 0
Response.Buffer = TRUE
Response.Clear

Response.ContentType = "image/jpg"

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open  "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/database/database.mdb")    

sqlString = "Select * from tblBusinessImages where fldID = " & request.querystring("id")
Set rs = cn.Execute(sqlString)
Response.BinaryWrite rs("fldImageData")
Response.End

But I keep getting an error telling that the browser can't read or display the image.

The database field 'tblBusinessImages' is an OLE field, and the image is saved into it by copy-paste, only for testing purpose at this time (could this be a wrong way?)

Now I know that MS-Access saves extra data in the BLOB object (as MSDN says here:

If any extraneous information is contained in the BLOB data, this will be passed by this script, and the image will not display properly. This becomes important when you realize that most methods of placing images into BLOB fields place extra information in the form of headers with the image. Examples of this are Microsoft Access and Microsoft Visual FoxPro. Both of these applications save OLE headers in the BLOB field along with the actual binary data.

)

My question is how do I read the RAW image data from a BLOB without the extra data/headers that MS-Access saves?

Thanks.

Ron Evans Ron Evans · Accepted Answer · 2015-02-14T15:26:36

After a day of work I realized what the problem was: The problem was in the way the picture was saved to the database (manually).

In order to save images to database, the following code should be used:

Dim fileName
Dim conn
Dim rsTemp
Dim fldID
Dim sSQL
Dim mystream

Set mystream = Server.CreateObject("ADODB.Stream")
mystream.Type = 1
mystream.Open
mystream.LoadFromFile  "D:\Desktop\My Downloads\compose1.jpg"

Set conn = Server.CreateObject("ADODB.Connection")
Set rsTemp = Server.CreateObject("ADODB.Recordset")

conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("/database/database.mdb")
sSQL = "Select fldImageData from tblBusinessImages where fldID = 1;"

rsTemp.Open sSQL, conn, 3, 3

rsTemp.Fields("fldImageData").AppendChunk mystream.Read

rsTemp.Update
rsTemp.Close
set mystream = nothing

And in order to read an image from MS-Access database, this code should be used:

Dim conn
Dim rsTemp
Dim sSQL
Dim fldID

fldID = Request.QueryString("id")

If Not fldID = "" And IsNumeric(fldID) Then

    Set conn = Server.CreateObject("ADODB.Connection")
    Set rsTemp = Server.CreateObject("ADODB.Recordset")

    conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("/database/database.mdb")

    sSQL = "Select * from tblBusinessImages where fldID = " & request.querystring("id")

    rsTemp.Open sSQL, conn, 3, 3

    If Not rsTemp.EOF Then
        Response.ContentType = "image/jpeg"
        Response.BinaryWrite rsTemp("fldImageData")
    Else
        Response.Write("File could not be found")
    End If

    rsTemp.Close
    conn.Close

    Set rsTemp = Nothing
    Set conn = Nothing
Else
    Response.Write("File could not be found")
End If

This way the image data will be saved as Long Binary Data in the OLE field in the database. When read, it will be posted to the browser as a readable image data.

Reading image from MS-Access database in Classic ASP

1 Answers