无效的强制转换异常 [英] Invalid cast exception
本文介绍了无效的强制转换异常的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的存储过程在这里我面临无效演员阵容的异常，请建议...
this is my stored procedure here I am facing Exception of Invalid cast, kindly suggest...
USE [bomgen_UTC_PROD]
GO
/****** Object: StoredProcedure [bomgen_Admin].[utc_NewProcessImportData] Script Date: 03/30/2015 01:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [bomgen_Admin].[utc_NewProcessImportData]
(
@ImportStagingId INT,
@EffectiveDate DATE,
@VersionName NVARCHAR(1000),
@VersionNotes NVARCHAR(1000)
)
AS
BEGIN
SET NOCOUNT ON;
--BEGIN TRANSACTION ProcessImportDataTransaction;
BEGIN TRY
DECLARE @TxnCountLimit INT, @TxnCount INT;
SELECT @TxnCountLimit = 500;
SELECT @TxnCount = 0;
DECLARE @VersionId INT, @NewPartId INT, @NewPartDetailId INT, @MatchingPartDetailId INT, @CategoryId INT;
DECLARE @PartNumber NVARCHAR(200), @RowNumber INT, @PartId INT;
DECLARE @FullDescription NVARCHAR(MAX), @ShortDescription NVARCHAR(MAX), @ShippingWeightLB DECIMAL(10,2), @ShippingWeightKG DECIMAL(10,2);
DECLARE @ListPrice MONEY, @ListPriceText NVARCHAR(MAX), @PriceLists NVARCHAR(100), @CountryOfOrigin NVARCHAR(3);
DECLARE @idx INT, @lidx INT;
DECLARE @CheckCat NVARCHAR(200);
DECLARE @PriorVersionId INT, @PriorHazardVersionId INT;
-- create a new version
INSERT INTO Version (EffectiveDate, VersionName, VersionNotes, PublishedFlag)
VALUES (@EffectiveDate, @VersionName, @VersionNotes, 0);
SELECT @VersionId = SCOPE_IDENTITY();
-- copy the hazard versions;
SELECT @PriorHazardVersionId = MAX(VersionId)
FROM HazardVersion;
IF (@PriorHazardVersionId IS NOT NULL)
BEGIN
PRINT 'previous hazard version=' + cast(@PriorHazardVersionId as varchar(100))
INSERT INTO HazardVersion (HazardId, AgentId, VersionId)
SELECT hv.HazardId
, hv.AgentId
, @VersionId
FROM HazardVersion hv
WHERE hv.VersionId = @PriorHazardVersionId;
END;
ELSE
BEGIN
PRINT 'previous hazard version is null so=' + cast(@versionId as varchar(100))
INSERT INTO HazardVersion (HazardId, AgentId, VersionId)
SELECT h.HazardId
, h.AgentId
, @VersionId
FROM Hazard h;
END;
DECLARE NewPartCursor CURSOR FOR
SELECT LTRIM(RTRIM(isd.PartNumber)) AS PartNumber
, LTRIM(RTRIM(isd.FullDescription)) AS FullDescription
, LTRIM(RTRIM(isd.ShortDescription)) AS ShortDescription
, CASE WHEN ISNUMERIC(LTRIM(RTRIM(isd.ShippingWeightLB))) = 1 THEN CAST(LTRIM(RTRIM(REPLACE(isd.ShippingWeightLB, ',', ''))) AS DECIMAL(10,2)) ELSE NULL END AS ShippingWeightLB
, CASE WHEN ISNUMERIC(LTRIM(RTRIM(isd.ShippingWeightKG))) = 1 THEN CAST(LTRIM(RTRIM(REPLACE(isd.ShippingWeightKG, ',', ''))) AS DECIMAL(10,2)) ELSE NULL END AS ShippingWeightKG
, CASE WHEN ISNUMERIC(LTRIM(RTRIM(isd.ListPriceText))) = 1 THEN CAST(LTRIM(RTRIM(isd.ListPriceText)) AS MONEY) ELSE NULL END AS ListPrice
, LTRIM(RTRIM(ListPriceText)) AS ListPriceText
, LTRIM(RTRIM(isd.PriceLists)) AS PriceLists
, LTRIM(RTRIM(isd.CountryOfOrigin)) AS CountryOfOrigin
, isd.RowNumber
, p.PartId
FROM ImportStagingDetail isd
LEFT OUTER JOIN Part p ON (LTRIM(RTRIM(isd.PartNumber)) = p.PartNumber)
WHERE isd.ImportStagingId = @ImportStagingId;
PRINT 'part no =' + cast(@PartNumber as varchar(100))
OPEN NewPartCursor;
FETCH NEXT FROM NewPartCursor INTO @PartNumber, @FullDescription, @ShortDescription, @ShippingWeightLB,
@ShippingWeightKG, @ListPrice, @ListPriceText, @PriceLists, @CountryOfOrigin, @RowNumber, @PartId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Part Number..='+@PartNumber;
IF (@TxnCount = 0)
BEGIN
BEGIN TRANSACTION ProcessImportDataTransaction;
END;
-- check if row already exists in the part table; if it doesn't, add it. if it does, update it if necessary.
IF (@PartId IS NULL)
BEGIN
-- create the part
INSERT INTO Part (PartNumber, InitialVersionId)
VALUES (@PartNumber, @VersionId);
SELECT @NewPartId = SCOPE_IDENTITY();
PRINT 'New part id ='+ cast(@NewPartId as varchar(100));
-- insert into part detail
INSERT INTO PartDetail (PartId, FullDescription, ShortDescription, ShippingWeightLB, ShippingWeightKG, ListPrice, ListPriceText, CountryOfOrigin)
VALUES (@NewPartId, @FullDescription, @ShortDescription, @ShippingWeightLB, @ShippingWeightKG, @ListPrice, @ListPriceText, @CountryOfOrigin);
SELECT @NewPartDetailId = SCOPE_IDENTITY();
INSERT INTO PartDetailVersion (PartDetailId, VersionId)
VALUES (@NewPartDetailId, @VersionId);
-- parse the price lists and add the part to the appropriate categories
SELECT @lidx = 1;
SELECT @idx = CHARINDEX(',', @PriceLists);
WHILE @idx > 0
BEGIN
SELECT @CheckCat = SUBSTRING(@PriceLists, @lidx, @idx-@lidx);
INSERT INTO PartCategory (PartId, CategoryId, VersionId)
SELECT @NewPartId
, c.CategoryId
, @VersionId
FROM Category c
WHERE c.CategoryString = @CheckCat;
SELECT @lidx = @idx+1;
SELECT @idx = CHARINDEX(',', @PriceLists, @lidx);
END;
END;
ELSE
BEGIN
-- part already exists
PRINT 'Partid already exits='+cast(@PartId as varchar(100));
-- check if the part detail is different any version
SELECT @MatchingPartDetailId = MAX(PartDetailId)
FROM PartDetail
WHERE PartId = @PartId
AND LTRIM(RTRIM(FullDescription)) = @FullDescription
AND LTRIM(RTRIM(ShortDescription)) = @ShortDescription
AND ISNULL(ShippingWeightLB, -55.00) = ISNULL(@ShippingWeightLB, -55.00)
AND ISNULL(ShippingWeightKG, -55.00) = ISNULL(@ShippingWeightKG, -55.00)
AND ISNULL(ListPrice, -55.00) = ISNULL(@ListPrice, -55.00)
AND LTRIM(RTRIM(ListPriceText)) = @ListPriceText
AND LTRIM(RTRIM(CountryOfOrigin)) = @CountryOfOrigin;
IF (@MatchingPartDetailId IS NULL)
BEGIN
-- insert into part detail
INSERT INTO PartDetail (PartId, FullDescription, ShortDescription, ShippingWeightLB, ShippingWeightKG, ListPrice, ListPriceText, CountryOfOrigin)
VALUES (@PartId, @FullDescription, @ShortDescription, @ShippingWeightLB, @ShippingWeightKG, @ListPrice, @ListPriceText, @CountryOfOrigin);
SELECT @NewPartDetailId = SCOPE_IDENTITY();
INSERT INTO PartDetailVersion (PartDetailId, VersionId)
VALUES (@NewPartDetailId, @VersionId);
END;
ELSE
BEGIN
-- part detail already exists, just need to create a new part detail version
INSERT INTO PartDetailVersion (PartDetailId, VersionId)
VALUES (@MatchingPartDetailId, @VersionId);
END;
-- parse the price lists and add the part to the appropriate categories
SELECT @lidx = 1;
SELECT @idx = CHARINDEX(',', @PriceLists);
WHILE @idx > 0
BEGIN
SELECT @CheckCat = SUBSTRING(@PriceLists, @lidx, @idx-@lidx);
INSERT INTO PartCategory (PartId, CategoryId, VersionId)
SELECT @PartId
, c.CategoryId
, @VersionId
FROM Category c
WHERE c.CategoryString = @CheckCat;
SELECT @lidx = @idx+1;
SELECT @idx = CHARINDEX(',', @PriceLists, @lidx);
END;
-- copy any part default, part document, part group/subgroup, part additional info that may already exist
-- what is the max version
SELECT @PriorVersionId = MAX(pc.VersionId)
FROM PartCategory pc
WHERE pc.PartId = @PartId
AND pc.VersionId != @VersionId;
IF (@PriorVersionId IS NOT NULL)
BEGIN
INSERT INTO PartAdditionalInfo (PartId, VersionId, AgentId, IsAgentFlag, IsContainerFlag, MinFillLB, MinFillKG, MaxFillLB, MaxFillKG, DefaultNozzle)
SELECT pai.PartId
, @VersionId
, pai.AgentId
, pai.IsAgentFlag
, pai.IsContainerFlag
, pai.MinFillLB
, pai.MinFillKG
, pai.MaxFillLB
, pai.MaxFillKG
, pai.DefaultNozzle
FROM PartAdditionalInfo pai
WHERE pai.PartId = @PartId
AND pai.VersionId = @PriorVersionId;
-- check to see if the part groups this would belong to already exist.
INSERT INTO PartGroup (CategoryId, VersionId, GroupName, Notes, DisplayOrder)
SELECT pg.CategoryId
, @VersionId
, pg.GroupName
, pg.Notes
, pg.DisplayOrder
FROM PartGroup pg INNER JOIN PartPartGroupLink ppgl ON pg.PartGroupId = ppgl.PartGroupId
WHERE ppgl.PartId = @PartId
AND pg.VersionId = @PriorVersionId
AND NOT EXISTS (SELECT *
FROM PartGroup pg0
WHERE pg0.CategoryId = pg.CategoryId
AND pg0.VersionId = @VersionId
AND pg0.GroupName = pg.GroupName);
INSERT INTO PartPartGroupLink (PartId, PartGroupId)
SELECT ppgl.PartId
, pg0.PartGroupId
FROM PartPartGroupLink ppgl
INNER JOIN PartGroup pg ON (ppgl.PartGroupId = pg.PartGroupId)
INNER JOIN PartGroup pg0 ON (pg.CategoryId = pg0.CategoryId AND
pg.GroupName = pg0.GroupName AND
pg0.VersionId = @VersionId)
WHERE ppgl.PartId = @PartId
AND pg.VersionId = @PriorVersionId
-- check to see if the part subgroups this would belong to already exist.
INSERT INTO PartSubgroup (SubgroupName, PartGroupId, Notes, DisplayOrder)
SELECT ps.SubgroupName
, pgx.PartGroupId
, ps.Notes
, ps.DisplayOrder
FROM PartSubgroup ps INNER JOIN PartPartSubgroupLink ppsl ON (ps.PartSubgroupId = ppsl.PartSubgroupId)
INNER JOIN PartGroup pg ON ps.PartGroupId = pg.PartGroupId
INNER JOIN PartGroup pgx ON (pg.CategoryId = pgx.CategoryId AND
pg.GroupName = pgx.GroupName AND
pgx.VersionId = @VersionId)
WHERE ppsl.PartId = @PartId
AND pg.VersionId = @PriorVersionId
AND NOT EXISTS (SELECT *
FROM PartSubgroup ps0
WHERE ps0.PartGroupId = pgx.PartGroupId
AND ps0.SubgroupName = ps.SubgroupName);
--INSERT INTO PartSubgroup (SubgroupName, PartGroupId, Notes)
-- SELECT ps.SubgroupName
-- , pgx.PartGroupId
-- , ps.Notes
-- FROM PartSubgroup ps INNER JOIN PartPartSubgroupLink ppsl ON (ps.PartSubgroupId = ppsl.PartSubgroupId)
-- INNER JOIN PartGroup pg ON ps.PartGroupId = pg.PartGroupId
-- INNER JOIN PartGroup pgx ON (pg.CategoryId = pgx.CategoryId AND
-- pg.GroupName = pgx.GroupName AND
-- pgx.VersionId = @VersionId)
-- WHERE ppsl.PartId = @PartId
-- AND pg.VersionId = @PriorVersionId
-- AND NOT EXISTS (SELECT *
-- FROM PartSubgroup ps0 INNER JOIN PartGroup pg0 ON ps0.PartGroupId = pg0.PartGroupId
-- WHERE pg0.CategoryId = pg.CategoryId
-- AND pg0.VersionId = @VersionId
-- AND pg0.GroupName = pg.GroupName
-- AND ps0.PartGroupId = ps.PartGroupId
-- AND ps0.SubgroupName = ps.SubgroupName);
INSERT INTO PartPartSubgroupLink (PartId, PartSubgroupId)
SELECT ppsl.PartId
, ps0.PartSubgroupId
FROM PartPartSubgroupLink ppsl
INNER JOIN PartSubgroup ps ON (ppsl.PartSubgroupId = ps.PartSubgroupId)
INNER JOIN PartGroup pg ON (ps.PartGroupId = pg.PartGroupId)
INNER JOIN PartGroup pg0 ON (pg.CategoryId = pg0.CategoryId AND
pg.GroupName = pg0.GroupName AND
pg0.VersionId = @VersionId)
INNER JOIN PartSubgroup ps0 ON (pg0.PartGroupId = ps0.PartGroupId AND
ps.SubgroupName = ps0.SubgroupName)
WHERE ppsl.PartId = @PartId
AND pg.VersionId = @PriorVersionId;
INSERT INTO PartDocument (PartId, BrandId, VersionId, DocumentName, PartDocumentFileId)
SELECT pd.PartId
, pd.BrandId
, @VersionId
, pd.DocumentName
, pd.PartDocumentFileId
FROM PartDocument pd
WHERE pd.PartId = @PartId
AND pd.VersionId = @PriorVersionId;
DECLARE @PDId INT, @NewPartDefaultId INT;
DECLARE PartDefaultCursor CURSOR FOR
SELECT pd.PartDefaultId
FROM PartDefault pd
WHERE pd.PartId = @PartId
AND pd.VersionId = @PriorVersionId;
OPEN PartDefaultCursor;
FETCH NEXT FROM PartDefaultCursor INTO @PDId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'previous PartDefaultId=' + cast(@PDId as varchar(100)) + ' for version='+ cast(@VersionId as varchar(100));
INSERT INTO PartDefault (PartId, CategoryId, VersionId, DefaultPartId, DefaultQuantity, UseDefaultQuantityFlag, PerOrderFlag)
SELECT pd.PartId
, pd.CategoryId
, @VersionId
, pd.DefaultPartId
, pd.DefaultQuantity
, pd.UseDefaultQuantityFlag
, pd.PerOrderFlag
FROM PartDefault pd
WHERE pd.PartDefaultId = @PDId;
SELECT @NewPartDefaultId = SCOPE_IDENTITY();
PRINT 'New PartDefaultId=' + cast(@NewPartDefaultId as varchar(100)) ;
INSERT INTO PartDefaultDependency (PartDefaultId, SystemOptionId, ChoiceNumber)
SELECT @NewPartDefaultId
, pdd.SystemOptionId
, pdd.ChoiceNumber
FROM PartDefaultDependency pdd
WHERE pdd.PartDefaultId = @PDId;
FETCH NEXT FROM PartDefaultCursor INTO @PDId;
END;
CLOSE PartDefaultCursor;
DEALLOCATE PartDefaultCursor;
END;
END;
FETCH NEXT FROM NewPartCursor INTO @PartNumber, @FullDescription, @ShortDescription, @ShippingWeightLB,
@ShippingWeightKG, @ListPrice, @ListPriceText, @PriceLists, @CountryOfOrigin, @RowNumber, @PartId;
SELECT @TxnCount = @TxnCount + 1;
IF (@TxnCount > @TxnCountLimit)
BEGIN
SELECT @TxnCount = 0;
COMMIT TRANSACTION ProcessImportDataTransaction;
END;
END;
CLOSE NewPartCursor;
DEALLOCATE NewPartCursor;
COMMIT TRANSACTION ProcessImportDataTransaction;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @CursorStatus INT;
SELECT @CursorStatus = CURSOR_STATUS('global', 'NewPartCursor');
IF (@CursorStatus >= 0)
BEGIN
CLOSE NewPartCursor;
DEALLOCATE NewPartCursor;
END;
SELECT
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
--ROLLBACK TRANSACTION ProcessImportDataTransaction;
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END;
推荐答案
问题在于表的架构ImportStagingDetail
列
- ShippingWeightLB
- ShippingWeightKG
- ListPriceText
不是数字 - 实际上它们似乎是varchar包含可以包含逗号的数据的列。
始终使用适当的数据类型来存储数据：日期应该是Date或DateTime列类型，数字数据应该存储在数字列类型中;在你真正需要显示数据之前，不要关心数据如何看。
如果你检查数据，你可能会发现至少有一个我列出的至少一列中的值看起来不像小数（10,2）或金钱。
如果您在修复后仍然遇到问题架构，尝试将代码简化为基础，以便您可以看到树木。并在发布
The problem is with the schema of your tableImportStagingDetail
The columns
- ShippingWeightLB
- ShippingWeightKG
- ListPriceText
Are not numeric - in fact they appear to bevarcharcolumns holding data that can include commas.
Always use the appropriate data type for storing data: Dates should be in Date or DateTime column types, numeric data should be stored in a numeric column type; and do not concern yourself with how data will "look" until you actually need to display it.
If you examine your data you will probably find that at least one of the values in at least one of the columns I listed does not look like a decimal(10,2) or money.
If you are still having problems after fixing the schema, try reducing the code down to the basics so you can see the wood for the trees. And remove commented out code before you post
这篇关于无效的强制转换异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
