×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Bit SQL parameter - ADO Fail

Bit SQL parameter - ADO Fail

Bit SQL parameter - ADO Fail

(OP)
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?

CODE

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

CODE

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)
  Call AddCommandParam(cmd, "Returned", adBinary, blReturned) 'This is the call giving me problems
  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)
    cmd.Parameters.Append prm 'Errors here, Error 3708, 
                              'Parameter object is improperly define. Inconsistent or incomplete information was provided
    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...

CODE

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

Example of running SQL directly that works ...

CODE

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

RE: Bit SQL parameter - ADO Fail

(OP)
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."

RE: Bit SQL parameter - ADO Fail


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.

RE: Bit SQL parameter - ADO Fail

(OP)
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...

CODE

Select Case DataType
  Case adBigInt, adInteger 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close