Unknown invalid type converson in my SQL stored Pr

Question

Error: Conversion failed when converting the nvarchar value " value of WOID" to data type int

System background: Coding in VBA using MS-Access 2010. Currently working on code behind module and calling stored procedure. The stored procedure is written in SQL and ran on the Ms-SQL server 2008 application where the database is stored.

Stored Procedure: The stored procedure's purpose is to:

• Retrieve three input parameters: WOID, SampleID and Analyte
• Join two tables: tblWoSampleTest , tblTest
• Select testID WHERE the three values match
• Save value of retrieved testId in output parameter

note: WOID and SampleID column are in tblWoSampleTest and Analyte is in tbltest

Here is my code that I have tested on the SQL management Studio and seems to work. There is a IsNUll statement because sometimes a testId does not exist and if a zero is returned I know to skip certain logic.

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @TestId int OUT
AS

SELECT @TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID 
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte

SELECT @TestID =ISNULL(@TestID,0) 

go

In the access procedure, WOID is declared as a string. Also when I look of the data type in the WOID column of tblWOSAmpleTest it it nvarchar, SampleID is int and Analyte is int. Any suggestions on what could be wrong? Also I can supply my VBA code if this type of error often occurs when calling the stored procedure opposed to the actual stored procedure itself Update calling code:

Set cmd = New ADODB.Command
    cmd.ActiveConnection = Conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "upGetTestIDForAnalyte"

    cmd.Parameters.Append cmd.CreateParameter("@Analyte", adVarChar, adParamInput, 60, Analyte)
    cmd.Parameters.Append cmd.CreateParameter("@WOID", adVarChar, adParamInput, 60, ThisWOID)
    cmd.Parameters.Append cmd.CreateParameter("@SampleID", adDouble, adParamInput, 60, ThisSampleID)
    cmd.Parameters.Append cmd.CreateParameter("@testid", adDouble, adParamOutput, , adParamReturnValue)
    cmd.Execute
    Conn.Close
    ThisTestID = cmd.Parameters("@testid").Value

Also the t1.WOID would be the data type of the column WOID in tblWOsampletest which is type nvarchar (10). Also the string I pass to the stored procedure will look somethink like this "1406-00591" Update2: here is the table definition of tblwosampletest (t1)

CREATE TABLE [dbo].[tblWOSampleTest](
[WOID] [nvarchar](10) NOT NULL,
[SampleID] [int] NOT NULL,
[TestID] [int] NOT NULL,
[TestPrice] [float] NULL,
[StatusID] [int] NULL,
[EnterID] [int] NULL,
[ModID] [int] NULL,
[EnterDate] [datetime] NULL,
[ModDate] [datetime] NULL,
[AnalysisId] [int] NULL,
[DateAnalyzed] [datetime] NULL,
[ActResult] [float] NULL,
[QueueId] [int] NULL,
[OnHold] [bit] NOT NULL,
[Approved] [bit] NOT NULL,
[AddToQueue] [bit] NOT NULL,
[TemplateID] [int] NULL,
[FormulaId] [int] NULL,
[Signature] [int] NULL,
[Initials] [int] NULL,
[FlagTest] [int] NULL,
[DisplayedResult] [nvarchar](50) NULL,
[TechId] [int] NULL,
[DeleteTest] [bit] NOT NULL,
[RdSerialNum] [nvarchar](50) NULL,
[RdStartDate] [datetime] NULL,
[RdEndDate] [datetime] NULL,
[ScinilatedDT] [datetime] NULL,
[RdCPMA] [float] NULL,
[RdBackground] [float] NULL,
[RdDecayhrs] [float] NULL,
[RdExposhrs] [float] NULL,
[RdElutehrs] [float] NULL,
[RdDegreesF] [float] NULL,
[RdWCmpSample] [float] NULL,
[RdWCpmBKrnd] [float] NULL,
[RdWTFourAnaTime] [float] NULL,
[RdWEluTime] [float] NULL,
 CONSTRAINT [PK_tblWOSampleTest] PRIMARY KEY CLUSTERED 
(
[WOID] ASC,
[SampleID] ASC,
[TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblWOSampleTest]  WITH NOCHECK ADD  CONSTRAINT [FK_tblWOSampleTest_tblTest] FOREIGN KEY([TestID])
REFERENCES [dbo].[tblTest] ([TestID])
GO

ALTER TABLE [dbo].[tblWOSampleTest] CHECK CONSTRAINT [FK_tblWOSampleTest_tblTest]
GO

ALTER TABLE [dbo].[tblWOSampleTest]  WITH NOCHECK ADD  CONSTRAINT [FK_tblWOSampleTest_tblWO] FOREIGN KEY([WOID])
REFERENCES [dbo].[tblWO] ([WOID])
GO

ALTER TABLE [dbo].[tblWOSampleTest] CHECK CONSTRAINT [FK_tblWOSampleTest_tblWO]
GO

ALTER TABLE [dbo].[tblWOSampleTest]  WITH NOCHECK ADD  CONSTRAINT [FK_tblWOSampleTest_tlkpQueue] FOREIGN KEY([QueueId])
REFERENCES [dbo].[tlkpQueue] ([QueueId])
GO

ALTER TABLE [dbo].[tblWOSampleTest] CHECK CONSTRAINT [FK_tblWOSampleTest_tlkpQueue]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_TestPrice]  DEFAULT (0) FOR [TestPrice]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_ActResult]  DEFAULT (0) FOR [ActResult]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_QueueId]  DEFAULT (0) FOR [QueueId]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_OnHold]  DEFAULT (1) FOR [OnHold]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_Approved]  DEFAULT (0) FOR [Approved]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_AddToQueue]  DEFAULT (0) FOR [AddToQueue]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_DeleteTest]  DEFAULT (0) FOR [DeleteTest]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdCPMA]  DEFAULT (0) FOR [RdCPMA]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdBackground]  DEFAULT (0) FOR [RdBackground]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdDecayhrs]  DEFAULT (0) FOR [RdDecayhrs]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdExposhrs]  DEFAULT (0) FOR [RdExposhrs]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdElutehrs]  DEFAULT (0) FOR [RdElutehrs]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdDegreesF]  DEFAULT (0) FOR [RdDegreesF]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdWCmpSample]  DEFAULT (0) FOR [RdWCmpSample]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdWCpmBKrnd]  DEFAULT (0) FOR [RdWCpmBKrnd]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdWTFourAnaTime]  DEFAULT (0) FOR [RdWTFourAnaTime]
GO

ALTER TABLE [dbo].[tblWOSampleTest] ADD  CONSTRAINT [DF_tblWOSampleTest_RdWEluTime]  DEFAULT (0) FOR [RdWEluTime]
GO

Answer 1

try to manually convert t1.WOID to nvarchar.

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @TestId int OUT
AS

SELECT @TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID 
WHERE @WOID = CONVERT(NVARCHAR,t1.WOID) AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte

SELECT @TestID =ISNULL(@TestID,0) 

hope this helps.