Results 1 to 28 of 28

Thread: [RESOLVED] Questions on Database Update

  1. #1

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

    Resolved [RESOLVED] Questions on Database Update

    HI! I have to two tables tblStocks and tblSales (MS ACCESS) which i would like to manipulate. I am using ADODB. My questions are:

    1. Which is better in updating table Update tbl Set text......ObjConn.Execute or rs.update?
    2. How do i update two tables at the same time? I want to update one (1) field in my tblStocks and five (5) fields in tblSales. The fields in two tables are not the same.
    3. I have this code in cmdAddItem_Click().
    VB Code:
    1. rsStocks.Fields("Quantity") = (Val(rsStocks.Fields("Quantity") + Val(iQtyTemp)) - Val(txtQty.Text))
    2.     rsStocks.Update 'Update the database

    I have another code in
    VB Code:
    1. Private Sub lstStocks_KeyDown(KeyCode As Integer, Shift As Integer)
    2.   If rsStocks.State = adStateOpen Then rsStocks.Close
    3.   rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    4.  
    5.   Select Case KeyCode
    6.     Case vbKeyDelete
    7.       If lstStocks.ListItems.Count <> 0 Then
    8.         edwin = lstStocks.SelectedItem.Index
    9.         sSQL = "SELECT * FROM tblStocks WHERE Code = '" & lstStocks.ListItems.Item(edwin).Text & "'"
    10.         'rsStocks.Open("SELECT * FROM tblStocks WHERE Code = '" & lstStocks.ListItems.Item(edwin).Text & "'")
    11.         rsStocks.Fields("Quantity") = Val(rsStocks.Fields("Quantity") + Val(lstStocks.ListItems.Item(edwin).SubItems(2)))
    12.         rsStocks.Update
    13.         lstStocks.ListItems.Remove (edwin)
    14.         iValTemp = 0
    15.         For i = 1 To lstStocks.ListItems.Count
    16.           iValTemp = Val(iValTemp) + Val(lstStocks.ListItems.Item(i).SubItems(5))
    17.           txtAmountDue.Text = iValTemp
    18.         Next
    19.       End If
    20.   End Select
    21. End Sub

    the codes above works fine. But why is it that when i change the first code in its SQL equivalent it behaves differently.
    VB Code:
    1. sSQL = "UPDATE tblStocks SET Quantity = '" & Val(rsStocks.Fields("Quantity")) & "' + '" & Val(iQtyTemp) & "' - '" & Val(txtQty.Text) & "'"
    2.     oConn.Execute sSQL

    Please Help! Example code is needed.
    Last edited by Simply Me; Apr 30th, 2006 at 05:04 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

  2. #2

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

    Re: Questions on Database Update

    I change my last code with this
    VB Code:
    1. sSQL = "UPDATE TblStocks SET Quantity = [Quantity] WHERE Quantity= '" & (Val(rsStocks.Fields("Quantity") & "' + '" & Val(iQtyTemp)) & "' - '" & Val(txtQty.Text)) & "'"
    2.     oConn.Execute sSQL
    this time the database is not subtracting the value i typed txtQty from the value stored in field Quantity.

    Any one 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

  3. #3

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

    Re: Questions on Database Update

    Any Database Guru help 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

  4. #4
    Frenzied Member litlewiki's Avatar
    Join Date
    Dec 2005
    Location
    Zeta Reticuli Distro:Ubuntu Fiesty
    Posts
    1,162

    Re: Questions on Database Update

    ialways use the objconn.execute method ,i think its a personal prefernce as to which method u choose

    all your fields are numerics so why are you sending them as strings ,numerics are sent as ' & variable & ' .also make sure that you are using different connections and recordsets ,one for retreiving and other for uppdating or better store them in local variables then execute them
    __________________
    ________________0ооо___
    ___ооо0________(___)____
    __(___)_________) _/_____
    ___\_ (_________(_/______
    ____\_)_________________

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

    Re: Questions on Database Update

    If you use the .Execute method you can update many records in one SQL statement. Using the .Update method will only be able to update one record at a time. I always find it easier and better documented when you use SQL to do processes on your database when ever possible. one statement in readable english (or whatever language you use) can be very useful months down the road when one has to revist the code to find out what is happening.

    They won't have to figure out what the originator was attempting to do (for the most part).

  6. #6

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

    Re: Questions on Database Update

    Thanks litlewiki and randem.

    I was able to make the last code i pasted in post #1 work.

    [quote]all your fields are numerics so why are you sending them as strings ,numerics are sent as ' & variable & ' .[/qoute]

    If I send value to my fields like what you are saying it gives me error but if i do it like this '" & variable & "' it works find.

    Right now what i am trying to figure out is the SQL equivalent of this line found in post #1
    VB Code:
    1. rsStocks.Fields("Quantity") = Val(rsStocks.Fields("Quantity") + Val(lstStocks.ListItems.Item(edwin).SubItems(2)))
    2.         rsStocks.Update
    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

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    strSQL = "Update tableName Set Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " Where PrimaryKey for Table = " & the record key

    cbconn.Execute strSQL
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8

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

    Re: Questions on Database Update

    i did this
    VB Code:
    1. sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & "' WHERE Code='" & Val(lstStocks.ListItems.Item(edwin).Text) & "'"

    its not adding giving me the correct value.
    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

  9. #9
    PowerPoster Pasvorto's Avatar
    Join Date
    Oct 2002
    Location
    Minnesota, USA
    Posts
    2,951

    Re: Questions on Database Update

    I think you mean this:

    sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text) & """

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    What is the actual SQL statement that is being generated? Can you post that.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11

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

    Re: Questions on Database Update

    Quote Originally Posted by Pasvorto
    I think you mean this:

    sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text) & """
    I am getting this error message "Syntax error in String in Query expression 'Code=2"'"
    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
    PowerPoster Pasvorto's Avatar
    Join Date
    Oct 2002
    Location
    Minnesota, USA
    Posts
    2,951

    Re: Questions on Database Update

    sSQL = "UPDATE TblStocks SET Quantity = Quantity + "
    sSql = sSql & Val(lstStocks.ListItems.Item(edwin).SubItems(2))
    sSql = sSql & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text) & chr(34)

  13. #13
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    The extra double qoute at the end of the SQL string should not be there if the code is a number.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  14. #14

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

    Re: Questions on Database Update

    What I am trying to do is like this. I have a listview where i can delete any row in it. When I delete a row I would like that the value in the column QTY will added back to Quantity field in my database.

    Say I have in my field Quantity=500. If I will issue one (1) item then my Quantity field becomes 499. That one (1) item will then be displayed in my listview but once I delete that row in my listview (the item one (1)) 1 should be added back to 499.

    Here's my code:
    VB Code:
    1. Private Sub lstStocks_KeyDown(KeyCode As Integer, Shift As Integer)
    2.   If rsStocks.State = adStateOpen Then rsStocks.Close
    3.   rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    4.  
    5.   Select Case KeyCode
    6.     Case vbKeyDelete
    7.       If lstStocks.ListItems.Count <> 0 Then
    8.         edwin = lstStocks.SelectedItem.Index
    9.         sSQL = "SELECT * FROM tblStocks WHERE Code = ' & lstStocks.ListItems.Item(edwin).Text & '"
    10.     sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= ' & Val(lstStocks.ListItems.Item(edwin).Text) & '"
    11.     oConn.Execute sSQL
    12.        
    13.        
    14.         lstStocks.ListItems.Remove (edwin)
    15.         iValTemp = 0
    16.         For i = 1 To lstStocks.ListItems.Count
    17.           iValTemp = Val(iValTemp) + Val(lstStocks.ListItems.Item(i).SubItems(5))
    18.           txtAmountDue.Text = iValTemp
    19.         Next
    20.       End If
    21.   End Select
    22. End Sub
    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
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    Is code a numeric in the Database? What sSQL are you runing the Select statement or the Update statement? Why both of them in the code section? The select is never going to run as coded.

    if Code is a numeric in the DB then
    VB Code:
    1. sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= " & Val(lstStocks.ListItems.Item(edwin).Text)

    if code is text in the DB then
    VB Code:
    1. sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= '" & (lstStocks.ListItems.Item(edwin).Text) & "'"
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  16. #16

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

    Re: Questions on Database Update

    Im sorry SELECT should have been commented in my post. Yes Code is Numeric in DB.
    I tried you code and give "Data Type mismatch in criteria expression."
    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
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    Please post the generated SQL statement that is being run.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  18. #18

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

    Re: Questions on Database Update

    Quote Originally Posted by GaryMazzone
    Please post the generated SQL statement that is being run.
    Im sorry I dont seem to understand what you mean.
    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
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    The program is generating the SQL string to send to the database what is the complete string that is being sent by the program. Use debug print statements to place it into the immediate window or place a textbox on the form and place the SQL statement there once the generation is complete. Then copy and paste that code into the thread.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  20. #20
    New Member
    Join Date
    Apr 2006
    Posts
    11

    Re: Questions on Database Update

    I have a suggesstion for u i dont know whether i m correct or not but i think this will help u to generate queries as and when required

    1. Open Data base in MS - Access
    2. Then Open Query tab
    3. Try to make Query overthere (Using Design View)
    4. Then see the sql code generated for it and
    5. try to implement that same in the sqlqry stat that was being told by our friends

    Tell me if it works or anyhow i could be able to help

  21. #21

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

    Re: Questions on Database Update

    Quote Originally Posted by GaryMazzone
    The program is generating the SQL string to send to the database what is the complete string that is being sent by the program. Use debug print statements to place it into the immediate window or place a textbox on the form and place the SQL statement there once the generation is complete. Then copy and paste that code into the thread.
    Do you mean this?
    UPDATE TblStocks SET Quantity = Quantity + 1 WHERE Code= 1
    When the error "Data Type mismatch in criteria expression." displayed on screen i clicked the debug button and I type ?sSQL in the immediate window and it gave that 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

  22. #22
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    OK next question is what are the data types in the database for Quanity and Code? Also ensure that code in in brackets.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  23. #23

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

    Re: Questions on Database Update

    Quantity is of number data type and Code is of text data type
    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

  24. #24
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    Then code needs to be enclosed in single qoutes.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  25. #25

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

    Re: Questions on Database Update

    Quote Originally Posted by GaryMazzone
    Then code needs to be enclosed in single qoutes.
    GaryMazzone, I have the following code working:
    TO DISPLAY ITEM IN LISTVIEW AND SUBSEQUENTLY SUBTRACT SUCH ITEM FROM THE DATABASE.
    Code:
    Private Sub cmdAddItem_Click()
      Dim lst As ListItem, itemFound As ListItem
      
      If lstStocks.Enabled = False Then
        lstStocks.Enabled = True
      End If
      
      sSQL = "SELECT * FROM tblStocks WHERE Code = '" & txtCode.Text & "'"
    
      If rsStocks.State = adStateOpen Then rsStocks.Close
      rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
      
      If txtQty.Text <> 0 Then
        cAmountDueTemp = 0
        If txtDiscount.Text = "" Then
          txtDiscount.Text = 0
          cSubtotal = (CLng(txtQty.Text) * (rsStocks.Fields("SellingPrice")))
        Else
          cSubtotal = (CLng(txtQty.Text) * (rsStocks.Fields("SellingPrice"))) - (CLng(txtQty.Text) * (CCur(txtDiscount.Text))) 'Val(iTempSubTotal))
        End If
    
        sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & iQtyTemp & "' - '" & CLng(txtQty.Text) & "' WHERE Code='" & txtCode.Text & "'"
        oConn.Execute sSQL
        
        Set lst = lstStocks.ListItems.Add(, , txtCode.Text) 'DISPLAY IN LISTVIEW
          lst.SubItems(1) = txtDesc.Text
          lst.SubItems(2) = CLng(txtQty.Text)
          lst.SubItems(3) = Format(rsStocks.Fields("SellingPrice"), "###,###,##0.00")
          lst.SubItems(4) = Format(rsStocks.Fields("PurchasePrice"), "###,###,##0.00")
          lst.SubItems(5) = Format(CCur(txtDiscount.Text) * CLng(txtQty.Text), "###,###,##0.00")
          lst.SubItems(6) = cSubtotal
        
        txtCode.Text = ""
        txtDiscount.Text = ""
        txtQty.Text = ""
        txtCode.SetFocus
      
        For i = 1 To lstStocks.ListItems.Count
          cAmountDueTemp = CCur(cAmountDueTemp) + CCur(lstStocks.ListItems.Item(i).SubItems(6))
          txtAmountDue.Text = cAmountDueTemp
        Next
      Else
        MsgBox "Quantity must not be zero"
        txtQty.SetFocus
        SendKeys "{home}+{end}"
      End If
    End Sub
    AND THIS CODE TO ADD BACK AN ITEM TO THE DATABASE IF BUYER DECIDE NOT TO BUY PREVIOUSLY ADDED ITEM
    Code:
    Private Sub lstStocks_KeyDown(KeyCode As Integer, Shift As Integer)
      If rsStocks.State = adStateOpen Then rsStocks.Close
      rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
      
      Select Case KeyCode
        Case vbKeyDelete
          If lstStocks.ListItems.Count <> 0 Then
            edwin = lstStocks.SelectedItem.Index
        sSQL = "UPDATE TblStocks SET Quantity = Quantity + ' & Val(lstStocks.ListItems.Item(edwin).SubItems(2)) & ' WHERE Code= ' & Val(lstStocks.ListItems.Item(edwin).Text) & '"
        oConn.Execute sSQL
            
            
            lstStocks.ListItems.Remove (edwin)
            cAmountDueTemp = 0
            For i = 1 To lstStocks.ListItems.Count
              cAmountDueTemp = CCur(cAmountDueTemp) + CCur(lstStocks.ListItems.Item(i).SubItems(5))
              txtAmountDue.Text = cAmountDueTemp
            Next
          End If
      End Select
    End Sub
    PROBLEM: Everytime I delete an Item instead of adding back the item to the database it subtract it to the database.

    DATA TYPES USED:
    Code= Text
    ProductDescription = text
    SellingPrice = Currency
    PurchasePrice = Currency
    Quantity = Long
    Unit = text
    EntryDate = Date
    Discount = Currency

    IN THE DECLARATION AREA I HAVE THE FF:
    Dim rsSales As adodb.Recordset
    Dim rsStocks As adodb.Recordset
    Dim sSQL$, sSQL1$
    Dim cSubtotal As Currency, cAmountDueTemp As Currency
    Dim iQtyTemp As Long, i%, edwin%, itmFound%
    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

  26. #26
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: Questions on Database Update

    I think I'm confused here. Let's start with the remove from inventory

    the code:
    Code:
    sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & iQtyTemp & "' - '" & CLng(txtQty.Text) & "' WHERE Code='" & txtCode.Text & "'"
    What is the iQttTemp & "'-'" & clng(txtQty.Text) for. Why not just
    Code:
    sSQL = "UPDATE TblStocks SET Quantity = Quantity - + & CLng(txtQty.Text) & " WHERE Code='" & txtCode.Text & "'"
    That should subtract the quanty in the txtQty field on the from from the Quanity field in the database.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  27. #27

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

    Re: Questions on Database Update

    Quote Originally Posted by GaryMazzone
    the code:
    Code:
    sSQL = "UPDATE TblStocks SET Quantity = Quantity + '" & iQtyTemp & "' - '" & CLng(txtQty.Text) & "' WHERE Code='" & txtCode.Text & "'"
    i used the iQtyTemp in my Private sub lstStocks_DblClick().----for editing purposes. When i double click an item in listview the item will be romove from the listview and display in textboxes.

    Quote Originally Posted by GaryMazzone
    What is the iQttTemp & "'-'" & clng(txtQty.Text) for. Why not just
    Code:
    sSQL = "UPDATE TblStocks SET Quantity = Quantity - + & CLng(txtQty.Text) & " WHERE Code='" & txtCode.Text & "'"
    That should subtract the quanty in the txtQty field on the from from the Quanity field in the database.
    I tried your code and it gave me type mismatch error. When i debug it this is what is written in the immediate window.

    UPDATE TblStocks SET Quantity = Quantity + '0' - '1' WHERE Code='4800888121929'

    I think i should not be using the txtQty.text when i use the delete key there's no value in the textbox that's why i used this line
    Code:
    " & CLng(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE Code= ' & (lstStocks.ListItems.Item(edwin).Text) & '"
    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,725

    Re: Questions on Database Update

    Thanks to My Buddy Erick Widya for helping me!
    and to all who contributed their ideas.

    here's the correct code:
    [code]sSQL = "UPDATE tblStocks SET Quantity = Quantity + " & CLng(lstStocks.ListItems.Item(edwin).SubItems(2)) & " WHERE
    Code:
    = '" & (lstStocks.ListItems.Item(edwin).Text) & "'"
    Last edited by Simply Me; May 6th, 2006 at 12:17 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

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