Printable View
Let's go for test #1 - the first sproc I posted.
What is the final line of that SPROC as I posted it?
What is the final line of that SPROC as you loaded it into your query?
If you would like - load it again - run it just once - only once and give me the results of that test.
One step at a time - please.
Ok here is the result:
2
Msg 266, Level 16, State 2, Procedure StockInTest1, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
and here is the SPROC
Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest1')
DROP PROCEDURE StockInTest1
GO
CREATE PROCEDURE StockInTest1
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
print @MaxSeq
-- INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
-- VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
-- IF @ProdCount <> 0
-- INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
-- VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
--IF @@ERROR = 0
-- COMMIT
--ELSE
-- ROLLBACK
Ok here is the 2nd result for the 2nd SPROC:
Msg 2627, Level 14, State 1, Procedure StockInTest3, Line 25
Violation of PRIMARY KEY constraint 'PK_tblStockMasterFile'. Cannot insert duplicate key in object 'dbo.tblStockMasterFile'.
The statement has been terminated.
(1 row(s) affected)
0
(2 row(s) affected)
Msg 266, Level 16, State 2, Procedure StockInTest3, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3.
and here is the SPROC
Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest2')
DROP PROCEDURE StockInTest2
GO
CREATE PROCEDURE StockInTest2
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
--print @MaxSeq
INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
select * from tblStockMasterFile Where PRoductId=@ProdId
-- IF @ProdCount <> 0
-- INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
-- VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
--IF @@ERROR = 0
-- COMMIT
--ELSE
-- ROLLBACK
and here is the result for the 3rd SPROC:
Msg 2627, Level 14, State 1, Procedure StockInTest3, Line 25
Violation of PRIMARY KEY constraint 'PK_tblStockMasterFile'. Cannot insert duplicate key in object 'dbo.tblStockMasterFile'.
The statement has been terminated.
(1 row(s) affected)
0
(3 row(s) affected)
Msg 266, Level 16, State 2, Procedure StockInTest3, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 4, current count = 5.
and here is the SPROC:
Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest3')
DROP PROCEDURE StockInTest3
GO
CREATE PROCEDURE StockInTest3
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
--print @MaxSeq
INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
--select * from tblStockMasterFile Where PRoductId=@ProdId
Declare @ErrorCode int
-- IF @ProdCount <> 0
INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
Set @ErrorCode=@@ERROR
print @ERRORCODE
select * From tblStockTransFile Where ProductId=@ProdId
--IF @ERRORCODE = 0
-- COMMIT
--ELSE
-- ROLLBACK
You are missing a line from what I posted - the final line - the most important one. I cannot help that what I posted filled more then the screen and the scroll bar appears. But to think that you ignored the scroll bar and didn't copy/paste the whole SPROC is a shame.Quote:
Originally Posted by Simply Me
The code as I posted it was:
Please be more careful - you are truly wasting your time - I can only imagine the frustration you must be feeling...Code:print @MaxSeq
-- INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
-- VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
-- IF @ProdCount <> 0
-- INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
-- VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
--IF @@ERROR = 0
-- COMMIT
--ELSE
-- ROLLBACK
ROLLBACK - temporarily rollback always for testing purposes
But it's got nothing to do with SQL or sprocs...
And you completely ignore what I asked
I was guessing you left out the final line - why do you think I ask a question like "what is the final line of the sproc"??Quote:
Originally Posted by szlamany
Arrgghh....
Error when I execute the Procedure:
Msg 102, Level 15, State 1, Procedure StockInTest1, Line 37
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Procedure StockInTest1, Line 37
Incorrect syntax near 'always'.
I used this Exec StockInTest1 '01','Tide','09-19-2007','bar',5,10,20Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest1')
DROP PROCEDURE StockInTest1
GO
CREATE PROCEDURE StockInTest1
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
print @MaxSeq
-- INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
-- VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
-- IF @ProdCount <> 0
-- INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
-- VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
--IF @@ERROR = 0
-- COMMIT
--ELSE
-- ROLLBACK
ROLLBACK - temporarily rollback always for testing purposes
Result
Code:Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'StockInTest1'.
The error message is pretty clear.Code:ROLLBACK - temporarily rollback always for testing purposes
The - sign is not valid
A comment is started with two -- signs
I should have posted
Sorry...Code:ROLLBACK -- temporarily rollback always for testing purposes
btw - if you double-click the error message in the result window the cursor will jump to the exact spot in the SPROC that has a syntax error.
ok. I run this code as well as use Exec StockInTest1 '03','Test','09-25-2007','bar',5,10,20 and there's no error came out. The message i got is "1"
ok. I run this code as well as use Exec StockInTest1 '03','Test','09-25-2007','bar',5,10,20 and there's no error came out. The message i got is "1"
Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest1')
DROP PROCEDURE StockInTest1
GO
CREATE PROCEDURE StockInTest1
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
print @MaxSeq
-- INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
-- VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
-- IF @ProdCount <> 0
-- INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
-- VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
--IF @@ERROR = 0
-- COMMIT
--ELSE
-- ROLLBACK
ROLLBACK -- temporarily rollback always for testing purposes
i run the 2nd code and there is no error and when I used EXEC one record is displayed in results tab
Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest2')
DROP PROCEDURE StockInTest2
GO
CREATE PROCEDURE StockInTest2
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
--print @MaxSeq
INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
select * from tblStockMasterFile Where PRoductId=@ProdId
-- IF @ProdCount <> 0
-- INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
-- VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
--IF @@ERROR = 0
-- COMMIT
--ELSE
-- ROLLBACK
ROLLBACK -- temporarily rollback always for testing purposes
i tried the third code and there was no error. I used EXEC and it gave me one record under the results tab this time more columns where displayed compare to the 2nd code.
Code:Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
WHERE name='StockInTest3')
DROP PROCEDURE StockInTest3
GO
CREATE PROCEDURE StockInTest3
@ProdID varchar(50),
@ProdName varchar(50),
@EntryDate datetime,
@ProdUnit varchar(50),
@ProdReorderPoint int,
@ProdPurchasePrice money,
@ProdQTY int
AS
DECLARE @MaxSEQ INT
DECLARE @ProdCount INT
BEGIN TRAN
-- SELECT @ProdCount = COUNT(ProductID)
-- FROM tblStockMasterFile
-- WHERE ProductID = @ProdID
SET @MaxSEQ=(SELECT IsNull(Max(pSequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
--print @MaxSeq
INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
--select * from tblStockMasterFile Where PRoductId=@ProdId
Declare @ErrorCode int
-- IF @ProdCount <> 0
INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence)
VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)
Set @ErrorCode=@@ERROR
print @ERRORCODE
select * From tblStockTransFile Where ProductId=@ProdId
--IF @ERRORCODE = 0
-- COMMIT
--ELSE
-- ROLLBACK
ROLLBACK -- temporarily rollback always for testing purposes
That seems to me like your sproc is now working - although it's got a ROLLBACK - so you can't see these results once the EXEC is over.
The SPROC does three very simple things.
Get's a "next sequence number to use"
Insert's into a parent table.
Insert's into a child table.
Anything else you want to discuss about this SPROC?? Is there anything else it needs to do??
Yes it insert's value to parent and child table. If I exec it twice I get error telling me that it tried saving to parent table again which is not suppose to be. So the SPROC is missing a code, where if the ProductID is existing in the parent table it should ONLY save in child table.
Well - you did have @ProdCount variable and and IF statement in your version of the SPROC. Of course the IF was on the wrong insert - right? You had the IF on the child table insert and not the master table insert.
If you want this INSERT
to be only done for a row that does not exist then check for thatCode:INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
Exists() is the proper function for this - and SELECT * is proper as well, since it really doesn't return anything. Exists() basically evaulates the WHERE clause of the SELECT within it and returns a nice true/false type value.Code:If Not Exists(Select * From tblStockMasterFile Where ProductId=@ProdId)
Begin
INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
End
Also - note that I'm showing you a BEGIN/END block - it's not required with a single statement IF - but if you were doing several things as a result of the IF you would need the BEGIN/END block.
Where do i insert the code in my SPROC?
Didn't I post your exact INSERT statement? Isn't that what you have in your SPROC right now?Quote:
Originally Posted by Simply Me
Change your INSERT statement to the IF/BEGIN/INSERT/END block that I posted.
If this does not make sense please post your current SPROC so I can help you further.
This is my code now.Next step is I will call this in vbvb Code:
You might want to remove these two lines - or at least comment them out with -- sign
Also - a little hint...Code:print @ERRORCODE
select * From tblStockTransFile Where ProductId=@ProdId
If you ever expect to do a SELECT from a SPROC that first does an INSERT or UPDATE. Or expect to use output parameters...
Then always put
SET NOCOUNT ON
at the top of the sproc. This suppresses the "Rows Affected" message from getting passed from sql to vb. That message gets in the way of processing recordsets and output parameters.