Error Calling Stored Procedure with Multiple Parameters

I am a long time user of Access but new to using a SQL Server backend in Access.

I have written my second stored procedure in SQL, it is to insert a record into a table.  The parameters passed to the SP are the fields to be populated in the table.  Here's the SP"

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spAddNameAddressWork]    Script Date: 7/30/2015 10:25:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAddNameAddressWork] 
	@ID                   int,
	@ControlNumber        int,
	@SequenceNumber       int,
	@AddrLine1            nvarchar(30),
	@AddrLine2            nvarchar(30),
	@AddrLine3            nvarchar(30),
	@AddrLine4            nvarchar(30),
	@City                 nvarchar(50),
	@State                nvarchar(2),
	@Zip                  nvarchar(50),
	@UnMailable           bit,
	@Bankruptcy           bit,
	@AddressCheckNeeded   bit,
	@SelectForAct20       bit,
	@MuniCode             smallint

As

Begin

Set NOCOUNT on;

Insert dbo.SQNA_NameAddress_Work (ID, 
	ControlNumber, 
	SequenceNumber,
	AddrLine1,
	AddrLine2,
	AddrLine3,
	AddrLine4,
	City, 
	State,
	Zip,
	UnMailable,
	Bankruptcy,
	AddressCheckNeeded,
	SelectForAct20, 
	MuniCode)
	Values (@ID, 
	@ControlNumber, 
	@SequenceNumber,
	@AddrLine1,
	@AddrLine2,
	@AddrLine3,
	@AddrLine4,
	@City, 
	@State,
	@Zip,
	@UnMailable,
	@Bankruptcy,
	@AddressCheckNeeded,
	@SelectForAct20, 
	@MuniCode)


END

Open in new window


Here is the code I created so far to call the stored procedure.

Public Sub addNameAddr_SQL_SP(wkID As Long, _
                              wkMuniCode As Long, _
                              wkControlNumber As Long, _
                              wkSequenceNumber As Long, _
                              wkAddrLine1 As String, _
                              wkAddrLine2 As String, _
                              wkAddrLine3 As String, _
                              wkAddrLine4 As String, _
                              wkCity As String, _
                              wkState As String, _
                              wkZip As String, _
                              wkUnMailableYN As Boolean, _
                              wkBankruptcyYN As Boolean, _
                              wkAddrCheckNeededYN As Boolean, _
                              wkSelectForAct20YN As Boolean)
                              
'
Dim adCn As ADODB.Connection
Dim adCmd As ADODB.Command
Dim adRs As ADODB.Recordset
Dim sConn As String

Const spName = "dbo.spAddNameAddressWork"

sConn = "DRIVER=SQL Server;SERVER=MIKE-PC\WIN71SQLSERVER;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=JTSConversion;"

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 = wkID
adCmd.Parameters(3).Value = wkControlNumber
adCmd.Parameters(4).Value = wkSequenceNumber
adCmd.Parameters(5).Value = wkAddrLine1
adCmd.Parameters(6).Value = wkAddrLine2
adCmd.Parameters(7).Value = wkAddrLine3
adCmd.Parameters(8).Value = wkAddrLine4
adCmd.Parameters(8).Value = wkCity
adCmd.Parameters(9).Value = wkState
adCmd.Parameters(10).Value = wkZip
adCmd.Parameters(11).Value = wkUnMailableYN
adCmd.Parameters(12).Value = wkBankruptcyYN
adCmd.Parameters(13).Value = wkAddrCheckNeededYN
adCmd.Parameters(14).Value = wkSelectForAct20YN
adCmd.Parameters(15).Value = wkMuniCode

'
Set adRs = adCmd.Execute
'
adRs.Close
adCn.Close
'
Set adRs = Nothing
Set adCmd = Nothing
Set adCn = Nothing
'
End Sub

Open in new window


Finally the routine I created to test the call to fill the parameters used to call the stored procedure.

Public Sub testAddNameAddrSQL90()
'
Dim wkID  As Long
Dim wkMuniCode  As Long
Dim wkControlNumber  As Long
Dim wkSequenceNumber  As Long
Dim wkAddrLine1  As String
Dim wkAddrLine2  As String
Dim wkAddrLine3  As String
Dim wkAddrLine4  As String
Dim wkCity As String
Dim wkState As String
Dim wkZip As String
'
Dim wkUnMailableYN As Boolean
Dim wkBankruptcyYN As Boolean
Dim wkAddrCheckNeededYN As Boolean
Dim wkSelectForAct20YN As Boolean
'
wkID = 1111
wkMuniCode = 222
wkControlNumber = 333
wkSequenceNumber = 444
wkAddrLine1 = "This is Addr1"
wkAddrLine2 = "This is Addr2"
wkAddrLine3 = "This is Addr3"
wkAddrLine4 = "This is Addr4"
wkCity = "SomeCity"
wkState = "ST'"
wkZip = "55555"
'
wkUnMailableYN = True
wkBankruptcyYN = False
wkAddrCheckNeededYN = True
wkSelectForAct20YN = False
'
addNameAddr_SQL_SP wkID, _
                   wkMuniCode, _
                   wkControlNumber, _
                   wkSequenceNumber, _
                   wkAddrLine1, _
                   wkAddrLine2, _
                   wkAddrLine3, _
                   wkAddrLine4, _
                   wkCity, _
                   wkState, _
                   wkZip, _
                   wkUnMailableYN, _
                   wkBankruptcyYN, _
                   wkAddrCheckNeededYN, _
                   wkSelectForAct20YN
'
End Sub

Open in new window


In the routine calling the stored procedure I am getting an error on the statement

adCmd.Parameters(9).Value = wkState

The error is: 3421 Application uses a value of the wrong type for the current operation

I checked the contents of wkState are 'ST' exactly as expected.  The parameter is defined as nvarchar(2) and the 'State' field in the table is defined as nvarchar(2)

Can anyone spot an issue?

Here's a snapshot of the table definitionSQL Table Definition
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.

Rey Obrero (Capricorn1)Commented:
line 29 where you assign values to variables

wkState = "ST'"  < it has a single quote on it after the letter T

try

wkState = "ST"
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
Lee SavidgeCommented:
Line 29 in testAddNameAddrSQL90

wkState = "ST'"

It has an extra single quote in it.
0
Lee SavidgeCommented:
Beaten by 7 seconds!
0
mlcktmguyAuthor Commented:
Thanks, I could have looked at that for hours and never seen that.
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
Microsoft Access

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.