Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Can't Raise Error on my VB coding

Author  Topic 

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2004-10-17 : 20:50:15
Good day all members in here...

You know i had a problem on my stored procedure SQL transaction querry.
But first allow me to show my querry...


---------------------------MY STORED PROC-------------------------------

CREATE PROCEDURE [dbo].[sp_ProcessStockIN]
@TODAY AS DATETIME, @vsiCategory as varchar(50),
@vsiDate as datetime, @vsiDRNo as varchar(30),
@vsiORNo as varchar(30), @vsiSupplierRep as varchar(50),
@vsiSupplierCode as char(10), @vsiP_UOM as char(10),
@vsiP_Quantity as int, @vsiP_Price as decimal(8,2),
@vsiQTYperUOM as int, @vsiMarkedUpPrice as decimal(8,2),
@vsiDiscount as decimal(8,2), @vsiSellingPrice as decimal(8,2),
@vsiS_UOM as char(10), @vsiS_Quantity as int,
@vsiCreatedBy as varchar(30), @vsiCreatedDate as datetime,
@vItem as varchar(50), @Barcode as char(50)
AS

BEGIN TRANSACTION sp_ProcessStockIN WITH Mark
--SET NOCOUNT OFF
-- BEGIN(-1)--Record if its a new Category---------------------------------------
DECLARE @CategoryExist as int

SET @CategoryExist = (SELECT COUNT(*) FROM tblCategory WHERE catCategory = @vsiCategory)
IF @CategoryExist = 0
BEGIN
INSERT INTO tblCategory (catCategory) VALUES (@vsiCategory)
END
IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in updating the category table', 16, 1)
ROLLBACK TRANSACTION
--RETURN -104
END
-- END(-1)--------------------------------------------------------------------------------

-- BEGIN(0)--Record if its a new stock-----------------------------------------
DECLARE @StockItemInTableStock as int
DECLARE @TheStockCode as char(10)

SET @StockItemInTableStock = (SELECT COUNT(*) FROM tblStocks
WHERE Category = @vsiCategory AND
Item = @vItem AND UOM = @vsiS_UOM)
IF @StockItemInTableStock > 0
BEGIN
SET @TheStockCode = (SELECT ItemCode FROM tblStocks
WHERE Category = @vsiCategory AND
Item = @vItem AND UOM = @vsiS_UOM)
END
ELSE
BEGIN
SET @TheStockCode = (SELECT dbo.fn_GenStockItemCode(@TODAY))

INSERT INTO tblStocks (ItemCode, Barcode, Category, Item, UOM, Short_ab)
VALUES(@TheStockCode, @Barcode, @vsiCategory, @vItem, @vsiS_UOM, '')
END

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in updating the new stock', 16, 1)
ROLLBACK TRANSACTION
--RETURN -100
END
-- END(0)-------------------------------------------------------------------------------

-- BEGIN(1) This routine writes Stocks IN in tblStockMasterIn--------------------
DECLARE @TheTransInCode as char(10)

SET @TheTransInCode = (select dbo.fn_GenTransInCode(@TODAY))

INSERT INTO tblStockMasterIn(siTransInCode, siCategory,
siStockCode, siDate,
siDRNo, siORNo,
siSupplierRep, siSupplierCode,
siP_UOM, siP_Quantity,
siP_Price, siQTYperUOM,
siMarkedUpPrice, siDiscount,
siSellingPrice, siS_UOM,
siS_Quantity, siCreatedBy, siCreatedDate)
VALUES (@TheTransInCode, @vsiCategory,
@TheStockCode, @vsiDate,
@vsiDRNo, @vsiORNo,
@vsiSupplierRep, @vsiSupplierCode,
@vsiP_UOM, @vsiP_Quantity,
@vsiP_Price, @vsiQTYperUOM,
@vsiMarkedUpPrice, @vsiDiscount,
@vsiSellingPrice, @vsiS_UOM,
@vsiS_Quantity, @vsiCreatedBy, @vsiCreatedDate)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in updating the Stock in', 16, 1)
ROLLBACK TRANSACTION
--RETURN -101
END
-- END(1)-------------------------------------------------------------------------------

-- BEGIN(2)---This routine will queue the stock on FIFO basis-------------------
DECLARE @QueCode as char(10)
DECLARE @QueNum as int

SET @QueCode = (SELECT dbo.fn_GenQueueCode(@Today))
SET @QueNum = (SELECT MAX(qPriority) FROM tblStockQueue
WHERE qStockCode = @TheStockCode and qVirtualIsZero = 0)

IF @QueNum is null
BEGIN SET @QueNum = 1 END
ELSE
BEGIN SET @QueNum = (@QueNum + 1) END

INSERT INTO tblStockQueue (qQueueCode, qTransInCode,
qPriority, qStockCode,
qQuantity, qPrice, qVirtualQuantity)
VALUES(@QueCode, @TheTransInCode,
@QueNum, @TheStockCode,
@vsiS_Quantity, @vsiSellingPrice, @vsiS_Quantity)

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in updating the Stock queuing', 16, 1)
ROLLBACK TRANSACTION
--RETURN -102
END
-- END(2)-------------------------------------------------------------------------------

-- BEGIN(3)---Write to the Stocks Master File--------------------------------
DECLARE @StockIsFound as int

SET @StockIsFound = (SELECT COUNT(smStockCode) FROM tblStockMaster
WHERE smStockCode = @TheStockCode)
IF @StockIsFound > 0
BEGIN
UPDATE tblStockMaster SET smQuantity = (smQuantity + @vsiS_Quantity)
WHERE smStockCode = @TheStockCode
END
ELSE
BEGIN
INSERT INTO tblStockMaster (smStockCode, smQuantity, smVirtualPrice)
VALUES (@TheStockCode, @vsiS_Quantity, '')
END
IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in updating the StockMaster', 16, 1)
ROLLBACK TRANSACTION
--RETURN -103
END
-- END(3)-------------------------------------------------------------------------------
--SET NOCOUNT ON
COMMIT

GO

--------------------------END OF MY STORED PROC-----------------------


On the last part of my querry particularly this...

BEGIN
INSERT INTO tblStockMaster (smStockCode, smQuantity, smVirtualPrice)
VALUES (@TheStockCode, @vsiS_Quantity, '')
END

I intentionally write this code to produce an error so that i can catch it up on my VB.
the error is this...

Server: Msg 8114, Level 16, State 5, Procedure sp_ProcessStockIN, Line 140
Error converting data type varchar to numeric.

The reason for this error is because of the no value "''" I assigned to the querry.
But on my VB coding? As I try to execute the stored proc, it does not raise up an error!
Is there anyone here can give a help why this one happened?

Any answers will be much appreciated. Thnx...







Want Philippines to become 1st World COuntry? Go for World War 3...

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2004-10-17 : 22:00:06
What version of vb are you using vb6,vb.net ??
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2004-10-17 : 22:05:00
Im using VB6 by the way. tnx for the reply.

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2004-10-17 : 22:30:38
You have mentioned that vb does not raise an error. What does vb do when you make the call to the proc?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-10-17 : 22:35:41
I don't think that error will be raised in the VB error object. It should appear in the Errors collection of your ADO Connection object.
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2004-10-17 : 23:15:21
Hello ehorn...

As I execute the stored procedure?
SQL = "exec sp_ProcessStockIN 'blah', 'blah', 'blah'"
con.Execute SQL, recAffected

recAffected returns 1. Wherein in fact the transaction has been roll back and when i see my DB? nothing happens to the records, its still on the same as it is.

I try to execute the stored proc on my querry analyzer...

EXEC dbo.sp_ProcessStockIN '10/11/2004', 'BEVERAGES', '10/11/2004', 'DR555', 'OR555', 'Nestle Foods Incorporation', 'A2004J4000', 'Box', 10, 42.5, 10, 10, 0, 5.5, 'pc', 100, 'Canteen User', '10/11/2004 4:24:02 PM', 'Sunglo', '555'

here what happen...
(1 row(s) affected)


(1 row(s) affected)

Server: Msg 8114, Level 16, State 5, Procedure sp_ProcessStockIN, Line 142
Error converting data type varchar to numeric.



Hello robvolk...
I appreciate your comment, but i try a simple stored proc.

CREATE PROCEDURE [dbo].[sp_Testing]
@QTY as int,
AS
INSERT INTO tblTest (myQTY) VALUES('')

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error in updating', 16, 1)
ROLLBACK TRANSACTION
END
GO

Why the error on this one raise up?

Tnx guys...


Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-10-18 : 00:04:07
try it with
set nocount on
at the start of the sp.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2004-10-18 : 01:09:32
Thnx nr....

Thnx for the help. Finally the problem was solve. I put set nocount on at the start of my Stored proc.

Thnx also to ehorn and robvolk for their response on this post.

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site