BUG: Parameterized Insert of LongVarBinary Data Type Can Return "Type Is Invalid" Error (264242)

SYMPTOMS

When you attempt a parameterized insert of an SQL_LONGVARBINARY (adLongVarBinary) data type with the ActiveX Data Objects (ADO) command object, the following error message may be returned:

Type is Invalid

This problem occurs with the Microsoft Visual FoxPro driver, or with any of the Microsoft Jet Indexed Sequential Access Method (ISAM) drivers.

RESOLUTION

In the case of the Jet ISAM drivers, you can resolve the problem by using the native Microsoft Jet 4.0 OLE DB Provider, which does support the SQL_BINARY type. There is no known workaround for the Visual FoxPro driver.

MORE INFORMATION

Steps to Reproduce Behavior

Create a data source name (DSN) named "dBaseFiles" that points to any folder, and specify "dBase 5.0" as the type. Run the following code to create a table and attempt an insert:

Private Sub Form_Load()
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim param As ADODB.Parameter
   
    cn.Open "DSN=dBaseFiles"
    'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data    'Source=C:\dBase;Extended Properties=dBase 5.0;Persist Security Info=False"

    cn.Execute "CREATE TABLE test (col1 oleobject)"

    cmd.ActiveConnection = cn
    cmd.CommandText = "insert into test values (?)"
    Dim arr(10) As Byte
    arr(0) = 1
    arr(1) = 2
    Set param = cmd.CreateParameter(, adLongVarBinary, adParamInput, UBound(arr) + 1, arr)
    cmd.Parameters.Append param
    cmd.Execute
    cn.Close
    Stop
    
    End
  
End Sub

To correct the problem, comment the line

cn.Open "DSN=dBaseFiles"

and uncomment the line that follows it.