Inserting images into a SQL Compact from VB(A) does not appear to be possible using vanilla VBA code (please correct me if I am wrong). In order to work around this limitation, it is possible to create a function in C#, and call this function from VBA. This excellent post got me going. This is the VBA Code:
Sub InsertBlob()
Dim IMG_FILE As String
Dim intFile As Integer
Dim ImgBuff() As Byte
Dim ImgLen As Long
Dim ret As String
IMG_FILE = "C:\ErikEJ.jpg"
'Read/Store GIF file in ByteArray
intFile = FreeFile
Open IMG_FILE For Binary As #intFile
ImgLen = LOF(intFile)
ReDim ImgBuff(ImgLen) As Byte
Get #intFile, , ImgBuff()
Close #intFile
Dim sqlCeUtil As New SqlCompactUtility
ret = sqlCeUtil.SaveBlob(ImgBuff(), "Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture) VALUES (@Blob)", "@Blob")
End Sub
First a Byte array with the image contents is created. Then the .NET based method (SaveBlob) is invoked.
The SaveBlob method takes 4 parameters:
1. The byte array
2. The SQL Compact connection string
3. The Insert statement
4. The parameter name
In order for this to work, add a reference to the .NET dll library from the VB(A) project, the code of which is shown below (created per the instructions in the "A Basic Walk Through" part of the post mentioned above). If you have followed the instruction in the blog, the DLL should appear in the Add Reference dialog once built.
using System;
using System.Data.SqlServerCe;
using System.Runtime.InteropServices;
namespace SqlCompactInterop
{
public class SqlCompactUtility
{
//SaveBlob(data, @"Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture, Id) VALUES (@Blob, 2)", "@Blob");
public string SaveBlob(byte[] data, string connectionString, string insertStatement, string parameterName)
{
try
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
SqlCeCommand cmd = new SqlCeCommand(insertStatement);
cmd.Parameters.Add(new SqlCeParameter(parameterName, System.Data.SqlDbType.Image, data.Length));
cmd.Parameters[parameterName].Value = data;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
return string.Empty;
}
}
catch (SqlCeException e)
{
return ShowErrors(e);
}
}
// Error handling routine that generates an error message
private string ShowErrors(SqlCeException e)
{
SqlCeErrorCollection errorCollection = e.Errors;
System.Text.StringBuilder bld = new System.Text.StringBuilder();
Exception inner = e.InnerException;
if (null != inner)
{
bld.Append(inner.ToString());
}
// Enumerate the errors to a message box.
foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);
// Enumerate each numeric parameter for the error.
foreach (int numPar in err.NumericErrorParameters)
{
if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
}
// Enumerate each string parameter for the error.
foreach (string errPar in err.ErrorParameters)
{
if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
}
}
return bld.ToString();
}
}
}
"Hope this helps"
