Sql Count

I think because the procedure is not complete yet... there's not code yet for saving it in tblStockMasterFile. How do I combined the two?

I need to convert these codes

Code:

---

    oConn.BeginTrans
     
      sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
                "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
      oConn.Execute sSQL
     
      sSQL = "SELECT IsNull(Max(Sequence),0 )+ 1" & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate='" & DTEntryDate.Value & "'"
                  Debug.Print sSQL
      oConn.Execute sSQL
     
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
                "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                  "','" & txtProdQTY.Text & " ','In')"
                  Debug.Print sSQL
      oConn.Execute sSQL

   
    oConn.CommitTrans

---

here's the code now

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),
        @Reason text, @SEQ numeric(18,0)
AS
DECLARE @ErrorCode int
INSERT INTO tblStockMasterFile(ProductID,ProductName,Unit,ReOrderPoint)
    VALUES(@ProdID,@ProdName,@ProdUnit,@ProdReOrderPoint)
   
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,@Reason,@SEQ)
SET @ErrorCode=@@ERROR
IF (@errorCode=0)
        RETURN (0)
ELSE
        RETURN (@ErrorCode)

---

i tried this

Code:

---

Exec StockIn '01','Tide','2007-09-17','bar',5,10,20,'In',0

---

and the error is this

Code:

---

Msg 2627, Level 14, State 1, Procedure StockIn, Line 10
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 2627, Level 14, State 1, Procedure StockIn, Line 17
Violation of PRIMARY KEY constraint 'PK_tblStockTransFile'. Cannot insert duplicate key in object 'dbo.tblStockTransFile'.
The statement has been terminated.

---

I am heading out for a couple of hours...

But you simply put all those insert and select statements into the same sproc - sprocs can have more then a single query.

The code to get the max value would be:

Code:

---

Declare @MaxSeq int
Set @MaxSeq=(Select IsNull(Max(Sequence),0 )+ 1
                  from ...
                  where ...)

---

I came to realized that I am doing the test wrongly. NOw, Here's the SPROC but when I execute this I got an error "Msg 156, Level 15, State 1, Procedure StockIn, Line 19
Incorrect syntax near the keyword 'INSERT'."

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),
        @Reason text, @SEQ numeric(18,0)
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

--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,@Reason,@SEQ)
SET @ErrorCode=@@ERROR
IF (@errorCode=0)
        RETURN (0)
ELSE
        RETURN (@ErrorCode)

---

Now, How do I call this in VB?

Is it possible in the SPROC instead of using @Reason as VALUES, I would just put there "In" so that user will not always type it whenever stock will be entered? if so how?

BTW, If i used this code I get error when executing it.

Code:

---

SET @MaxSeq=(SELECT IsNull(Max(Sequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate

---

but if i used this, there's no error

Code:

---

SELECT IsNull(Max(Sequence),0 )+ 1
    FROM tblStockTransFile
                WHERE ProductID= @ProdID AND EntryDate=@EntryDate

---

But of them when I try to use EXECUTE StockIn...... the record will be saved but it displays an error.

You can say:

Code:

---

If IsNull(@Reason,'')='' Set @Reason='IN'

---

Or get rid of @Reason altogether and hardwire 'IN' in the VALUES list in that INSERT

You are missing a closing parenthesis in the SET @MAXSEQ=(...)

should be

Code:

---

SET @MaxSeq=(SELECT IsNull(Max(Sequence),0 )+ 1
FROM tblStockTransFile
WHERE ProductID= @ProdID AND EntryDate=@EntryDate)

---

I had that parenthesis in my example in post #44.

You must SELECT into a variable to use the value in the SPROC - otherwise a SELECT all by itself simply comes out to VB as a recordset after the sproc runs.

Its already fixed. Next is how do I call the sproc in VB?

Quote:

---

Originally Posted by szlamany

This creates a command object and a recordset object and executes a stored procedure.

Code:

---

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim offCmnd As ADODB.Command

Set offCmnd = New ADODB.Command

With offCmnd
    .CommandText = "rptDailyAttTeacher"
    .ActiveConnection = gCn
    .CommandType = adCmdStoredProc
   
    .Parameters.Append .CreateParameter("@Yr", adInteger, adParamInput, , frmTeaAttend.lblYr.Caption)
    .Parameters.Append .CreateParameter("@Bldg", adInteger, adParamInput, , frmTeaAttend.gstrBldg)
    .Parameters.Append .CreateParameter("@Attendance_Date", adDate, adParamInput, , Format$(dtpOffice.Value, "SHORT DATE"))
    .Parameters.Append .CreateParameter("@Sort_by_Grade$N_Y", adVarChar, adParamInput, 1, "N")
    .Parameters.Append .CreateParameter("@Final_Report$N_Y", adVarChar, adParamInput, 1, "N")
    Set rs = .Execute()
End With

Set offCmnd = Nothing

---

This one is from a very old program we have - but it has adDate datatypes - which should help you later.

---

This was post #26

Do I need to always use .ActiveConnection everytime I call a procedure even I have already have .ActiveConnection on form load when I call my myProcedure?

are the variable used in calling the procedure the same with the variable used in the procedure? can you explain at least one line of the code? what are those and what they do.

this is my code now and it gives me error "Parameter object is improperly defined. Inconsistent or incomplete information was provided."

Code:

---

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

Set CMD = Nothing

---

Quote:

---

Originally Posted by Simply Me

Do I need to always use .ActiveConnection everytime I call a procedure even I have already have .ActiveConnection on form load when I call my myProcedure?

---

We create and destroy our CMD and RS objects in every sub - so we obviously need to reassign the .ActiveConnection. If you have a method that works for you that you like - then use it. I've got no opinion on this one.

Quote:

---

Originally Posted by Simply Me

are the variable used in calling the procedure the same with the variable used in the procedure? can you explain at least one line of the code? what are those and what they do.

---

Yes - the parameters being created should match in name and order as those that are defined in the SPROC - why wouldn't you want to do that? The .Parameter Collection is having .Append'd to it and created parameters (the .CreateParameter). Arguments to that method are Parameter name, Datatype, Direction, Size - which you are missing for those VARCHAR()'s and the "data" being passed for that parameter.

Quote:

---

Originally Posted by Simply Me

this is my code now and it gives me error "Parameter object is improperly defined. Inconsistent or incomplete information was provided."

---

I'm guessing the missing lengths on the VARCHAR parameters is the issue.

The error is "Application uses a value of the wrong type for the current operation". Here is my complete code in save button plus the SPROC. Note that in my code only the ELSE statement part is calling the procedure as I am still trying to code (which im having issues) the IF statement part.

Code:

---

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)

On Error GoTo ErrHandler
  If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
    'SEQ = rsStock.Fields("Sequence")
    oConn.BeginTrans

      sSQL = "SELECT IsNull(Max(Sequence),0)+1 " & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate='" & DTEntryDate.Value & "'"
                  Debug.Print sSQL
      oConn.Execute sSQL
     
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason)" & _
              "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
                  "','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In')"
                  Debug.Print sSQL
                  '" & rsStock.Fields("Sequence") + 1 & "'
      oConn.Execute sSQL
     
    oConn.CommitTrans
  Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    Set rsStock = New ADODB.Recordset
    Set CMD = New ADODB.Command
    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,50, Format$(DTEntryDate.Value, "SHORT DATE"))
      .Parameters.Append .CreateParameter("@ProdUnit", adVarChar, adParamInput,50, txtProdUnit.Text)
      .Parameters.Append .CreateParameter("@ProdReorderPoint", adint, adParamInput, , txtProdReOrderPoint.Text)
      .Parameters.Append .CreateParameter("@ProdPurchasePrice", adCurrency, adParamInput, , txtProdUnit.Text)
      .Parameters.Append .CreateParameter("@ProdQTY", adint, adParamInput, , txtProdQTY.Text)
      Set rsStock = .Execute()
    End With

    Set CMD = Nothing
  End If
 
  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")

  txtProdID.SetFocus
  Exit Sub

ErrHandler:
  Call msgError(Err)
End Sub 'cmdSave_Click

---

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)

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

---

Im really confused now :(
Im afraid I cannot complete this project :(

You are missing the length in this call

.Parameters.Append .CreateParameter("@ProdUnit", adVarChar, adParamInput, , txtProdUnit.Text)

should be something like

.Parameters.Append .CreateParameter("@ProdUnit", adVarChar, adParamInput, 50, txtProdUnit.Text)

That 50 is important.

Did you not read my last post??

I have edited my last post.

You put 50 on adDate - what's up with that?

Do you know how to step through code - one line at a time - with the F8 key?

Yes, I know. I removed now the 5o in adDate but i still get the same error

My sample code has adInteger

You have adint - that's not good.

tried changing that too to adNumeric its still the same error

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

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

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

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)

---

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 :((

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.

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

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

any idea szlamany?

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.

so what should be the code?

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.

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

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.

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.

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.

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.

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:

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.

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