Stored procedure exception in .NET works fine in Query Analyzer

[Frawls]

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

[Larry Lard]

Frawls wrote:

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.

[snip]

Show us the C# code that *calls* the sproc.
--
Larry Lard
la*******@googlemail.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version

[Frawls]

Thanks, the C# is
string orderID = "";
//Connection and Command Objects
SqlConnection oConnection = GetSQLConnection();
SqlCommand oCommand = new SqlCommand();

// Command Initialisation
oCommand.Connection = oConnection;
oCommand.CommandText = "TestCreateOrder";
oCommand.CommandType = CommandType.StoredProcedure;

// Inputs
oCommand.Parameters.Add("@UserID", SqlDbType.Int);
oCommand.Parameters["@UserID"].Direction = ParameterDirection.Input;
oCommand.Parameters["@UserID"].Value = UserID;

oCommand.Parameters.Add("@CartItems", SqlDbType.VarChar);
oCommand.Parameters["@CartItems"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@CartItems"].Value = strCartItems;

oCommand.Parameters.Add("@CartItemQuantities", SqlDbType.VarChar);
oCommand.Parameters["@CartItemQuantities"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@CartItemQuantities"].Value =
strCartItemQuantities;

oCommand.Parameters.Add("@DeliveryDirections", SqlDbType.VarChar);
oCommand.Parameters["@DeliveryDirections"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@DeliveryDirections"].Value =
strDeliveryDirections;

oCommand.Parameters.Add("@OrderInstructions", SqlDbType.VarChar);
oCommand.Parameters["@OrderInstructions"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@OrderInstructions"].Value =
strOrderInstructions;

oCommand.Parameters.Add("@OrderIDOutput", SqlDbType.Int);
oCommand.Parameters["@OrderIDOutput"].Direction =
ParameterDirection.Output;
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();

//Get the orderId for the customer
orderID = oCommand.Parameters["@@OrderIDOutput"].Value.ToString();

return orderID;

}

catch(Exception oException)
{
throw oException;
}
finally
{
oConnection.Close();
oConnection.Dispose();
oCommand.Dispose();

}
Larry Lard wrote:

Frawls wrote:

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.

[snip]

Show us the C# code that *calls* the sproc.
--
Larry Lard
la*******@googlemail.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version

[KWienhold]

Its hard to say without seeing your actual code, but just looking at
the exception:
Is it possible that the parameter you pass as @OrderItemQuantity to
your procedure is of type varchar and should be of type int?
When you add Parameters to your OledbCommand/SqlCommand you have to set
their datatype, I'm not sure wether they default to varchar if you
don't, but it seems likely that this is causing your problem.

Sincerely,
Kevin Wienhold
Frawls schrieb:

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

[Peter Bradley]

>oCommand.Parameters.Add("@CartItemQuantities", SqlDbType.VarChar)

Is that your problem? Your CartItemQuantities parameter is a VarChar.
Shouldn't it by an int?
Peter
"Frawls" <fr****@gmail.comwrote in message
news:11**********************@a34g2000cwb.googlegr oups.com...

Thanks, the C# is
string orderID = "";
//Connection and Command Objects
SqlConnection oConnection = GetSQLConnection();
SqlCommand oCommand = new SqlCommand();

// Command Initialisation
oCommand.Connection = oConnection;
oCommand.CommandText = "TestCreateOrder";
oCommand.CommandType = CommandType.StoredProcedure;

// Inputs
oCommand.Parameters.Add("@UserID", SqlDbType.Int);
oCommand.Parameters["@UserID"].Direction = ParameterDirection.Input;
oCommand.Parameters["@UserID"].Value = UserID;

oCommand.Parameters.Add("@CartItems", SqlDbType.VarChar);
oCommand.Parameters["@CartItems"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@CartItems"].Value = strCartItems;

oCommand.Parameters.Add("@CartItemQuantities", SqlDbType.VarChar);
oCommand.Parameters["@CartItemQuantities"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@CartItemQuantities"].Value =
strCartItemQuantities;

oCommand.Parameters.Add("@DeliveryDirections", SqlDbType.VarChar);
oCommand.Parameters["@DeliveryDirections"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@DeliveryDirections"].Value =
strDeliveryDirections;

oCommand.Parameters.Add("@OrderInstructions", SqlDbType.VarChar);
oCommand.Parameters["@OrderInstructions"].Direction =
ParameterDirection.Input;
oCommand.Parameters["@OrderInstructions"].Value =
strOrderInstructions;

oCommand.Parameters.Add("@OrderIDOutput", SqlDbType.Int);
oCommand.Parameters["@OrderIDOutput"].Direction =
ParameterDirection.Output;
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();

//Get the orderId for the customer
orderID = oCommand.Parameters["@@OrderIDOutput"].Value.ToString();

return orderID;

}

catch(Exception oException)
{
throw oException;
}
finally
{
oConnection.Close();
oConnection.Dispose();
oCommand.Dispose();

}
Larry Lard wrote:

>Frawls wrote:

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.

[snip]

Show us the C# code that *calls* the sproc.
--
Larry Lard
la*******@googlemail.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version

[Marc Gravell]

This belongs in a SQL newsgroup;

However: it sounds like the problem of using varchar IDs and integer
variables, but you don't describe (and I am not especially interested
in) your SQL schema.

A few points, however: if you are only talking about .Net clients and
Sql-Server 2005, then there are a lot of things you can do to tweak
this code - in particular catching the error in C# and rolling back
(versus committing) an ADO.Net transaction (or easier: an LTM / DTC
transaction) rather than the 'orrible block of repeated code in the
SQL. Likewise table variables and avoiding cursors at the server.
Also - your dynamic SQL "EXEC(@SQL1)" is both a security risk unless
vightly controlled, plus it will force inner-recompilation as it
references an out-of-scope object (#Tbl_SalePrice). Personally I'd use
a UDF (like your fnSplitter) to turn @CartItems into a single-column
table-variable (based on a simple string functions to reproduce
SPLIT), then JOIN to the other table - neither dynamic SQL nor
injections. Given you already have the UDF, step 6 is currently just a
huge train-wreck waiting to happen.

Marc

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Posting such a long SP will do no help, people (I did) will barely look at
it.

The error is self explanatory though, You are treating a varchar containing
a number as an integer, you cannot do that.
Make sure to use CAST to convert from varchar to int
--
Ignacio Machin
machin AT laceupsolutions com

"Frawls" <fr****@gmail.comwrote in message
news:11**********************@a34g2000cwb.googlegr oups.com...
| 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
|

[Jon Skeet [C# MVP]]

<"Ignacio Machin \( .NET/ C# MVP \)" <machin TA laceupsolutions.com>>
wrote:

Posting such a long SP will do no help, people (I did) will barely look at
it.

The error is self explanatory though, You are treating a varchar containing
a number as an integer, you cannot do that.
Make sure to use CAST to convert from varchar to int

Ironically, although the error message is self-explanatory, by adding a
useless catch statement which then just rethrows the original
exception, the OP has lost the information about which line originally
threw the exception :(

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Ignacio Machin \( .NET/ C# MVP \)]

Hi Jon,

Kudos for reading such a long email in details :)
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
| <"Ignacio Machin \( .NET/ C# MVP \)" <machin TA laceupsolutions.com>>
| wrote:
| Posting such a long SP will do no help, people (I did) will barely look
at
| it.
| >
| The error is self explanatory though, You are treating a varchar
containing
| a number as an integer, you cannot do that.
| Make sure to use CAST to convert from varchar to int
|
| Ironically, although the error message is self-explanatory, by adding a
| useless catch statement which then just rethrows the original
| exception, the OP has lost the information about which line originally
| threw the exception :(
|
| --
| Jon Skeet - <sk***@pobox.com>
| http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
| If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

<"Ignacio Machin \( .NET/ C# MVP \)" <machin TA laceupsolutions.com>>
wrote:

Kudos for reading such a long email in details :)

I didn't - it was in the very first page:

<quote>
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

</quote>

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too