don't, but it seems likely that this is causing your problem.
Quote:
Hi
>
I Am am having problems with a stored Procedure that i wrote.
>
Basically whats happening is that the Stored procedure Runs fine when i
EXECUTE it in SQL Query analyzer.
>
But when i debug through the application in Visual Studio .NET 2003 the
application an exception when it executes the query.
>
I believe that the error is related to the itemQty filled in the table
variable @OrderItemQuantity because when i change the int given to the
itemQuantity the error message will display that number.
>
The error i am getting in . NET (and my browser) is the following:
>
--------------------------------------------------------------------------------------------------------------------------------------------------------
Syntax error converting the varchar value ''1'' to a column of data
type int.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
>
Exception Details: System.Data.SqlClient.SqlException: Syntax error
converting the varchar value ''1'' to a column of data type int.
>
Source Error:
>
>
Line 109: catch(Exception oException)
Line 110: {
Line 111: throw oException;
Line 112: }
Line 113: finally
>
>
---------------------------------------------------------------------------------------------------------------------------------------------------------
>
The stored Procedure is as follows:
>
>
---------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>
>
>
>
/************************************************** ************************************************** ****
>
* 0 variables from the code
>
************************************************** ************************************************** ****/
>
>
@UserID int,
@CartItems varchar (1000),
@CartItemQuantities varchar (1000),
@DeliveryDirections varchar (3000),
@OrderInstructions varchar (3000),
@OrderIDOutput int OUTPUT
>
>
AS
>
/************************************************** ************************************************** ****
>
* 1 sproc variables
>
************************************************** ************************************************** ****/
>
declare @SQL varchar(5000)
declare @OrderID varchar(1000)
declare @ErrorNumber int
>
>
/************************************************** ************************************************** ****
>
* 2 create a temp table that matches the itemid to the quantity
>
************************************************** ************************************************** ****/
>
declare @OrderItemQuantity table (itemID int, itemQty int)
insert into @OrderItemQuantity
select cast(FS1.Value as int) as 'itemID', cast(FS2.Value as int) as
'itemQty'
from fnsplitter(@CartItems) FS1 join
fnsplitter(@CartItemQuantities) FS2
on FS1.rowNum = FS2.rowNum
>
>
>
/************************************************** ************************************************** ****
>
* 4 create a temp table for calculating the sale prices
>
************************************************** ************************************************** ****/
>
create table #Tbl_SalePrice (
ItemId int,
ChargePerItem money,
SaleType Varchar(50),
SaleValue Money,
ItemSaleCharge money)
>
/************************************************** ************************************************** ****
>
* 5 create a temp table for the order details
>
************************************************** ************************************************** ****/
>
create table #OrderItemList (
ItemId int,
ChargePerItem money,
Quantity int,
ItemTotalCharge money
)
>
>
>
BEGIN TRAN -- begin the tran--------------------
>
>
>
/************************************************** ************************************************** ****
>
* 6 popluate the temp table with the itemids & charge per item
>
************************************************** ************************************************** ****/
>
set @SQL = 'insert into #OrderItemList (ItemId, ChargePerItem) '
set @SQL = @SQL + 'select '
set @SQL = @SQL + '[IF].itemId, '
set @SQL = @SQL + 'cast(IF2.FeatureValue as money) '
set @SQL = @SQL + 'from dbo.ectItemFeature [IF] '
set @SQL = @SQL + 'join dbo.ectItemFeature IF2 '
set @SQL = @SQL + 'on [IF].itemId = IF2.itemId '
set @SQL = @SQL + 'where [IF].itemId in ('+@CartItems+') '
set @SQL = @SQL + 'and IF2.FeatureID = 9 '
set @SQL = @SQL + 'and [IF].FeatureID = 9 '
--set @SQL = @SQL + 'order by [IF].itemid '
>
--print @SQL
>
exec (@SQL)
>
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured in 6: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
/************************************************** ************************************************** ****
>
* 7 Cursor to apply the sale where applicable
>
************************************************** ************************************************** ****/
--is there a sale??----------------------------------
--i wanted to create a function to apply the sale but couldnt
--do an exec(@SQL1) within a function!
>
>
declare @SQL1 varchar(5000)
>
--popluate the temp table with the itemids & charge per item
set @SQL1 = 'insert into #Tbl_SalePrice (ItemId, ChargePerItem) '
set @SQL1 = @SQL1 + 'select '
set @SQL1 = @SQL1 + '[IF].itemId, '
set @SQL1 = @SQL1 + 'cast(IF2.FeatureValue as money) '
set @SQL1 = @SQL1 + 'from dbo.ectItemFeature [IF] '
set @SQL1 = @SQL1 + 'join dbo.ectItemFeature IF2 '
set @SQL1 = @SQL1 + 'on [IF].itemId = IF2.itemId '
set @SQL1 = @SQL1 + 'where [IF].itemId in ('+@CartItems+') '
set @SQL1 = @SQL1 + 'and IF2.FeatureID = 9 '
set @SQL1 = @SQL1 + 'and [IF].FeatureID = 9 '
--set @SQL = @SQL + 'order by [IF].itemid '
>
--print @SQL
>
exec (@SQL1)
>
>
--check if theres a saletype and value against the items populate
tempTable
update #Tbl_SalePrice
set #Tbl_SalePrice.SaleType = (
select ST.saleType from dbo.ectSaleType ST
join dbo.ectSale S with (nolock)
on S.saleTypeID = ST.saleTypeID
join dbo.ectSaleItems SI with (nolock)
on S.saleID = SI .saleID
where SI.ItemId = #Tbl_SalePrice.ItemId
)
>
update #Tbl_SalePrice
set #Tbl_SalePrice.SaleValue = (
select S.saleAmountOff from dbo.ectSale S
join dbo.ectSaleItems SI with (nolock)
on S.saleID = SI .saleID
where SI.ItemId = #Tbl_SalePrice.ItemId
)
>
--run a cursor over the items to calculate the ItemSaleCharge
>
declare @ItemID int
declare SaleCalcCursor CURSOR for
select distinct(ItemId) from #Tbl_SalePrice
>
open SaleCalcCursor
>
fetch next from SaleCalcCursor
INTO @ItemID
>
WHILE @@FETCH_STATUS = 0
BEGIN
declare @SaleType varchar(50)
select @SaleType = SaleType From #Tbl_SalePrice WHERE ItemId =
@ItemID
>
>
if(@SaleType = 'MONEYOFF')
BEGIN
update #Tbl_SalePrice
set ItemSaleCharge = (ChargePeritem - SaleValue)
END
>
if(@SaleType = 'PERCENTOFF')
BEGIN
update #Tbl_SalePrice
set ItemSaleCharge = (ChargePeritem - ((ChargePeritem / 100) *
SaleValue))
END
>
--Can add the rest of the sale types here as the retailer wants
them
>
FETCH NEXT FROM SaleCalcCursor INTO @ItemID
END
close SaleCalcCursor
>
deallocate SaleCalcCursor
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured in 7: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
/************************************************** ************************************************** ****
>
* 8 Populate the temp table with the quantities
>
************************************************** ************************************************** ****/
>
update #OrderItemList
set #OrderItemList.Quantity = (
select OIQ.itemQty from @OrderItemQuantity OIQ
where OIQ.ItemId = #OrderItemList.ItemId
)
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured 8: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
>
>
/************************************************** ************************************************** ****
>
* 9 update the price of the items that are on sale
>
************************************************** ************************************************** ****/
>
update #OrderItemList
set #OrderItemList.ChargePerItem = (
select TSP.ItemSaleCharge from #Tbl_SalePrice TSP
where TSP.ItemId = #OrderItemList.ItemId
and TSP.ItemSaleCharge IS NOT NULL
)
where #OrderItemList.ItemId in (
select TSP.ItemId from #Tbl_SalePrice TSP
where TSP.ItemSaleCharge IS NOT NULL
)
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured 9: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
/************************************************** ************************************************** ****
>
* 10 calculate the total cost for each item * quantity
>
************************************************** ************************************************** ****/
>
update #OrderItemList
set ItemTotalCharge = (select (ChargePerItem * Quantity))
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured in 10: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
>
/************************************************** ************************************************** ****
>
* 11 insert the order into the Order table.
>
************************************************** ************************************************** ****/
>
insert into
dbo.omtOrder(customerID,orderDate,firstName,surNam e,emailAddress,Address1,Address2,Address3,City,Cou nty,Country,Phone,Mobile,paymentID,statusID,status Date,itemsCharge,deliveryCharge,totalCharge,orderN ote,deliveryNote,deliveryDate,createDate,modifyDat e)
select
@UserID,getdate(),U.firstName,U.surName,U.emailAdd ress,UBA.Address1,UBA.Address2,UBA.Address3,UBA.Ci ty,UBA.County,UBA.Country,UBA.Phone,UBA.Mobile,1,1 ,getdate(),
(Select sum(ItemTotalCharge) from #OrderItemList),
CONVERT(money,DF.deliveryCharge) ,(select(Select sum(ItemTotalCharge)
from #OrderItemList) + CONVERT(money, DF.deliveryCharge)),
@OrderInstructions, @DeliveryDirections, getdate(), getdate(),
getdate()
from dbo.ectUser U with (nolock) join dbo.ectUserBillAddress UBA with
(nolock)
on U.customerID = UBA.customerID
join dbo.ectCountry C with (nolock)
on UBA.Country = C.Country
join dbo.ectDeliveryFee DF with (nolock)
on C.countryID = DF.countryID
where U.customerID = 1
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured in 11: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
/************************************************** ************************************************** ****
>
* 12 get the orderid for the order just placed
>
************************************************** ************************************************** ****/
>
select @OrderID = max(orderID) from dbo.omtOrder O where O.customerID
= @UserID
>
>
>
/************************************************** ************************************************** ****
>
* 13 insert the order items into the Order items Table.
>
************************************************** ************************************************** ****/
>
>
insert into
dbo.omtOrderItems(orderID,ItemId,itemName,customer ID,categoryID,statusID,statusDate,quantity,charge, createDate,modifyDate)
select @OrderID, OIL.ItemId, EIF.featureValue, @UserID, CI.categoryId,
1, getdate(), OIL.Quantity, OIL.ItemTotalCharge, getdate(),getdate()
from #OrderItemList OIL join dbo.ectItemFeature EIF with (nolock)
on OIL.ItemId = EIF.ItemId
join dbo.ectCategoryItem CI with (nolock)
on OIL.ItemId = CI.ItemId
where EIF.featureId = 1 --1 is the item name
>
>
-- Check for errors
SET @ErrorNumber= @@ERROR
>
IF (@ErrorNumber <0 )
BEGIN
PRINT 'Error Occured in 13: '
PRINT @@ERROR
ROLLBACK TRAN
RETURN
END
>
>
/************************************************** ************************************************** ****
>
* 14 Drop the temp tables
>
************************************************** ************************************************** ****/
>
drop table #OrderItemList
drop table #Tbl_SalePrice
>
>
>
/************************************************** ************************************************** ****
>
* 15 Select the orderid for to return to the customer
>
************************************************** ************************************************** ****/
>
>
select @OrderIDOutput = cast(max(orderID) as int) from dbo.omtOrder O
where O.customerID = @UserID
>
COMMIT TRANSACTION
>
>
>
GO
>
>
----------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
PLEASE NOTE @CartItems expects a string of ids like '1,2,3,4'
ALSO @CartItemQuantities expects a string of quantities like '4,4,5,5'
>
>
I understand that this sql can probably be refactored but i will be
interested in that after i fix the problem.
>
The stored procedure uses the following fnSplitter that i found that
was kindly shared on the internet:
>
>
----------------------------------------------------------------------------------------------------------------------------------------------------
>
>
ALTER Function [dbo].[fnSplitter] (@IDs Varchar(4000))
Returns @Tbl_IDs Table (rowNum int, value varchar(1000)) As
Begin
-- Append comma
Set @IDs = @IDs + ','
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
>
declare @rownum int
>
-- Start from first character
Set @Pos1=1
Set @Pos2=1
>
-- row number
Set @rownum=1
>
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select @rownum,
cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) as int)
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = @Pos1+1
--increment the rownumber
Set @rownum= @rownum+1
End
Return
End
>
>
---------------------------------------------------------------------------------------------------------------------------------
>
>
Any help would be appreciated as im completely lost as to how it can
work fine in query analyzer but then cause an exception in .net
