Microsoft KB Archive/318639

= HOW TO: Store and Retrieve an Image File with SQL Server CE and eVB =

Article ID: 318639

Article Last Modified on 1/3/2007

-

APPLIES TO


 * Microsoft Encarta Interactive World Atlas 2001
 * Microsoft SQL Server 2000 Windows CE Edition 1.1
 * Microsoft SQL Server 2000 Windows CE Edition 1.1 Service Pack 1

-



This article was previously published under Q318639



IN THIS TASK
SUMMARY
 * How to Store a JPG file to a SQL Server CE Database
 * How to Retrieve a JPG File from a SQL Server CE Database
 * How to Add References to the Project



SUMMARY
This step-by-step article describes how to store an image file to a Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) database and how to retrieve the stored image from the database and reconstruct the image file. This article uses Microsoft Embedded Visual Basic as the development platform.

The sections that follow describe how to create an Embedded Visual Basic application that uses two command buttons. One command button stores a .jpg file to a SQL Server CE database. The second command button is for reconstructing the .jpg file from the image data stored in the SQL Server CE database.

back to the top

How to Store a JPG file to a SQL Server CE Database
 Copy the MS.jpg file from the Program Files\Common Files\Microsoft Shared\Grphflt folder on your desktop computer to the CE device. If you cannot find this file, use any other .jpg file and be sure to replace the references in the code segment of the example with the appropriate name. The following code segment describes how to store a .jpg file to a SQL Server CE database.

NOTE: The code segment assumes you have a database named Image.sdf created on the CE device.  Create a new Microsoft eMbedded Visual Basic application.  Add a command button to the default form, and then paste the following code in for the command: Private Sub Command1_Click Dim cn     Dim rs      Dim pixfld

Const adcmdtabledirect = 512 Const adlockoptimistic = 3 Const adopenkeyset = 1

Dim sfiledata Dim picfile As File Set picfile = CreateObject(&quot;FileCtl.File&quot;) picfile.Open &quot;\MS.JPG&quot;, fsModeBinary sfiledata = picfile.Input(picfile.LOF) Set cn = CreateObject(&quot;ADOCE.Connection.3.1&quot;) Set rs = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) cn.ConnectionString = &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\image.sdf&quot; cn.Open ' Comment the following line the first time you run this sample. cn.Execute &quot;drop table JpegTest&quot; cn.Execute &quot;create table JpegTest (ID int, Pix Image)&quot; rs.Open &quot;jpegTest&quot;, cn, adopenkeyset, adlockoptimistic, adcmdtabledirect Set pixfld = rs(&quot;pix&quot;) rs.AddNew rs.Fields(&quot;id&quot;) = 1 pixfld.AppendChunk sfiledata picfile.Close

rs.Update rs.Close cn.Close Set rs = Nothing Set cn = Nothing MsgBox &quot;success storing image to database&quot; End Sub

 

back to the top

How to Retrieve a JPG File from a SQL Server CE Database
The following code segment describes how to reconstruct a .jpg file from the image data stored in SQL Server CE.

NOTE: The code assumes that the image stored in the CE database is of type .jpg. You must know the type of the image (.jpg, .gif, .bmp, and so forth) to successfully reconstruct the file by using the following code sample. The code also assumes that the name and path of the reconstructed .jpg file is \MSRetrieved.jpg.

Add another command button to the default form, and then paste the following code: Private Sub Command2_Click Dim cn     Dim rs      Const adcmdtabledirect = 512 Const adlockoptimistic = 3 Const adopenkeyset = 1 Dim picfile As File Set picfile = CreateObject(&quot;FileCtl.File&quot;) picfile.Open &quot;\MSRetrieved.JPG&quot;, fsModeBinary, fsAccessWrite Set cn = CreateObject(&quot;ADOCE.Connection.3.1&quot;) Set rs = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) cn.ConnectionString = &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\image.sdf&quot; cn.Open rs.Open &quot;JpegTest&quot;, cn, adopenkeyset, adlockoptimistic, adcmdtabledirect ' You may have to adjust the size passed to GetChunk. picfile.LinePrint rs.Fields(&quot;Pix&quot;).GetChunk(10000) picfile.Close rs.Close cn.Close Set rs = Nothing Set cn = Nothing MsgBox &quot;success loading image to file&quot; End Sub back to the top

How to Add References to the Project
To add references to the project, from the Project menu, click References. Click to select references to:


 * Microsoft CE ADO Control 3.1


 * Microsoft CE ADO ext. 3.1 for DDL


 * Microsoft CE File System Control 3.0


 * Microsoft CE SQL Server Control 1.0

back to the top

Keywords: kbgraphxlink kbhowto kbhowtomaster KB318639

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.