title: Can I return a byte array from a SQL Server VarBinary column using a parameterized query?
body: <p>I wrote a small VBA procedure to test uploading and downloading of files as binary data into and out of a VarBinary column in SQL Server using ADO. The upload process appears to work, but I cannot get the download process to work.</p>
  
  <p>I believe the output parameter for VarBinary is setup incorrectly, but I cannot find any documentation on how to do it correctly.</p>
  
  <p>I get run-time error 3708 "Parameter object is improperly defined. Inconsistent or incomplete information was provided." at line <code>.Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamOutput)</code> </p>
  
  <p><strike><em>Update</em>: <code>SELECT ? = myblob FROM bin_table WHERE ID = ?;</code> appears to be returning a binary string, not a binary array. I believe this is where the problem lies, but I still don't know how to fix it.</strike></p>
  
  <p><em>Update</em>: I fixed the compile error "Type mismatch: array or user-defined type expected" by adding adding <code>.Value</code> to the end of the line <code>WriteFile "C:\some_new_file.pdf", .Parameters("@myblob")</code>.</p>
  
  <p>Any help is greatly appreciated. Thanks!</p>
  
  <pre><code>Private Sub TestReadWriteBlob()
  
      Dim objConnection As New ADODB.Connection
      Dim objCommand As New ADODB.Command
      Dim objRecordset As New ADODB.Recordset
      Dim intNewID As Integer
  
      With objConnection
          .CursorLocation = adUseClient
          .ConnectionString = "PROVIDER=SQLOLEDB;Server=&lt;server&gt;;Database=&lt;database&gt;;UID=&lt;uid&gt;;PWD=&lt;pwd&gt;;trusted_connection=false;"
          .Open
      End With
  
      With objCommand
          .ActiveConnection = objConnection
          .CommandText = "INSERT INTO bin_table ( myblob ) VALUES ( ? ); SELECT ? = id FROM bin_table WHERE ID = @@IDENTITY;"
          .CommandType = adCmdText
          .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamInput, -1, ReadFile("C:\some_file.pdf"))
          .Parameters.Append .CreateParameter("@NewID", adInteger, adParamOutput)
          .Execute
          intNewID = .Parameters("@NewID")
      End With
  
      Debug.Print intNewID
  
      Set objCommand = Nothing
      With objCommand
          .ActiveConnection = objConnection
          .CommandText = "SELECT ? = myblob FROM bin_table WHERE ID = ?;"
          .CommandType = adCmdText
          .Parameters.Append .CreateParameter("@myblob", adVarBinary, adParamOutput)
          .Parameters.Append .CreateParameter("@NewID", adInteger, adParamInput, , intNewID)
          .Execute
          WriteFile "C:\some_new_file.pdf", .Parameters("@myblob").Value
      End With
  
  End Sub
  
  Public Function ReadFile(ByVal strPath As String) As Byte()
  
      Dim intFile As Integer
  
      intFile = FreeFile
      Open strPath For Binary Access Read As intFile
      ReDim ReadFile(LOF(intFile) - 1)
      Get intFile, , ReadFile
      Close intFile
  
  End Function
  
  Public Sub WriteFile(ByVal strPath As String, bytBlob() As Byte, Optional ByVal Overwrite As Boolean = True)
  
      Dim intFile As Integer
  
      intFile = FreeFile
      If Overwrite And Dir(strPath) &lt;&gt; "" Then
          Kill strPath
      End If
      Open strPath For Binary Access Write As intFile
      Put intFile, , bytBlob
      Close intFile
  
  End Sub
  </code></pre>