Error '3708' Calling SQL Server SPROC from Access2013

Calling SQL Server SPROC From and Access 2013 Front End.

I get error '3708' Parameter Object improperly defined.  Inconsistent of incomplete information provided.

The error is on statement
adCmd.Execute Options:=adExecuteNoRecords   in the calling routine

The values of the parameters in order are:
1522578
"30 Day Conversion"
761
1
1
"LegaCvt"
10/9/2015

Everything looks good and I have run this routine before.  Can anyone spot the issue?

SPROC:
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[sptblCommentsAdd]    Script Date: 12/25/2017 10:17:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sptblCommentsAdd] 
	@PropertyID             int,
    @Comment				nvarchar(MAX) = '', 
	@TaxAuthorityID			int = 0,
	@TaxTypeID		    	int = 0,
    @CommentTypeID			int = 0, 
    @UserAdded				nvarchar(100) = '',
    @DateAdded				datetime = null
As

Begin

Set NOCOUNT on;

SET @DateAdded = COALESCE(@DateAdded, GETDATE())

Insert dbo.tblComments (
    PropertyRecID, 
	TaxAuthorityID,
	TaxTypeID, 
	CommentTypeID,
	Comment,
	DateAdded,
	UserAdded)
	Values (
	@PropertyID, 
	@TaxAuthorityID,
	@TaxTypeID, 
	@CommentTypeID,
	@Comment,
	ISNULL(@DateAdded, GETDATE()),
	@UserAdded)


END

Open in new window


Calling Routine:
Public Sub writeComment_Sproc(passedPropertyID As Long, _
                              passedComment As String, _
                     Optional passedTaxAuthorityID As Long = 0, _
                     Optional passedTaxTypeID As Long = 0, _
                     Optional passedUserName As String = "", _
                     Optional passedCommentTypeID As Long = 1, _
                     Optional passedDate As Variant = Null)
'
wkDateTime = Now
wkUser = GimmeUserName

If Len(Trim(passedUserName)) > 0 Then
    wkUser = passedUserName
Else
    wkUser = GimmeUserName
End If
'
If IsValidSQLDate(passedDate) Then
    wkDateTime = passedDate
End If

'
Dim adCn As ADODB.Connection
Dim adCmd As ADODB.Command
'Dim adRs As ADODB.Recordset
Dim sConn As String

Const spName = "dbo.sptblCommentsAdd"

setSQLConnection
sConn = gConnection
'
Set adCn = New ADODB.Connection
adCn.Open sConn

Set adCmd = New ADODB.Command
adCmd.ActiveConnection = adCn
adCmd.CommandText = spName
adCmd.CommandType = adCmdStoredProc
adCmd.Parameters.Refresh
adCmd.Parameters(1).Value = passedPropertyID
adCmd.Parameters(2).Value = passedComment
adCmd.Parameters(3).Value = passedTaxAuthorityID
adCmd.Parameters(4).Value = passedTaxTypeID
adCmd.Parameters(5).Value = passedCommentTypeID
adCmd.Parameters(6).Value = wkUser
adCmd.Parameters(7).Value = wkDateTime

adCmd.Execute Options:=adExecuteNoRecords

'
'adRs.Close     ' no recordset on this one
adCn.Close
'
'Set adRs = Nothing
Set adCmd = Nothing
Set adCn = Nothing
'
End Sub

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bfuchsCommented:
Isn't the date param supposed to be surrounded by quotes?
0
mlcktmguyAuthor Commented:
To see if this was the date was causing the issue, instead of passing a date in that field I passed Null.  I could do that since this is an optional field.

I still get the same error.  Since the last six fields are optional, I replaced them all with Null but I still got the error.  I'm certain that the PropertyID, which is the only required parameter is a valid number.

I even more confused now.
0
bfuchsCommented:
Well in that case, the best thing would be to go back to the last working version and apply each change separately, hopefully you will find the culprit..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
I ended up modifying the calling routine using a param format that allows specific data type definitions.

Public Sub writeComment_Sproc(passedPropertyID As Long, _
                              passedComment As String, _
                     Optional passedTaxAuthorityID As Long = 0, _
                     Optional passedTaxTypeID As Long = 0, _
                     Optional passedUserName As String = "", _
                     Optional passedCommentTypeID As Long = 1, _
                     Optional passedDate As Variant = Null)
'
wkDateTime = Now
wkUser = GimmeUserName

If Len(Trim(passedUserName)) > 0 Then
    wkUser = passedUserName
Else
    wkUser = GimmeUserName
End If
'
If IsValidSQLDate(passedDate) Then
    wkDateTime = passedDate
End If
'
Dim returnRecID As Long
'
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim param3 As New ADODB.Parameter
Dim param4 As New ADODB.Parameter
Dim param5 As New ADODB.Parameter
Dim param6 As New ADODB.Parameter
Dim param7 As New ADODB.Parameter

With cmd
    .CommandText = "sptblCommentsAdd"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    Set param1 = .CreateParameter("passedPropertyID", adBigInt, adParamInput, , passedPropertyID)
    .Parameters.Append param1
    '
    Set param2 = .CreateParameter("passedComment", adVarChar, adParamInput, 1000, passedComment)
    .Parameters.Append param2
    '
    Set param3 = .CreateParameter("passedTaxAuthorityID", adBigInt, adParamInput, , passedTaxAuthorityID)
    .Parameters.Append param3
    '
    Set param4 = .CreateParameter("passedTaxTypeID", adBigInt, adParamInput, , passedTaxTypeID)
    .Parameters.Append param4
    '
    Set param5 = .CreateParameter("passedCommentTypeID", adBigInt, adParamInput, passedCommentTypeID)
    .Parameters.Append param5
    '
    Set param6 = .CreateParameter("passedUserName", adVarChar, adParamInput, 100, passedUserName)
    .Parameters.Append param6
    '
    Set param7 = .CreateParameter("passedDate", adDBTimeStamp, adParamInput, , passedDate)
    .Parameters.Append param7
     
    .Execute Options:=adExecuteNoRecords
    Set .ActiveConnection = Nothing
End With

'MsgBox FormatCurrency(param2.Value, 2)
'MsgBox FormatCurrency(cmd.Parameters("TotalDollar"), 2)

Set param1 = Nothing
Set param2 = Nothing
Set param3 = Nothing
Set param4 = Nothing
Set param5 = Nothing
Set param6 = Nothing
Set param7 = Nothing
'
Set cmd = Nothing
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.