Retrieving Binary data from SQL in VB6
If you have a column of type binary in SQL and return it using ADODB in VB6 even if the column contains only 1 byte of data it must be stored as a byte array.

So for example:


    Dim cm As ADODB.Command
    Dim rs As ADODB.Recordset

    Set cm = New ADODB.Command
    Set rs = New ADODB.Recordset

    With cm
        .ActiveConnection = cn
        .CommandTimeout = 30
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM Table WHERE RowID = ?"
        .Parameters.Append .CreateParameter("TableID", adInteger, adParamInput, , TableID)
    End With
    
    RecordsetOpen cn, rs, cm, , adOpenForwardOnly, adLockReadOnly

    With rs
        If .State = adStateOpen Then
            If .RecordCount > 0 Then
                Dim tempArray() As Byte
                tempArray = .Fields("BinaryColumn")
                ''Success! Returns array containing 1 Byte in it

                Dim value as Byte
                value = .Fields("BinaryColumn")
                ''Fails! Run-Time error '13' Type Mismatch
            End If
        End If
    End With

Allot of people would look at that and say "So what? Its failing with a type mismatch because you have a Byte() and are trying to set a Byte!".

My argument is Fields.Value is a property of Type Variant and given VB6s liberal policies on typecasting I would have figured something like this would work.

Could someone explain why this fails?

    
Retrieving Binary data from SQL in VB6
===

If you have a column of type binary in SQL and return it using ADODB in VB6 even if the column contains only 1 byte of data it must be stored as a byte array.

So for example:


    Dim cm As ADODB.Command
    Dim rs As ADODB.Recordset

    Set cm = New ADODB.Command
    Set rs = New ADODB.Recordset

    With cm
        .ActiveConnection = cn
        .CommandTimeout = 30
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM Table WHERE RowID = ?"
        .Parameters.Append .CreateParameter("TableID", adInteger, adParamInput, , TableID)
    End With
    
    RecordsetOpen cn, rs, cm, , adOpenForwardOnly, adLockReadOnly

    With rs
        If .State = adStateOpen Then
            If .RecordCount > 0 Then
                Dim tempArray() As Byte
                tempArray = .Fields("BinaryColumn")
                ''Success! Returns array containing 1 Byte in it

                Dim value as Byte
                value = .Fields("BinaryColumn")
                ''Fails! Run-Time error '13' Type Mismatch
            End If
        End If
    End With

Allot of people would look at that and say "So what? Its failing with a type mismatch because you have a Byte() and are trying to set a Byte!".

My argument is Fields.Value is a property of Type Variant and given VB6s liberal policies on typecasting I would have figured something like this would work.

Could someone explain why this fails?

    