Error 20005 in DataSaved event w/SQL Server

I am using the LEADOLEDB (v10) object to store a TIF image in an Image database column (column type is image).

The code is based on the example in the Help file and works fine with Access, but when I moved the database to SQL Server, the DataSaved event is getting a 20005 error.

My code looks like this:

Private Sub AdodcImages_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    
    On Error GoTo WillMoveError

    If (gbDataDirty) Then
        LEADOLEDBImages.Bitmap = 0
        If (LEAD1.Bitmap > 0) Then
            If LEAD1.BitmapBits = 1 Then
                'Compressed TIF
                LEADOLEDBImages.DataSaveBits = 1
                LEADOLEDBImages.DataSaveFormat = FILE_CCITT_GROUP4
                LEADOLEDBImages.DataSaveQuality = QFACTOR_QMS
            Else
                LEADOLEDBImages.DataSaveBits = 24
                LEADOLEDBImages.DataSaveFormat = FILE_LEAD
                LEADOLEDBImages.DataSaveQuality = QFACTOR_QMS
            End If
            'Prepare to write the image to the database column.
            LEADOLEDBImages.Bitmap = LEAD1.Bitmap
            LEADOLEDBImages.DataDirty = True
        End If
    End If
    Exit Sub

WillMoveError:
    ErrorMsgBox "Error in WillMove adding to Image Database: 0x" & Hex(err.Number) & " - " & err.Description

End Sub

Private Sub LEADOLEDBImages_DataLoaded(ByVal nStatus As Integer)

    If (nStatus = 0) Then
        LEAD1.RefBitmap = True 'don't make a copy
        LEAD1.Bitmap = LEADOLEDBImages.Bitmap 'send the image to the LEAD OCX
        LEAD1.RefBitmap = False 'reset
    ElseIf (nStatus = ERROR_FILENOTFOUND) Then 'if it's an empty record
    ElseIf (nStatus <> ERROR_FILENOTFOUND) Then 'if it's not an empty record
        LEAD1.Bitmap = 0
        If Not Adodc1.Recordset.EOF Then
            ErrorMsgBox "Error " & nStatus & ":" & err.Number & " loading image from database: " & err.Description
        End If
    End If
    gbDataDirty = False

End Sub

Private Sub LEADOLEDBImages_DataSaved(ByVal nStatus As Integer)
    
    LEADOLEDBImages.Bitmap = 0 'free the current reference
    If (nStatus <> 0) Then
        LEAD1.ForceRepaint
        ErrorMsgBox "Error " & nStatus & " saving to image database" & vbCrLf
    End If

End Sub

MAIN CODE TO CREATE THE DATABASE RECORD:

   AdodcImages.Recordset.AddNew

   ... set non-image database fields

   AdodcImages.Recordset.Update 'create the record.

    LEADOLEDBImages.Bitmap = 0
    LEAD1.Load TIFFileName, 0, 0, 1
     gbDataDirty = True
     AdodcImages.Recordset.Filter = 0 'forces the output of the image field by triggering WillMove.
---> THIS CAUSES A 20005 ERROR IN DataSaved with SQL Server.

What causes these 20005 errors from the OLEDB control?

If there was an underlying SQL database error, how can I access the real error code?

Thanks for any help you can offer.

 

If you are having issues using our binding control and SQL server and you want more control over what is going on you can save the image into a memory buffer and store that buffer in a blob field.  Attached is a tutorial that shows how to do this.  It was taken from the help file of LEADTOOLS 14.5, but it should be the same for v10.

Actually, I do not think V10 had the SaveArray() method. Bashar's example will work well for v11 and above.

Here is an example for V10 to use the LEADTOOLS SaveMemory() method, WinAPI and some undocumentated features of VB 5.

The information in this page applies to:

LEADTOOLS Version 10 or less
VB 5
ADO
DAO
database

Summary:

With LEADTOOLS you can save an image from the ActiveX control to memory. LEADTOOLS gives you a handle to this memory. With the Windows API you can convert this handle to variant byte array so that VB functions like AppendChunk can use them.

More Information:

1. Below is a DAO example. Note, you can convert it to ADO with the same results.

2. You need to add these declarations to a BAS file:

Declare Function GlobalLock Lib 'kernel32' (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib 'kernel32' (ByVal hMem As Long) As Long
Declare Function VarPtrArray Lib 'msvbvm50.dll' Alias 'VarPtr' (Ptr() As Any) As Long
Declare Sub CopyMemory Lib 'kernel32' Alias 'RtlMoveMemory' (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

2. Add the following to a button on a form with a DAO Datacontrol and LEAD control:

    Dim DestField As Field
    Dim Buffer() As Byte
    Dim BufferSize As Long
    Dim MemHandle As Long
    Dim myPointer As Long
   
    Set DestField = Data1.Recordset!photo

    LEAD1.Load 'c:\leadtools\ltwin10x\images\sample1.cmp', 0, 0, 1
    LEAD1.SaveMemory MemHandle, FILE_BMP, 1, 2, BufferSize
    Data1.Recordset.MoveLast
    Data1.Recordset.AddNew
   
    myPointer = GlobalLock(MemHandle)
    ReDim Buffer(BufferSize)
    CopyMemory Buffer(0), ByVal myPointer, BufferSize
 GlobalUnlock MemHandle
    GlobalFree MemHandle
   
    DestField.AppendChunk Buffer
    Data1.Recordset!who = 'imagetest2.cmp'
    Data1.Recordset.Update

   

Thanks - that worked much better - no more errors with SQL Server when storing the image.

I've found the LEADOLEDB control to be a little unpredictable, so this will be a better approach to storing images in a database.

(BTW: I removed the unused VarPtrArray declaration in your example and replaced it with a missing API declaration for GlobalFree.)