473,842 Members | 1,372 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored procedure exception in .NET works fine in Query Analyzer

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 @OrderItemQuant ity 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.Sql Client.SqlExcep tion: 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),
@CartItemQuanti ties varchar (1000),
@DeliveryDirect ions varchar (3000),
@OrderInstructi ons 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 @OrderItemQuant ity table (itemID int, itemQty int)
insert into @OrderItemQuant ity
select cast(FS1.Value as int) as 'itemID', cast(FS2.Value as int) as
'itemQty'
from fnsplitter(@Car tItems) FS1 join
fnsplitter(@Car tItemQuantities ) 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.Featu reValue as money) '
set @SQL = @SQL + 'from dbo.ectItemFeat ure [IF] '
set @SQL = @SQL + 'join dbo.ectItemFeat ure 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.Featu reValue as money) '
set @SQL1 = @SQL1 + 'from dbo.ectItemFeat ure [IF] '
set @SQL1 = @SQL1 + 'join dbo.ectItemFeat ure 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.ectSaleItem s 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.ectSaleItem s 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 @OrderItemQuant ity 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.ItemSaleCha rge from #Tbl_SalePrice TSP
where TSP.ItemId = #OrderItemList. ItemId
and TSP.ItemSaleCha rge IS NOT NULL
)
where #OrderItemList. ItemId in (
select TSP.ItemId from #Tbl_SalePrice TSP
where TSP.ItemSaleCha rge 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(cu stomerID,orderD ate,firstName,s urName,emailAdd ress,Address1,A ddress2,Address 3,City,County,C ountry,Phone,Mo bile,paymentID, statusID,status Date,itemsCharg e,deliveryCharg e,totalCharge,o rderNote,delive ryNote,delivery Date,createDate ,modifyDate)
select
@UserID,getdate (),U.firstName, U.surName,U.ema ilAddress,UBA.A ddress1,UBA.Add ress2,UBA.Addre ss3,UBA.City,UB A.County,UBA.Co untry,UBA.Phone ,UBA.Mobile,1,1 ,getdate(),
(Select sum(ItemTotalCh arge) from #OrderItemList) ,
CONVERT(money,D F.deliveryCharg e) ,(select(Select sum(ItemTotalCh arge)
from #OrderItemList) + CONVERT(money, DF.deliveryChar ge)),
@OrderInstructi ons, @DeliveryDirect ions, getdate(), getdate(),
getdate()
from dbo.ectUser U with (nolock) join dbo.ectUserBill Address UBA with
(nolock)
on U.customerID = UBA.customerID
join dbo.ectCountry C with (nolock)
on UBA.Country = C.Country
join dbo.ectDelivery Fee 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.omtOrderIte ms(orderID,Item Id,itemName,cus tomerID,categor yID,statusID,st atusDate,quanti ty,charge,creat eDate,modifyDat e)
select @OrderID, OIL.ItemId, EIF.featureValu e, @UserID, CI.categoryId,
1, getdate(), OIL.Quantity, OIL.ItemTotalCh arge, getdate(),getda te()
from #OrderItemList OIL join dbo.ectItemFeat ure EIF with (nolock)
on OIL.ItemId = EIF.ItemId
join dbo.ectCategory Item 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(orderI D) 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 @CartItemQuanti ties 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,@Pos 1-@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

Jan 23 '07 #1
9 2473
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 @OrderItemQuant ity 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.Sql Client.SqlExcep tion: 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*******@googl email.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version
Jan 23 '07 #2
Thanks, the C# is
string orderID = "";
//Connection and Command Objects
SqlConnection oConnection = GetSQLConnectio n();
SqlCommand oCommand = new SqlCommand();

// Command Initialisation
oCommand.Connec tion = oConnection;
oCommand.Comman dText = "TestCreateOrde r";
oCommand.Comman dType = CommandType.Sto redProcedure;

// Inputs
oCommand.Parame ters.Add("@User ID", SqlDbType.Int);
oCommand.Parame ters["@UserID"].Direction = ParameterDirect ion.Input;
oCommand.Parame ters["@UserID"].Value = UserID;

oCommand.Parame ters.Add("@Cart Items", SqlDbType.VarCh ar);
oCommand.Parame ters["@CartItems "].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@CartItems "].Value = strCartItems;

oCommand.Parame ters.Add("@Cart ItemQuantities" , SqlDbType.VarCh ar);
oCommand.Parame ters["@CartItemQuant ities"].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@CartItemQuant ities"].Value =
strCartItemQuan tities;

oCommand.Parame ters.Add("@Deli veryDirections" , SqlDbType.VarCh ar);
oCommand.Parame ters["@DeliveryDirec tions"].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@DeliveryDirec tions"].Value =
strDeliveryDire ctions;

oCommand.Parame ters.Add("@Orde rInstructions", SqlDbType.VarCh ar);
oCommand.Parame ters["@OrderInstruct ions"].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@OrderInstruct ions"].Value =
strOrderInstruc tions;

oCommand.Parame ters.Add("@Orde rIDOutput", SqlDbType.Int);
oCommand.Parame ters["@OrderIDOutput "].Direction =
ParameterDirect ion.Output;
try
{
oConnection.Ope n();
oCommand.Execut eNonQuery();

//Get the orderId for the customer
orderID = oCommand.Parame ters["@@OrderIDOutpu t"].Value.ToString ();

return orderID;

}

catch(Exception oException)
{
throw oException;
}
finally
{
oConnection.Clo se();
oConnection.Dis pose();
oCommand.Dispos e();

}
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 @OrderItemQuant ity 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.Sql Client.SqlExcep tion: 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*******@googl email.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version
Jan 23 '07 #3
Its hard to say without seeing your actual code, but just looking at
the exception:
Is it possible that the parameter you pass as @OrderItemQuant ity 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 @OrderItemQuant ity 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.Sql Client.SqlExcep tion: 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),
@CartItemQuanti ties varchar (1000),
@DeliveryDirect ions varchar (3000),
@OrderInstructi ons 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 @OrderItemQuant ity table (itemID int, itemQty int)
insert into @OrderItemQuant ity
select cast(FS1.Value as int) as 'itemID', cast(FS2.Value as int) as
'itemQty'
from fnsplitter(@Car tItems) FS1 join
fnsplitter(@Car tItemQuantities ) 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.Featu reValue as money) '
set @SQL = @SQL + 'from dbo.ectItemFeat ure [IF] '
set @SQL = @SQL + 'join dbo.ectItemFeat ure 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.Featu reValue as money) '
set @SQL1 = @SQL1 + 'from dbo.ectItemFeat ure [IF] '
set @SQL1 = @SQL1 + 'join dbo.ectItemFeat ure 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.ectSaleItem s 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.ectSaleItem s 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 @OrderItemQuant ity 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.ItemSaleCha rge from #Tbl_SalePrice TSP
where TSP.ItemId = #OrderItemList. ItemId
and TSP.ItemSaleCha rge IS NOT NULL
)
where #OrderItemList. ItemId in (
select TSP.ItemId from #Tbl_SalePrice TSP
where TSP.ItemSaleCha rge 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(cu stomerID,orderD ate,firstName,s urName,emailAdd ress,Address1,A ddress2,Address 3,City,County,C ountry,Phone,Mo bile,paymentID, statusID,status Date,itemsCharg e,deliveryCharg e,totalCharge,o rderNote,delive ryNote,delivery Date,createDate ,modifyDate)
select
@UserID,getdate (),U.firstName, U.surName,U.ema ilAddress,UBA.A ddress1,UBA.Add ress2,UBA.Addre ss3,UBA.City,UB A.County,UBA.Co untry,UBA.Phone ,UBA.Mobile,1,1 ,getdate(),
(Select sum(ItemTotalCh arge) from #OrderItemList) ,
CONVERT(money,D F.deliveryCharg e) ,(select(Select sum(ItemTotalCh arge)
from #OrderItemList) + CONVERT(money, DF.deliveryChar ge)),
@OrderInstructi ons, @DeliveryDirect ions, getdate(), getdate(),
getdate()
from dbo.ectUser U with (nolock) join dbo.ectUserBill Address UBA with
(nolock)
on U.customerID = UBA.customerID
join dbo.ectCountry C with (nolock)
on UBA.Country = C.Country
join dbo.ectDelivery Fee 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.omtOrderIte ms(orderID,Item Id,itemName,cus tomerID,categor yID,statusID,st atusDate,quanti ty,charge,creat eDate,modifyDat e)
select @OrderID, OIL.ItemId, EIF.featureValu e, @UserID, CI.categoryId,
1, getdate(), OIL.Quantity, OIL.ItemTotalCh arge, getdate(),getda te()
from #OrderItemList OIL join dbo.ectItemFeat ure EIF with (nolock)
on OIL.ItemId = EIF.ItemId
join dbo.ectCategory Item 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(orderI D) 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 @CartItemQuanti ties 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,@Pos 1-@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
Jan 23 '07 #4
>oCommand.Param eters.Add("@Car tItemQuantities ", SqlDbType.VarCh ar)

Is that your problem? Your CartItemQuantit ies parameter is a VarChar.
Shouldn't it by an int?
Peter
"Frawls" <fr****@gmail.c omwrote in message
news:11******** **************@ a34g2000cwb.goo glegroups.com.. .
Thanks, the C# is
string orderID = "";
//Connection and Command Objects
SqlConnection oConnection = GetSQLConnectio n();
SqlCommand oCommand = new SqlCommand();

// Command Initialisation
oCommand.Connec tion = oConnection;
oCommand.Comman dText = "TestCreateOrde r";
oCommand.Comman dType = CommandType.Sto redProcedure;

// Inputs
oCommand.Parame ters.Add("@User ID", SqlDbType.Int);
oCommand.Parame ters["@UserID"].Direction = ParameterDirect ion.Input;
oCommand.Parame ters["@UserID"].Value = UserID;

oCommand.Parame ters.Add("@Cart Items", SqlDbType.VarCh ar);
oCommand.Parame ters["@CartItems "].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@CartItems "].Value = strCartItems;

oCommand.Parame ters.Add("@Cart ItemQuantities" , SqlDbType.VarCh ar);
oCommand.Parame ters["@CartItemQuant ities"].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@CartItemQuant ities"].Value =
strCartItemQuan tities;

oCommand.Parame ters.Add("@Deli veryDirections" , SqlDbType.VarCh ar);
oCommand.Parame ters["@DeliveryDirec tions"].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@DeliveryDirec tions"].Value =
strDeliveryDire ctions;

oCommand.Parame ters.Add("@Orde rInstructions", SqlDbType.VarCh ar);
oCommand.Parame ters["@OrderInstruct ions"].Direction =
ParameterDirect ion.Input;
oCommand.Parame ters["@OrderInstruct ions"].Value =
strOrderInstruc tions;

oCommand.Parame ters.Add("@Orde rIDOutput", SqlDbType.Int);
oCommand.Parame ters["@OrderIDOutput "].Direction =
ParameterDirect ion.Output;
try
{
oConnection.Ope n();
oCommand.Execut eNonQuery();

//Get the orderId for the customer
orderID = oCommand.Parame ters["@@OrderIDOutpu t"].Value.ToString ();

return orderID;

}

catch(Exception oException)
{
throw oException;
}
finally
{
oConnection.Clo se();
oConnection.Dis pose();
oCommand.Dispos e();

}
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 @OrderItemQuant ity 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.Sql Client.SqlExcep tion: 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*******@googl email.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version

Jan 23 '07 #5
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
Jan 23 '07 #6
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.c omwrote in message
news:11******** **************@ a34g2000cwb.goo glegroups.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 @OrderItemQuant ity 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.Sql Client.SqlExcep tion: 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),
| @CartItemQuanti ties varchar (1000),
| @DeliveryDirect ions varchar (3000),
| @OrderInstructi ons 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 @OrderItemQuant ity table (itemID int, itemQty int)
| insert into @OrderItemQuant ity
| select cast(FS1.Value as int) as 'itemID', cast(FS2.Value as int) as
| 'itemQty'
| from fnsplitter(@Car tItems) FS1 join
| fnsplitter(@Car tItemQuantities ) 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.Featu reValue as money) '
| set @SQL = @SQL + 'from dbo.ectItemFeat ure [IF] '
| set @SQL = @SQL + 'join dbo.ectItemFeat ure 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.Featu reValue as money) '
| set @SQL1 = @SQL1 + 'from dbo.ectItemFeat ure [IF] '
| set @SQL1 = @SQL1 + 'join dbo.ectItemFeat ure 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.ectSaleItem s 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.ectSaleItem s 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 @OrderItemQuant ity 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.ItemSaleCha rge from #Tbl_SalePrice TSP
| where TSP.ItemId = #OrderItemList. ItemId
| and TSP.ItemSaleCha rge IS NOT NULL
| )
| where #OrderItemList. ItemId in (
| select TSP.ItemId from #Tbl_SalePrice TSP
| where TSP.ItemSaleCha rge 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(cu stomerID,orderD ate,firstName,s urName,emailAdd ress,Address1,A ddress2,Address 3,City,County,C ountry,Phone,Mo bile,paymentID, statusID,status Date,itemsCharg e,deliveryCharg e,totalCharge,o rderNote,delive ryNote,delivery Date,createDate ,modifyDate)
| select
|
@UserID,getdate (),U.firstName, U.surName,U.ema ilAddress,UBA.A ddress1,UBA.Add ress2,UBA.Addre ss3,UBA.City,UB A.County,UBA.Co untry,UBA.Phone ,UBA.Mobile,1,1 ,getdate(),
| (Select sum(ItemTotalCh arge) from #OrderItemList) ,
| CONVERT(money,D F.deliveryCharg e) ,(select(Select sum(ItemTotalCh arge)
| from #OrderItemList) + CONVERT(money, DF.deliveryChar ge)),
| @OrderInstructi ons, @DeliveryDirect ions, getdate(), getdate(),
| getdate()
| from dbo.ectUser U with (nolock) join dbo.ectUserBill Address UBA with
| (nolock)
| on U.customerID = UBA.customerID
| join dbo.ectCountry C with (nolock)
| on UBA.Country = C.Country
| join dbo.ectDelivery Fee 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.omtOrderIte ms(orderID,Item Id,itemName,cus tomerID,categor yID,statusID,st atusDate,quanti ty,charge,creat eDate,modifyDat e)
| select @OrderID, OIL.ItemId, EIF.featureValu e, @UserID, CI.categoryId,
| 1, getdate(), OIL.Quantity, OIL.ItemTotalCh arge, getdate(),getda te()
| from #OrderItemList OIL join dbo.ectItemFeat ure EIF with (nolock)
| on OIL.ItemId = EIF.ItemId
| join dbo.ectCategory Item 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(orderI D) 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 @CartItemQuanti ties 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,@Pos 1-@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
|
Jan 23 '07 #7
<"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.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 23 '07 #8
Hi Jon,

Kudos for reading such a long email in details :)
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
| <"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.co m>
| http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
| If replying to the group, please do not mail me too
Jan 23 '07 #9
<"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.Sql Client.SqlExcep tion: 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.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 23 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
2321
by: Dragonhunter | last post by:
Hello, The aspfaq.com seems to really push stored procedures, and I hear the same advice here all the time. So I want to take the advice. Is it possible to create and practically maintain, delete, use, etc.. stored procedures soley from asp (i.e., no GUI or console- like being hosted on Brinkster)? The tutorial on aspfaq.com mentions that stored procedures can be
3
22155
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my application server can talk to the database. I've determined the failure occurs when the the following statement is executed: cstmt.execute(); (due to the failure of println statements placed afterwards). I get the following error after trying to...
5
7298
by: Warren Wright | last post by:
Hi group, I have a select statement that if run against a 1 million record database directly in query analyzer takes less than 1 second. However, if I execute the select statement in a stored procedure instead, calling the stored proc from query analyzer, then it takes 12-17 seconds. Here is what I execute in Query Analyzer when bypassing the stored procedure:
3
18612
by: Michael | last post by:
This one's really got me. I have a VB.NET (version 1.1.4322) project that provides an easy way to execute stored procedures on a generic level. When I run the code on computer A (running SQL Server 2000 version 08.00.0194) the code works great. However, computer B (running SQL Server 2000 version 08.00.0534) bombs when I try to execute the sproc saying 'Could not find stored procedure 'spmw_ReadByPage'. My thought process went as...
4
9564
by: shyner | last post by:
Hi Everyone, I've been battling this for two days with no luck. I'm using SQL Server 2000. Here's the mystery: I've got a stored procedure that takes a single varchar parameter to determine how the result set is sorted. Here it is: CREATE PROCEDURE spDemo @SortField varchar(30)
3
2545
by: Gary Besta | last post by:
I am trying to add a simple case statement to a stored procedure or user defined function. However when I try and save the function/procedure I get 2 syntax errors. Running the query in query analyser works fine and a result is given with no syntax errors. I believe its something to do with the spaces in the field names. Not my choice as its an existing system I have to work around. Any help greatly appreciated SQL Query
5
3666
by: Timppa | last post by:
Hi, Could anyone help me with my problem ? Environment: Access 2000 and Sql Server 2000. I have a stored procedure as follows: DROP table1 SELECT alias1.field1,alias2.field2,table2.field6 INTO table1
9
7672
by: vikram.mankar | last post by:
I have a stored procedure thats transferring/processing data from one table to two different tables. The destination tables have a unique value constraint as the source tables at times has duplicate records and this will prevent the duplicates from being reported. When the stored procedure (which includes a cursor) is executed through query analyzer, it runs fine, and reports an error everytime it sees a duplicate value (as expected). It...
1
2305
by: central_scrutinizer | last post by:
I have an ASP that has been working fine for several months, but it suddenly broke. I wonder if windows update has installed some security patch that is causing it. The problem is that I am calling a stored procedure via an ASP (classic, not .NET) , but nothing happens. The procedure doesn't work, and I don't get any error messages. I've tried dropping and re-creating the user and permissions, to no avail. If it was a permissions...
0
9876
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10952
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10618
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10683
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7862
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7045
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4096
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3148
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.