Results 1 to 33 of 33

Thread: [RESOLVED] Code needs to be fixed.[Desperate]

  1. #1

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Resolved [RESOLVED] Code needs to be fixed.[Desperate]

    Here's my code:
    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(pSequence),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
    
        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)" & _
                    "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                       "','" & txtProdQTY.Text & " ','In')"
                     '  Debug.Print sSQL
          oConn.Execute sSQL
    
    
        oConn.CommitTrans
    
      End If
    
      Call FillListView(lstStockMasterFile, rsStock)
      Call ClearFunction(frmStockMasterFile, "TextBox")
    
      txtProdID.SetFocus
      Exit Sub
    
    ErrHandler:
      oConn.RollbackTrans
      Call msgError(Err)
    End Sub 'cmdSave_Click
    1. Check for the product ID if already exists.
    2. If No, then it will save record in tblStockMasterfFile (Product ID, Product Name, Unit, Re-Order Point) and tblStockTransFile (Product ID, Entry Date, Sequence, Purchase Price, Quantity, Reason). Note: the sequence value should be 1.
    3. If Yes, then it checks for the Entry Date.
    4. If Entry Date is the same then it saves the product and the sequence value is incremented by 1.
    5. If Entry Date is not the same then it saves the product and the sequence value is 1.

    I am having trouble with SPROC as I am very very new to it especially calling the SPROC in VB so as much as possible if I can do away with it for the time being then its better for me.

    Any help is much appreciated and thanks in advance.
    Last edited by Simply Me; Sep 21st, 2007 at 08:32 PM.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Code needs to be fixed.

    First, don't use this
    Code:
    oConn.Execute sSQL
    with a SELECT clause. You are asking for a result set of records that match your SELECT criteria and those records will be stored in a recordset. Do something like
    Code:
    Dim rs As ADODB.Recordset
    
    sSQL = "SELECT IsNull(Max(pSequence),0)+1 " 
    sSQL = sSQL & "FROM tblStockTransFile " 
    sSQL = sSQL & "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate='" & DTEntryDate.Value & "')"
    'Debug.Print sSQL
    Set rs = New ADODB.Recordset
    rs.Open sSQL, oConn

  3. #3

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.

    Thanks Hack! I'll try this. I'll be back for a result.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  4. #4

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.

    I received an error because I need to save the sequence value in the table becuase sequence is a part of the composite key so it should not be null. so in my code (in bold letter) I added pSequence.
    Code:
          sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,pSequence)" & _
                    "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                       "','" & txtProdQTY.Text & " ','In',What should I put here?)"
                       'Debug.Print sSQL
          oConn.Execute sSQL
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  5. #5

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.

    Hack or Anybody can help me out here please?
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  6. #6
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: Code needs to be fixed.[Desperate]

    The highest sequence no bumped by one...

  7. #7

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    Quote Originally Posted by randem
    The highest sequence no bumped by one...
    What do you mean by that? Any code please?
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  8. #8
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: Code needs to be fixed.[Desperate]

    Well, since you are using sequence numbers I assume they are unique and ascending, so you would need to get the highest sequence number you last used then increment it by one to add a new record to your table.

  9. #9

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    Yes that's what Im trying to achieve here. please refer to post #1.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  10. #10

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    someone here who can help please? I really need this one to be fix so that I can go on with what I am doing. Im stuck in this part. What I want to achieve is listed at the bottom of my first post.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  11. #11

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    i tried doing using the code below
    Code:
    Private Sub cmdSave_Click()
      If CheckNullValue = False Then Exit Sub
      Call ReplaceQuotation(txtProdName)
    On Error GoTo ErrHandler
    Dim rs As ADODB.Recordset
    Dim SEQ As Integer
      If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
         oConn.BeginTrans
        sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
                    "WHERE ProductID='" & txtProdID.Text & _
                        "' AND EntryDate='" & DTEntryDate.Value & "'"
        If rsStock.State = adStateOpen Then rsStock.Close
        rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    
        If Not IsNull(rsStock(0)) Then
          SEQ = rsStock(0) + 1
        Else
          SEQ = "1"
        End If
        rsStock.Close
        Set rsStock = Nothing
    
          sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,pSequence)" & _
                  "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
                       "','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In',SEQ)"
                      '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
    
        oConn.BeginTrans
    
        sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
                    "WHERE ProductID='" & txtProdID.Text & _
                        "' AND EntryDate='" & DTEntryDate.Value & "'"
        If rsStock.State = adStateOpen Then rsStock.Close
        rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    
        If Not IsNull(rsStock(0)) Then
          SEQ = rsStock(0) + 1
        Else
          SEQ = "1"
        End If
        rsStock.Close
        Set rsStock = Nothing
    
          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,pSequence)" & _
                    "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                       "','" & txtProdQTY.Text & " ','In',SEQ)"
                       'Debug.Print sSQL
          oConn.Execute sSQL
    
    
        oConn.CommitTrans
    
      End If
    
      Call FillListView(lstStockMasterFile, rsStock)
      Call ClearFunction(frmStockMasterFile, "TextBox")
    
      txtProdID.SetFocus
      Exit Sub
    
    ErrHandler:
    oConn.RollbackTrans
      Call msgError(Err)
    End Sub 'cmdSave_Click
    the name "SEQ" is not permitted in this context. Valid Expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  12. #12
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    SEQ is Integer, change

    If Not IsNull(rsStock(0)) Then
    SEQ = rsStock(0) + 1
    Else
    SEQ = "1"
    End If

    to

    If Not IsNull(rsStock(0)) Then
    SEQ = cInt(rsStock(0)) + 1
    Else
    SEQ = 1
    End If


    EDIT : Cast before adding
    IIF(Post.Rate > 0 , , )

  13. #13

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    I tried it but the error is still the same
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  14. #14

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    what do you mean by "Cast before Adding"?
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  15. #15
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    Quote Originally Posted by Simply Me
    what do you mean by "Cast before Adding"?
    Cint(), cast the value to Integer before adding.
    just a precaution. Not explicitly needed. if the pSequence is Integer in the DB, may not need it because the recordset would cast it.

    this is the error

    Code:
    & txtProdQTY.Text & "','In',SEQ)"
    shouldnt it be

    Code:
    & txtProdQTY.Text & "','In'," & SEQ & ")"
    ?
    IIF(Post.Rate > 0 , , )

  16. #16

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    Oh noohhhh. I've been doing this for almost two weeks now... Yes that's the error.

    oooppppsss. there's another error "No Transaction is Active" and is pointing to oConn.RollbackTrans.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  17. #17
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    If its says not txn is active, the error occured before a begintrans.
    So comment the On Error Goto and check where the error is occuring, or put a msgbox for err.description before the rollback.
    IIF(Post.Rate > 0 , , )

  18. #18

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    ok the error now after commenting On Error Goto is "Object Variable or With block variable not set" and is pointing to rsStock.Requery of the code below
    Code:
    'TODO: Populate the ListView control
    
    Sub FillListView(lstStockMasterFile As ListView, rsStock As ADODB.Recordset)
      Dim lst As ListItem
    
      rsStock.Requery
      lstStockMasterFile.ListItems.Clear
    
        While Not rsStock.EOF
       Set lst = lstStockMasterFile.ListItems.Add(, , rsStock("ProductID")) 'DISPLY IN LISTVIEW
        lst.SubItems(1) = rsStock.Fields("ProductName")
        lst.SubItems(2) = rsStock.Fields("Unit")
        lst.SubItems(3) = Format$(rsStock.Fields("PurchasePrice"), "###,###,##0.00")
        lst.SubItems(4) = rsStock.Fields("Quantity")
        lst.SubItems(5) = rsStock.Fields("Reason")
        lst.SubItems(6) = rsStock.Fields("EntryDate")
        lst.SubItems(7) = rsStock.Fields("ReOrderPoint")
        rsStock.MoveNext
        Set lst = Nothing 'Destroy the list
      Wend
    End Sub 'FillListView
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  19. #19
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    Thats becuse you close ther recordset before you call for that function

    in Private Sub cmdSave_Click()
    Code:
        rsStock.Close
        Set rsStock = Nothing
    should come after you call Call FillListView(lstStockMasterFile, rsStock)

    you cant close the Recordset if you are using it in that function.
    IIF(Post.Rate > 0 , , )

  20. #20

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    I already commented that line but still I get the same error message. I noticed that when I clicked debug and the error points to rsStock.Requery and then I stop the program and re-run it again, the record is already saved in the table.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  21. #21
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    There are two occurances of that code in your Save function. Can you post the changed save function again?
    If it says "Object Variable or With block variable not set" , probably becaus the object is not initialized. Check the call stack also to find the sequence of function calls.
    IIF(Post.Rate > 0 , , )

  22. #22

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    No, the error is "No Transaction is active" this time. Here's the edited code
    Code:
    Private Sub cmdSave_Click()
      Dim SEQ As Integer
      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
        oConn.BeginTrans
        
    '==========================THIS IS WORKING ALREADY==================================
      sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & _
                        "' AND EntryDate='" & DTEntryDate.Value & "'"
      If rsStock.State = adStateOpen Then rsStock.Close
      rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    
      If Not IsNull(rsStock(0)) Then
        SEQ = CInt(rsStock(0)) + 1
      Else
        SEQ = 1
      End If
    '    rsStock.Close
    '    Set rsStock = Nothing
    '==========================THIS IS WORKING ALREADY==================================
    
    
          sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,pSequence)" & _
                  "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
                       "','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In'," & SEQ & ")"
                      'Debug.Print sSQL
          oConn.Execute sSQL
    
        oConn.CommitTrans
      Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    
        oConn.BeginTrans
    
        
    '==========================THIS IS WORKING ALREADY==================================
        sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
                    "WHERE ProductID='" & txtProdID.Text & _
                        "' AND EntryDate='" & DTEntryDate.Value & "'"
        If rsStock.State = adStateOpen Then rsStock.Close
        rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    
        If Not IsNull(rsStock(0)) Then
          SEQ = rsStock(0) + 1
        Else
          SEQ = "1"
        End If
    '    rsStock.Close
    '    Set rsStock = Nothing
    '==========================THIS IS WORKING ALREADY==================================
    
          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,pSequence)" & _
                    "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                       "','" & txtProdQTY.Text & " ','In'," & SEQ & ")"
                       'Debug.Print sSQL
          oConn.Execute sSQL
    
    
        oConn.CommitTrans
    
      End If
    
      Call FillListView(lstStockMasterFile, rsStock)
      Call ClearFunction(frmStockMasterFile, "TextBox")
    
      txtProdID.SetFocus
      Exit Sub
    
    ErrHandler:
      oConn.RollbackTrans
      Call msgError(Err)
    End Sub 'cmdSave_Click
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  23. #23
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: Code needs to be fixed.[Desperate]

    Quesition... Why would you use transactions on one insert statement? Transaction are meant for multiple Inserts/Delete/Update queries that if one doesn't complete you can roll the others back. Having a transaction on other queries is pointless...
    Last edited by randem; Sep 24th, 2007 at 12:27 AM.

  24. #24
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: Code needs to be fixed.[Desperate]

    It is also pointless to do a transaction on a select statement...

  25. #25
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: Code needs to be fixed.[Desperate]

    Also if you are using Bound Data Controls... You could be at this for years... No one knows what goes on behind the scenes in those things...

  26. #26
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    Back to Square one, comment the error handler and find where the error occurs. the TXn error could occur in Committrans and Rollbacktrans. try to find where its generated.

    And randem is right, no point in having txns for those querries. Are you using bounded controls?
    IIF(Post.Rate > 0 , , )

  27. #27

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    Yes, randem is correct. I'll remove the transaction in the IF part.
    No, I'm not using bound controls. Im using ADODB
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  28. #28

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    When I commented the On Error GoTo ErrHandler the error message now is "item cannot be found in the collection corresponding to the requested name or ordinal" when I end the program and run it again the record is now displayed in the listview, meaning the record is saved. Why is it behaving like that? IM SO CONFUSE.

    Below is my whole code:
    vb Code:
    1. Option Explicit
    2. Dim rsStock As ADODB.Recordset
    3. Dim rsTempStock As ADODB.Recordset
    4. Dim CMD As ADODB.Command
    5. Dim sSQL$, sSQL1$
    6.  
    7. Private Sub cmdSave_Click()
    8.   Dim SEQ As Integer
    9.   If CheckNullValue = False Then Exit Sub
    10.   Call ReplaceQuotation(txtProdName)
    11.  
    12. 'On Error GoTo ErrHandler
    13.   If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
    14.  
    15. '==========================THIS IS WORKING ALREADY==================================
    16.   sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
    17.               "WHERE ProductID='" & txtProdID.Text & _
    18.                     "' AND EntryDate='" & DTEntryDate.Value & "'"
    19.   If rsStock.State = adStateOpen Then rsStock.Close
    20.   rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    21.  
    22.   If Not IsNull(rsStock(0)) Then
    23.     SEQ = CInt(rsStock(0)) + 1
    24.   Else
    25.     SEQ = 1
    26.   End If
    27. '    rsStock.Close
    28. '    Set rsStock = Nothing
    29. '==========================THIS IS WORKING ALREADY==================================
    30.  
    31.       sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,pSequence)" & _
    32.               "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
    33.                    "','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In'," & SEQ & ")"
    34.                   'Debug.Print sSQL
    35.       oConn.Execute sSQL
    36.  
    37.   Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    38.  
    39.    
    40. '==========================THIS IS WORKING ALREADY==================================
    41.    
    42.     sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
    43.                 "WHERE ProductID='" & txtProdID.Text & _
    44.                     "' AND EntryDate='" & DTEntryDate.Value & "'"
    45.     If rsStock.State = adStateOpen Then rsStock.Close
    46.     rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    47.  
    48.     If Not IsNull(rsStock(0)) Then
    49.       SEQ = rsStock(0) + 1
    50.     Else
    51.       SEQ = "1"
    52.     End If
    53. '    rsStock.Close
    54. '    Set rsStock = Nothing
    55. '==========================THIS IS WORKING ALREADY==================================
    56.     oConn.BeginTrans
    57.  
    58.       sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
    59.                 "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
    60.       oConn.Execute sSQL
    61.  
    62.       sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,pSequence)" & _
    63.                 "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
    64.                    "','" & txtProdQTY.Text & " ','In'," & SEQ & ")"
    65.                    'Debug.Print sSQL
    66.       oConn.Execute sSQL
    67.  
    68.  
    69.     oConn.CommitTrans
    70.  
    71.   End If
    72.  
    73.   Call FillListView(lstStockMasterFile, rsStock)
    74.   Call ClearFunction(frmStockMasterFile, "TextBox")
    75.  
    76.   txtProdID.SetFocus
    77.   Exit Sub
    78.  
    79. 'ErrHandler:
    80.   oConn.RollbackTrans
    81.   Call msgError(Err)
    82. End Sub 'cmdSave_Click
    83.  
    84. Private Sub cmdUpdate_Click()
    85.   If CheckNullValue = False Then Exit Sub
    86.   Call ReplaceQuotation(txtProdName)
    87.  
    88.   'TODO: check whether the PK (Product) already exist or not.
    89.   If iTempProdID <> txtProdID.Text Then
    90.  
    91.   'check whether user has change the PK otherwise it'll not be saved
    92.     If Checker = True Then
    93.       Call msgExist("Product")
    94.       txtProdID.SetFocus
    95.       ' Start highlight before first character.
    96.       txtProdID.SelStart = 0
    97.       ' Highlight to end of text.
    98.       txtProdID.SelLength = Len(txtProdID.Text)
    99.       Exit Sub
    100.     End If  'Checker
    101.   End If  'iTempCName
    102.  
    103.   If (MsgBox("Are you sure?", vbInformation + vbYesNo, ProgName) = vbYes) Then
    104.  
    105.  
    106.     On Error GoTo ErrHandler
    107.       sSQL = "UPDATE tblStockMasterFile " & _
    108.                   "SET ProductID= '" & txtProdID.Text & "'," & _
    109.                       "ProductName = '" & txtProdName.Text & "'," & _
    110.                       "Unit= '" & txtProdUnit.Text & "'," & _
    111.                       "ReOrderPoint='" & txtProdReOrderPoint.Text & "'" & _
    112.                          "WHERE ProductID = '" & iTempProdID & "'"
    113.       oConn.Execute sSQL
    114.       'Debug.Print sSQL
    115.  
    116.     Call FillListView(lstStockMasterFile, rsStock)
    117.     Call ClearFunction(frmStockMasterFile, "TextBox")
    118.  
    119.     cmdUpdate.Enabled = False
    120.     cmdSave.Enabled = True
    121.     lstStockMasterFile.Enabled = True
    122.   End If
    123.  
    124.   Exit Sub
    125. ErrHandler:
    126.   Call msgError(Err)
    127. End Sub 'cmdUpdate_Click
    Last edited by Simply Me; Sep 24th, 2007 at 04:28 AM.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  29. #29

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    vb Code:
    1. 'TODO : return true if ProductID already exist
    2. Private Function Checker() As Boolean
    3.   Checker = False
    4.  
    5.   If rsTempStock.State = adStateOpen Then rsTempStock.Close
    6.   sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
    7.          "FROM tblStockMasterFile " & _
    8.          "WHERE ProductID = '" & (txtProdID.Text) & "'"
    9.   'Debug.Print sSQL1
    10.   rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
    11.  
    12.   If rsTempStock("ProductIDExist") > 0 Then Checker = True
    13. End Function  'Function Checker
    14.  
    15. 'TODO : return true if all required field has been filled
    16. Private Function CheckNullValue() As Boolean
    17.   CheckNullValue = False
    18.  
    19.   If Len(Trim$(txtProdID.Text)) = 0 Then
    20.     Call msgMustBeFilled("Product ID")
    21.     txtProdID.SetFocus
    22.     Exit Function
    23.   ElseIf Len(Trim$(txtProdName.Text)) = 0 Then
    24.     Call msgMustBeFilled("Product Name")
    25.     txtProdName.SetFocus
    26.     Exit Function
    27.   ElseIf Len(Trim$(txtProdUnit.Text)) = 0 Then
    28.     Call msgMustBeFilled("Product Unit")
    29.     txtProdUnit.SetFocus
    30.     Exit Function
    31.   ElseIf Len(Trim$(txtProdPurchasePrice.Text)) = 0 Then
    32.     Call msgMustBeFilled("Product Purchase Price")
    33.     txtProdPurchasePrice.SetFocus
    34.     Exit Function
    35.   ElseIf Len(Trim$(txtProdQTY.Text)) = 0 Then
    36.     Call msgMustBeFilled("Product Quantity")
    37.     txtProdQTY.SetFocus
    38.     Exit Function
    39.   ElseIf Len(Trim$(txtProdReOrderPoint.Text)) = 0 Then
    40.     Call msgMustBeFilled("Product Re-Order Point")
    41.     txtProdReOrderPoint.SetFocus
    42.     Exit Function
    43.   End If
    44.  
    45.   'If cmdAddCategory.Enabled = True Then Call ReplaceQuotation(txtCategoryName)
    46.   CheckNullValue = True
    47. End Function  'CheckNullValue
    48.  
    49. Private Sub Form_Load()
    50.   Call openConnection
    51.   Me.Left = LeftPos - 400
    52.   Me.Top = TopPos - 200
    53.  
    54.   Set rsStock = New ADODB.Recordset
    55.   Set rsTempStock = New ADODB.Recordset
    56.   Set CMD = New ADODB.Command
    57.  
    58.   'Calling myProcedure
    59.   With CMD
    60.     .ActiveConnection = oConn
    61.     .CommandText = "[myProcedure]"
    62.     .CommandType = adCmdStoredProc
    63.     Set rsStock = .Execute()
    64.     Set rsTempStock = .Execute()
    65.   End With
    66.  
    67.   Set CMD = Nothing
    68.  
    69.   DTEntryDate.Value = Format$(Now, "mm/dd/yyyy") 'Display the Current Date
    70.   Call FillListView(lstStockMasterFile, rsStock)
    71. End Sub 'Form_Load
    72.  
    73. 'TODO: Populate the ListView control
    74.  
    75. Sub FillListView(lstStockMasterFile As ListView, rsStock As ADODB.Recordset)
    76.   Dim lst As ListItem
    77.  
    78.   rsStock.Requery
    79.   lstStockMasterFile.ListItems.Clear
    80.  
    81.     While Not rsStock.EOF
    82.    Set lst = lstStockMasterFile.ListItems.Add(, , rsStock("ProductID")) 'DISPLY IN LISTVIEW
    83.     lst.SubItems(1) = rsStock.Fields("ProductName")
    84.     lst.SubItems(2) = rsStock.Fields("Unit")
    85.     lst.SubItems(3) = Format$(rsStock.Fields("PurchasePrice"), "###,###,##0.00")
    86.     lst.SubItems(4) = rsStock.Fields("Quantity")
    87.     lst.SubItems(5) = rsStock.Fields("Reason")
    88.     lst.SubItems(6) = rsStock.Fields("EntryDate")
    89.     lst.SubItems(7) = rsStock.Fields("ReOrderPoint")
    90.     rsStock.MoveNext
    91.     Set lst = Nothing 'Destroy the list
    92.   Wend
    93. End Sub 'FillListView
    94.  
    95. Private Sub Form_Unload(Cancel As Integer)
    96.   If rsStock.State = adStateOpen Then rsStock.Close
    97.   Set rsStock = Nothing
    98.  
    99.   If rsTempStock.State = adStateOpen Then rsTempStock.Close
    100.   Set rsTempStock = Nothing
    101. End Sub 'Form_Unload
    102.  
    103. Private Sub lstStockMasterFile_DblClick()
    104.   If lstStockMasterFile.ListItems.Count <> 0 Then
    105.   sSQL = "SELECT ProductID, ProductName " & _
    106.             "FROM tblStockMasterFile " & _
    107.               "WHERE  ProductID = ' & lstStockMasterFile.ListItems.Item(lstStockMasterFile.SelectedItem.Index).Text & '"
    108.  
    109.     edwin = lstStockMasterFile.SelectedItem.Index
    110.     txtProdID.Text = lstStockMasterFile.ListItems.Item(edwin).Text
    111.     iTempProdID = txtProdID.Text
    112.     txtProdName.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(1)
    113.     txtProdUnit.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(2)
    114.     txtProdPurchasePrice.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(3)
    115.     txtProdQTY.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(4)
    116.     'txtReason.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(5)
    117.     DTEntryDate.Value = lstStockMasterFile.ListItems.Item(edwin).SubItems(6)
    118.     txtProdReOrderPoint.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(7)
    119.    
    120.     lstStockMasterFile.ListItems.Remove (edwin)
    121.     lstStockMasterFile.Enabled = False
    122.  
    123.     cmdSave.Enabled = False
    124.     cmdUpdate.Enabled = True
    125.  
    126.     txtProdSearch.Text = vbNullString
    127.   End If
    128. End Sub 'lstStockMasterFile_DblClick
    129.  
    130. Private Sub lstStockMasterFile_KeyDown(KeyCode As Integer, Shift As Integer)
    131.   Select Case KeyCode
    132.     Case vbKeyDelete
    133.       If lstStockMasterFile.ListItems.Count <> 0 Then
    134.         edwin = lstStockMasterFile.SelectedItem.Index
    135.  
    136.         sSQL = "DELETE FROM tblStockMasterFile " & _
    137.                   "WHERE ProductID= '" & (lstStockMasterFile.ListItems.Item(edwin).Text) & "'"
    138.         'Debug.Print sSQL
    139.         If (MsgBox(lstStockMasterFile.SelectedItem.SubItems(1) & " will permanently be deleted!" & Chr(13) & " Delete anyway?", vbCritical + vbYesNo + vbDefaultButton2, ProgName) = vbNo) Then
    140.           Exit Sub
    141.         Else
    142.           oConn.Execute sSQL
    143.           lstStockMasterFile.ListItems.Remove (edwin)
    144.         End If
    145.  
    146.       End If  'lstStockMasterFile.ListItems.Count
    147.  
    148.       lstStockMasterFile.Refresh
    149.   End Select  'Case KeyCode
    150. End Sub 'lstStockMasterFile_KeyDown
    151.  
    152. Function doQuery(ByVal sCriteria As String, ByVal sfieldName As String) As String
    153.     If InStr(sCriteria, "'") <> 0 Then
    154.         sCriteria = Replace(sCriteria, "'", "''")
    155.     End If
    156.  
    157.     If InStr(sCriteria, "[") <> 0 Then    'Added 5-14-07
    158.       sCriteria = Replace(sCriteria, "[", "[[]")
    159.     End If
    160.  
    161. '    doQuery = "SELECT ProductID,ProductName,Unit,ReOrderPoint FROM tblStockMasterFile " & _
    162.               "WHERE " & sfieldName & " LIKE '" & sCriteria & "%'"
    163.              
    164.     doQuery = "SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName, " & _
    165.           "tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint, " & _
    166.           "tblStockTransFile.EntryDate, tblStockTransFile.pSequence, " & _
    167.           "tblStockTransFile.PurchasePrice, tblStockTransFile.Quantity, " & _
    168.           "tblStockTransFile.Reason " & _
    169.               "FROM tblStockMasterFile INNER JOIN tblStockTransFile ON " & _
    170.                   "tblStockMasterFile.ProductID = tblStockTransFile.ProductID " & _
    171.                       "WHERE " & sfieldName & " LIKE '" & sCriteria & "%'"
    172. End Function 'doQuery
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  30. #30

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    Here is my Procedure
    vb Code:
    1. Use MasterFile
    2. GO
    3. If EXISTS(SELECT name FROM sysobjects
    4.         WHERE name='myProcedure')
    5.     DROP PROCEDURE myProcedure
    6. GO
    7.  
    8. CREATE PROCEDURE myProcedure
    9. AS
    10. SELECT tblStockMasterFile.ProductID,
    11.     tblStockMasterFile.ProductName,
    12.     tblStockMasterFile.Unit,
    13.     tblStockMasterFile.ReOrderPoint,
    14.     tblStockTransFile.EntryDate,
    15.     tblStockTransFile.pSequence,
    16.     tblStockTransFile.PurchasePrice,
    17.     tblStockTransFile.Quantity,
    18.     tblStockTransFile.Reason
    19.         FROM tblStockMasterFile INNER JOIN tblStockTransFile ON
    20.             tblStockMasterFile.ProductID = tblStockTransFile.ProductID
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  31. #31
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    OK , this is the same error in your other thread. So I think its the same problem. Your rsStock might be changed somewhere during the code.
    Best is to put a breakpoint in the start of the save function , and step through the code execution (pressing F8). And try to find where the error is occuring.
    IIF(Post.Rate > 0 , , )

  32. #32
    Frenzied Member
    Join Date
    Jul 2007
    Posts
    1,306

    Re: Code needs to be fixed.[Desperate]

    Why are you using only one recordset for all your results? (rsStock) If your are using one recordset, you have to make sure that its holding the correct records everywhere you are using it. I think its very hard to maintain like that. Better if you use another recordset for your other needs.
    I dont see any harm using many recordsets as long as you close them and destroy properly.
    IIF(Post.Rate > 0 , , )

  33. #33

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,732

    Re: Code needs to be fixed.[Desperate]

    its working now, I used another recordset for SELECT MAX(pSequence)
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width