Bit SQL parameter - ADO Fail

[lameid]

I do not know what nuance I am missing but the way I am trying to add a parameter for a bit is just not working for me and I am not having luck finding a working sample.
Thoughts?

UpdateReturn "Sample1", 133, "Sample2", True, Date() 'Immediate window sample call, 
[indent][/indent][indent][/indent][indent][/indent][indent][/indent]'I did change the string values through out

Public Sub UpdateReturn(strProject As String, lngProjectUID As Long, strRecipientType As String, blReturned As Boolean, dtReturn As Date)
  Dim conn As ADODB.Connection
  Dim strConn As String
  Dim rstSQL As ADODB.Recordset

  Dim cmd As New ADODB.Command
 
  
  strConn = "Provider=SQLNCLI11;" & _
    "Server=MyServer\MyInstance; Database=MYDB;" & _
    "Trusted_Connection=yes"

  Set conn = New ADODB.Connection
  conn.Open strConn

 
  Set cmd = New ADODB.Command
  cmd.CommandText = "dbo.spMessageReturned"
  cmd.CommandType = adCmdStoredProc
  cmd.activeConnection = conn
  
  Call AddCommandParam(cmd, "Project", adVarChar, strProject)
  Call AddCommandParam(cmd, "ProjectUID", adInteger, lngProjectUID)
  Call AddCommandParam(cmd, "RecipientType", adVarChar, strRecipientType)
  [red]Call AddCommandParam(cmd, "Returned", adBinary, blReturned) 'This is the call giving me problems[/red]
  Call AddCommandParam(cmd, "Return_Date", adDate, dtReturn)
  
  'Execute the Stored Procedure
  cmd.Execute
  conn.Close
  
End Sub

Public Sub AddCommandParam(ByRef cmd As ADODB.Command, ByRef Param As String, ByRef DataType As Long, ByRef varValue As Variant)
  'Adds a parameter to a command object
  'This is just a wrapper function to simplify adding parameters vs. using multiple lines of code for each parameter
  
  Dim prm As ADODB.Parameter
 
  Select Case DataType
  Case adBigInt
    Set prm = cmd.CreateParameter(Param, DataType, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters(Param).Value = CLng(varValue)
  Case adBoolean, adBinary
    Set prm = cmd.CreateParameter(Param, DataType, adParamInput)
    [red]cmd.Parameters.Append prm 'Errors here, Error 3708, 
                              'Parameter object is improperly define. Inconsistent or incomplete information was provided[/red]
    cmd.Parameters(Param).Value = CBool(varValue)
  Case adChar, adVarChar, adLongVarChar, adVarWChar, adWChar
    Set prm = cmd.CreateParameter(Param, DataType, adParamInput, Len(varValue))
    cmd.Parameters.Append prm     cmd.Parameters(Param).Value = CStr(varValue)
  Case adDate
    Set prm = cmd.CreateParameter(Param, DataType, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters(Param).Value = CDate(varValue)
  Case adDecimal, adDouble
    Set prm = cmd.CreateParameter(Param, DataType, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters(Param).Value = CDbl(varValue)
  Case Else
    'cmd.Parameters(Param).Value = varValue
  End Select

The declaration top from my SQL procedure...

CREATE PROCEDURE [dbo].[spMessageReturned] (@Project varchar(20), @ProjectUID int, @RecipientType Varchar(20), @Returned bit, @Return_Date Date)

Example of running SQL directly that works ...

Exec [dbo].[spMessageReturned] 'Sample1', 133, 'Sample2', 1, '3/2/2018'
Go

 

[lameid]

I did try switching to adBoolean from adBinary with reference to "Microsoft ActiveX Data Objects 2.8 Library" from the similarly named 6.1 version...

I get a different error on cmd.execute... "Error Converting data type varchar to int."

 

[MajP]

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

Your values do not seem correct. The third argument is direction and you appear to be providing a value. I think you need 2 commas.

 

[lameid]

Size is optional when the datatype has a firm size and value is optional but you probably had better set it before executing the command.

I did find it. Apparently whenever I wrote it, I left out the adInteger datatype possibility which I guess lead to a missing parameter and unexpected error message...

Select Case DataType
  Case adBigInt[red], adInteger[/red]