在我的SQL存储过程未知无效类型converson [英] Unknown invalid type converson in my SQL stored Procedure

问题描述

错误：转换转换为nvarchar值时的失败的 WOID值的数据类型为int

系统背景：使用MS-Access 2010中目前正隐藏模块code和调用存储过程编码VBA。该存储过程编写SQL和运行MS-SQL服务器上，其中数据库存储2008应用程序。

存储过程：存储过程的宗旨是：

• 检索三个输入参数：WOID，SampleID和分析物
• 在连接两个表：tblWoSampleTest，TBLTEST
• 选择testID那里的三个值匹配
检索testId的输出参数
• 在保存值

请注意：WOID和SampleID列在tblWoSampleTest和分析物是TBLTEST

下面是我的，我已经测试过的SQL Management Studio并似乎工作code。有一个ISNULL的语句，因为有时一个testId不存在，如果返回一个零，我知道跳过一定的逻辑。

  CREATE PROCEDURE upGetTestIDForAnalyte @WOID为nvarchar（60），@SampleID为nvarchar（60），@分析物为nvarchar（60），@TestId int的列
如

选择@TestID = t1.TestID
从tblWOSampleTest T1
JOIN TBLTEST T2
ON t1.TestID = t2.TestID
WHERE @WOID = t1.WOID和@SampleID = t1.SampleID和@Analyte = t2.Analyte

选择@TestID = ISNULL（@ TestID，0）

走
 

在接入过程，WOID声明为一个字符串。此外，当我看到的数据类型的tblWOSAmpleTest的WOID列是它为nvarchar，SampleID是int和分析物为int。什么任何建议可能是错的？此外，我可以提供我的VBA code。如果这种错误经常调用存储过程而不是实际存储过程本身时，会发生 更新调用code：

 设置CMD =新ADODB.Command
    cmd.ActiveConnection =康恩
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText =upGetTestIDForAnalyte

    cmd.Parameters.Append cmd.CreateParameter（@分析物，adVarChar，adParamInput，60，分析物）
    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）。值
 

另外，根据t1.WOID将是tblWOsampletest列WOID的数据类型是类型为nvarchar（10）。此外，我传递到存储过程的字符串看起来somethink这样的1406-00591 UPDATE2：这里是tblwosampletest的表定义（T1）

  CREATE TABLE [DBO]。[tblWOSampleTest]（
[WOID] [为nvarchar（10）NOT NULL，
[SampleID] [INT] NOT NULL，
[TestID] [INT] NOT NULL，
[TestPrice] [浮法] NULL，
[StatusID] [INT] NULL，
[输入用户] [INT] NULL，
[ModID] [INT] NULL，
[EnterDate] [日期时间] NULL，
[修改日期] [日期时间] NULL，
[AnalysisId] [INT] NULL，
[DateAnalyzed] [日期时间] NULL，
[ActResult] [浮法] NULL，
[QueueId] [INT] NULL，
[ONHOLD] [位] NOT NULL，
[批准] [位] NOT NULL，
[AddToQueue] [位] NOT NULL，
[TemplateID] [INT] NULL，
[FormulaId] [INT] NULL，
[签名] [INT] NULL，
[缩写] [INT] NULL，
[FlagTest] [INT] NULL，
[DisplayedResult] [为nvarchar（50）NULL，
[TechId] [INT] NULL，
[DeleteTest] [位] NOT NULL，
[RdSerialNum] [为nvarchar（50）NULL，
[RdStartDate] [日期时间] NULL，
[RdEndDate] [日期时间] NULL，
[ScinilatedDT] [日期时间] NULL，
[RdCPMA] [浮法] NULL，
[RdBackground] [浮法] NULL，
[RdDecayhrs] [浮法] NULL，
[RdExposhrs] [浮法] NULL，
[RdElutehrs] [浮法] NULL，
[RdDegreesF] [浮法] NULL，
[RdWCmpSample] [浮法] NULL，
[RdWCpmBKrnd] [浮法] NULL，
[RdWTFourAnaTime] [浮法] NULL，
[RdWEluTime] [浮法] NULL，
 约束[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]

走

。ALTER TABLE [DBO] [tblWOSampleTest] WITH NOCHECK ADD CONSTRAINT [FK_tblWOSampleTest_tblTest]外键（[TestID]）
参考文献：[DBO]。[TBLTEST]（[TestID]）
走

ALTER TABLE [DBO]。[tblWOSampleTest]检查约束[FK_tblWOSampleTest_tblTest]
走

。ALTER TABLE [DBO] [tblWOSampleTest] WITH NOCHECK ADD CONSTRAINT [FK_tblWOSampleTest_tblWO]外键（[WOID]）
参考文献：[DBO]。[tblWO]（[WOID]）
走

ALTER TABLE [DBO]。[tblWOSampleTest]检查约束[FK_tblWOSampleTest_tblWO]
走

ALTER TABLE [DBO]。[tblWOSampleTest] WITH NOCHECK ADD CONSTRAINT [FK_tblWOSampleTest_tlkpQueue]外键（[QueueId]）
参考文献：[DBO]。[tlkpQueue]（[QueueId]）
走

ALTER TABLE [DBO]。[tblWOSampleTest]检查约束[FK_tblWOSampleTest_tlkpQueue]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_TestPrice] DEFAULT（0）[TestPrice]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_ActResult] DEFAULT（0）[ActResult]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_QueueId] DEFAULT（0）[QueueId]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_OnHold] DEFAULT（1）[ONHOLD]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_Approved] DEFAULT（0）【批准]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_AddToQueue] DEFAULT（0）[AddToQueue]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_DeleteTest] DEFAULT（0）[DeleteTest]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdCPMA] DEFAULT（0）[RdCPMA]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdBackground] DEFAULT（0）[RdBackground]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdDecayhrs] DEFAULT（0）[RdDecayhrs]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdExposhrs] DEFAULT（0）[RdExposhrs]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdElutehrs] DEFAULT（0）[RdElutehrs]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdDegreesF] DEFAULT（0）[RdDegreesF]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdWCmpSample] DEFAULT（0）[RdWCmpSample]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdWCpmBKrnd] DEFAULT（0）[RdWCpmBKrnd]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdWTFourAnaTime] DEFAULT（0）[RdWTFourAnaTime]
走

。ALTER TABLE [DBO] [tblWOSampleTest] ADD CONSTRAINT [DF_tblWOSampleTest_RdWEluTime] DEFAULT（0）[RdWEluTime]
走
 

解决方案

尝试手动转换t1.WOID为nvarchar的。

  CREATE PROCEDURE upGetTestIDForAnalyte @WOID为nvarchar（60），@SampleID为nvarchar（60），@分析物为nvarchar（60），@TestId int的列
如

选择@TestID = t1.TestID
从tblWOSampleTest T1
JOIN TBLTEST T2
ON t1.TestID = t2.TestID
WHERE @WOID =转换（NVARCHAR，t1.WOID）和@SampleID = t1.SampleID和@Analyte = t2.Analyte

选择@TestID = ISNULL（@ TestID，0）
 

希望这会有所帮助。

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

解决方案

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.

这篇关于在我的SQL存储过程未知无效类型converson的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！