Sql Count

Re: Sql Count

Well - my only suggestion is for you to...

Put /* at the start of your SPROC and */ at the end of it.

That "comments out" the contents of the SPROC - so it basically does nothing.

From the first INSERT all the way through the RETURN

You might want to leave one line of code in the SPROC - something like SET @ERRORCODE=0.

With the contents of the SPROC commented out you can then put -- in from of each parameter - all except the first parameter. Put them all on separate lines first - that's neater.

With all the parameters except the first one commented out you then go into VB and comment out all but the first .CreateParameter line.

Now see if that runs - if it does you can happily accept that you've got clean code for parameter one.

Then uncomment out the next parameter - in the SPROC and in the VB code - see if it works.

Which one does it "die" on??

Re: Sql Count

I don know if im missing some point here. when you say

Quote:

With the contents of the SPROC commented out you can then put -- in from of each parameter - all except the first parameter. Put them all on separate lines first - that's neater.

its already commented right? why should I put "--". Im lost here

Re: Sql Count

i did this and when i run the project it says "could not find procedure StockIn"

vb Code:

Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
        WHERE name='StockIn')
    DROP PROCEDURE StockIn
GO
 
CREATE PROCEDURE StockIn
    @ProdID varchar(50),@ProdName varchar(50),
    @EntryDate datetime,@ProdUnit varchar(50),
    @ProdReorderPoint int,
    @ProdPurchasePrice money, @ProdQTY int)
AS
DECLARE @ErrorCode int
/*
INSERT INTO tblStockMasterFile(ProductID,ProductName,Unit,ReOrderPoint)
    VALUES(@ProdID,@ProdName,@ProdUnit,@ProdReOrderPoint)
 
DECLARE @MaxSEQ int
SET @MaxSEQ=(SELECT IsNull(Max(Sequence),0 )+ 1
    FROM tblStockTransFile
        WHERE ProductID= @ProdID AND EntryDate=@EntryDate)
 
INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity,Reason,Sequence)
    VALUES(@ProdID,@EntryDate,@ProdPurchasePrice,@ProdQTY,'In',@MaxSEQ)
 
 
SET @ErrorCode=@@ERROR
IF (@ErrorCode=0)
    RETURN (0)
ELSE
    RETURN (@ErrorCode)
*/
SET @ERRORCODE=0

vb Code:

With CMD
      .CommandText = "[StockIn]"
      .ActiveConnection = oConn
      .CommandType = adCmdStoredProc
    
      .Parameters.Append .CreateParameter("@ProdID", adVarChar, adParamInput, 50, txtProdID.Text)
      '.Parameters.Append .CreateParameter("@ProdName", adVarChar, adParamInput, 50, txtProdName.Text)
      '.Parameters.Append .CreateParameter("@EntryDate", adDate, adParamInput, , Format$(DTEntryDate.Value, "mm/dd/yy"))
      '.Parameters.Append .CreateParameter("@ProdUnit", adVarChar, adParamInput, 50, txtProdUnit.Text)
      '.Parameters.Append .CreateParameter("@ProdReorderPoint", adInteger, adParamInput, , txtProdReOrderPoint.Text)
      '.Parameters.Append .CreateParameter("@ProdPurchasePrice", adCurrency, adParamInput, , txtProdUnit.Text)
      '.Parameters.Append .CreateParameter("@ProdQTY", adInteger, adParamInput, , txtProdQTY.Text)
      Set rsStock = .Execute()
    End With

Re: Sql Count

Is the SPROC in the DB? You can verify that by opening the programability branch in Management Studio.

Change this:

Code:

CREATE PROCEDURE StockIn @ProdID varchar(50),@ProdName varchar(50), @EntryDate datetime,@ProdUnit varchar(50), @ProdReorderPoint int, @ProdPurchasePrice money, @ProdQTY int)

To this

Code:

CREATE PROCEDURE StockIn @ProdID varchar(50) --,@ProdName varchar(50) --,@EntryDate datetime --,@ProdUnit varchar(50) --,@ProdReorderPoint int --,@ProdPurchasePrice money --, @ProdQTY int)

Re: Sql Count

this is giving me headache.....Since the procedure is correctly running when i EXEC it so, I think the problem is in the calling of the SPROC.

Anybody please?? Been doing this for almost 5 days now but then I'm still stuck here :((

Re: Sql Count

I already spotted the error in my vb call and have made the necessary corrections but then again I have a new error "The precision is invalid" below is the edited code.

Code:

With CMD .CommandText = "[StockInOld]" .ActiveConnection = oConn .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@ProdID", adVarChar, adParamInput, 50, txtProdID.Text) .Parameters.Append .CreateParameter("@ProdName", adVarChar, adParamInput, 50, txtProdName.Text) .Parameters.Append .CreateParameter("@EntryDate", adDate, adParamInput, , Format$(DTEntryDate.Value, "mm/dd/yy")) .Parameters.Append .CreateParameter("@ProdUnit", adVarChar, adParamInput, 50, txtProdUnit.Text) ' .Parameters.Append .CreateParameter("@ProdReOrderPoint", adNumeric, adParamInput, , txtProdReOrderPoint.Text) .Parameters.Append .CreateParameter("@ProdReOrderPoint", adNumeric) .Parameters("@ProdReOrderPoint").NumericScale = 18 .Parameters("@ProdReOrderPoint").Precision = 0 .Parameters("@ProdReOrderPoint").Value = txtProdReOrderPoint.Text .Parameters.Append .CreateParameter("@ProdPurchasePrice", adCurrency, adParamInput, , txtProdPurchasePrice.Text) .Parameters.Append .CreateParameter("@ProdQTY", adNumeric) .Parameters("@ProdQTY").NumericScale = 18 .Parameters("@ProdQTY").Precision = 0 .Parameters("@ProdQTY").Value = txtProdQTY.Text Set rsStock = .Execute()

whether I write the code using the commented one (the one in bold letters) or not, I still get the same error.

Re: Sql Count

Originally my vb code in save button is this

vb Code:

On Error GoTo ErrHandler  If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only          
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,Sequence)" & _              
"VALUES('" & txtProdID.Text & "'," & Format$(DTEntryDate.Value, "mm-dd-yyyy") & _                   
",'" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & " ','In', '" & txtSEQ.Text & "')"                  
oConn.Execute sSQL        
 
Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile        
oConn.BeginTrans            
sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _                
"VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"      
oConn.Execute sSQL            
 
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, Sequence)" & _                
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _                   
"','" & txtProdQTY.Text & " ','In', '" & txtSEQ.Text & "')"                 '  Debug.Print sSQL      
oConn.Execute sSQL         
 
oConn.CommitTrans

and this is the Checker Function which called in my IF statement above

vb Code:

'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
  Checker = False
 
  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
         "FROM tblStockMasterFile " & _
         "WHERE ProductID = '" & (txtProdID.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
 
  If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function  'Function Checker

What we have converted into SPROC so far is that ELSE part of the code above.

One member here shared this code to me I executed this SPROC in management studio and there was no error I also tried to use EXEC StockIn '01','Tide','09-19-2007','bar',5,10,20 there was no error also but then it does not save any records to my tables. Any idea szlamany?

vb Code:

Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects 
    WHERE name='StockIn')
        DROP PROCEDURE StockIn
GO
CREATE PROCEDURE StockIn
    @ProdID varchar(50), 
    @ProdName varchar(50),
    @EntryDate datetime, 
    @ProdUnit varchar(50),
    @ProdReorderPoint numeric(18,0),
    @ProdPurchasePrice money, 
    @ProdQTY numeric(18,0)
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)
    
    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

Re: Sql Count

Post#66's error is already be solved but no record/s are being saved in the tables? Why is this so?

Re: Sql Count

any idea szlamany?

Re: Sql Count

Quote:

Originally Posted by Simply Me

any idea szlamany?

Yeah - because you are doing a rollback when the @@ERROR is 0

0 means success

You are rolling back the transaction.

The whole reason for using SPROCS was to test and debug them in a query window.

That means you put PRINT statements and run them and see where the logic flow goes.

Also - @@ERROR only persists for a single line in the SPROC. You need to be aware of that. Your earlier SPROC postings respected this by moving the value into @ERRORCODE immediately after a call.

Re: Sql Count

so what should be the code?

Re: Sql Count

Quote:

Originally Posted by Simply Me

so what should be the code?

It should be

Code:

IF @@ERROR = 0 COMMIT ELSE ROLLBACK

And actually that's not even right - you should move @@ERROR into @ERRORCODE or some other variable first before checking it.

Also - this line

Code:

IF @ProdCount <> 0 INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason, pSequence) VALUES(@ProdID, @EntryDate, @ProdPurchasePrice, @ProdQTY, 'In', @MaxSEQ)

Will only insert into the tblStockTransFile is a row already exists for that Product. Doesn't that mean you will never get an initial row??

Why the IF-statement at all? Why are you ever getting the PRODCOUNT - that doesn't make sense to me.

You have a whole pile of questionable logic errors here (<> vs = signs!).

The way you test this stuff is to do:

Code:

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

Do that first - see what comes back for @MAXSEQ - get happy with that - this is standard debugging in any language. If you don't know that your first line of code is clean you will never know where your bugs are.

Second test

Code:

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

Now we are seeeing the actual row added to tblStockMasterFile and then rolling it back since this is just a test You run these tests from another QUERY WINDOW - not from VB!!!!!!!!

Third test

Code:

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

Since you want to see what @@ERROR is - you must PRINT it. Then do a SELECT to see if the row got added.

You already posted code with @ERRORCODE vs @@ERROR. @@ERROR only persists for a single line of T-SQL code. It's value changes after every SELECT, IF, PRINT , whatever. You must most @@ERROR into a different variable in order to really use it - otherwise you can easily introduce bugs.

Re: Sql Count

ok. I 'll try and get back here... Im really desperate with this already. Its been a week of sleepless night and Im not getting anywhere....:( :( :(

Re: Sql Count

I tried testing the first one and there is an error. This one.
2
Msg 266, Level 16, State 2, Procedure StockInS, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

I am testing this in a new query window.

Re: Sql Count

Result of the second test:
Error Message if the ProductID is not yet existing
(1 row(s) affected)

(1 row(s) affected)
Msg 266, Level 16, State 2, Procedure StockInS, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 3, current count = 4.

Error Message if the product ID already exists.Msg 2627, Level 14, State 1, Procedure StockInS, 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)
Msg 266, Level 16, State 2, Procedure StockInS, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 4, current count = 5.

Both of the test yields one record results but when I open the table there's no record added to it.

1 Attachment(s)

Re: Sql Count

the third test.
Error Message:
Msg 2627, Level 14, State 1, Procedure StockInS, 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

(4 row(s) affected)
Msg 266, Level 16, State 2, Procedure StockInS, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 8, current count = 9.

the test yields a result but when I open the table no table is showing and I got an SQL Error which I attached here.

I also noticed that the code does not take care the case where the product ID already exists that's why this error always appears
Msg 2627, Level 14, State 1, Procedure StockInS, Line 25
Violation of PRIMARY KEY constraint 'PK_tblStockMasterFile'. Cannot insert duplicate key in object 'dbo.tblStockMasterFile'.
The statement has been terminated.

Re: Sql Count

Then you are tanking up BEGIN TRAN's without a matching number of ROLLBACK's.

If you run a sproc and it starts and it blows up on something - there is a good chance that the ROLLBACK never got encountered.

Sometimes I put in my second query window

Code:

Exec SomeSproc --Rollback

That ROLLBACK is commented out - but if the query doesn't run smooth - I highlight just that ROLLBACK statement and run that in the query window - to guarantee a ROLLBACK.

Every execute after a blowup that did not have it's rollback statement reached is piling on the errors.

And - btw - if you get an error - post the error and the sproc in the same post - otherwise I'm guessing at what you decided to take from my examples.

Also - simply exiting from the query window - closing it - will force any open transactions to rollback (actually you will be asked if you want to commit or rollback).

And of course - once you have an OPEN transaction - the table will deadlock and then you will get timeouts.

Pretty much everything you just posted about what is happening to you.

Re: Sql Count

Quote:

Originally Posted by szlamany

And - btw - if you get an error - post the error and the sproc in the same post - otherwise I'm guessing at what you decided to take from my examples.

Also - simply exiting from the query window - closing it - will force any open transactions to rollback (actually you will be asked if you want to commit or rollback).

And of course - once you have an OPEN transaction - the table will deadlock and then you will get timeouts.

Pretty much everything you just posted about what is happening to you.

I did post the errors I got for every test. I tested first your first code and then the second and then the third respectively.

I guess I am not going to get the correct query anyway. I have to give up now....I just need the code and from there I can learn but thanks for your help! :( :cry:

Re: Sql Count

Quote:

Originally Posted by szlamany

...if you get an error - post the error and the sproc in the same post

You did post the error - but did not post the SPROC - so I cannot see what actually ran to produce that error. Makes it impossible for us to help debug your issue remotely.

When we develop most any SPROC that has more than two or so queries we usually do it in a query window with just those queries themselves. None of the actual stored procedure CREATE or parameter list. For the parameters we simply DECLARE some vbl's and fill them with SET statements up top. Then testing is more obvious and easier to pull off.

Re: Sql Count

The SPROC that you posted are the same SPROC I tested.

Re: Sql Count

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.

Re: Sql Count

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

Re: Sql Count

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

Re: Sql Count

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

Re: Sql Count

Quote:

Originally Posted by Simply Me

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

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.

The code as I posted it was:

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

Please be more careful - you are truly wasting your time - I can only imagine the frustration you must be feeling...

But it's got nothing to do with SQL or sprocs...

And you completely ignore what I asked

Quote:

Originally Posted by szlamany

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.

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"??

Arrgghh....

Re: Sql Count

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'.

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 used this Exec StockInTest1 '01','Tide','09-19-2007','bar',5,10,20
Result

Code:

Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'StockInTest1'.

Re: Sql Count

Code:

ROLLBACK - temporarily rollback always for testing purposes

The error message is pretty clear.

The - sign is not valid

A comment is started with two -- signs

I should have posted

Code:

ROLLBACK -- temporarily rollback always for testing purposes

Sorry...

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.

Re: Sql Count

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"

Re: Sql Count

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

Re: Sql Count

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

Re: Sql Count

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

Re: Sql Count

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

Re: Sql Count

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.

Re: Sql Count

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

Code:

INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint) VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)

to be only done for a row that does not exist then check for that

Code:

If Not Exists(Select * From tblStockMasterFile Where ProductId=@ProdId) Begin INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint) VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint) End

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.

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.

Re: Sql Count

Where do i insert the code in my SPROC?

Re: Sql Count

Quote:

Originally Posted by Simply Me

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?

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.

Re: Sql Count

This is my code now.

vb 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)
If Not Exists(Select * From tblStockMasterFile Where ProductId=@ProdId)
Begin
    INSERT INTO tblStockMasterFile (ProductID, ProductName, Unit, ReOrderPoint)
      VALUES(@ProdID, @ProdName, @ProdUnit, @ProdReOrderPoint)
End
--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

Next step is I will call this in vb

Re: Sql Count

You might want to remove these two lines - or at least comment them out with -- sign

Code:

print @ERRORCODE select * From tblStockTransFile Where ProductId=@ProdId

Also - a little hint...

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.