Results 1 to 8 of 8

Thread: [RESOLVED] -1 in mysql?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2006
    Posts
    35

    Resolved [RESOLVED] -1 in mysql?

    just a quick question really. Is it possible to -1 from a mysql record when an order is submitted

    for example,

    1 item is added to order, when "create invoice" is clicked, it writes the order to the mysql database.

    is there a mysql query i can use to -1 from the value of stock located in another table?

  2. #2
    Frenzied Member d3gerald's Avatar
    Join Date
    Jan 2006
    Posts
    1,348

    Re: -1 in mysql?

    yes it is posible
    VB Code:
    1. 'your SQL statement should look like this one
    2. update [TableName] set [FieldName] = [FieldName] - 1 Where [PrimaryKeyFieldName] = [your parameter or primarykey identifier]
    On error goto Trap

    Trap:
    in case of emergency, drop the case...

    ****************************************
    If this post has been resolved. Please mark it as "Resolved" by going through the "Thread Tools" above and clicking on the "Mark Thread Resolved " option.
    if a post is helpful to you, Please Rate it by clicking on the Rate link right below the avatar

  3. #3
    Frenzied Member d3gerald's Avatar
    Join Date
    Jan 2006
    Posts
    1,348

    Re: -1 in mysql?

    supposing you want to update the AvailableStocks field of your table named Items whose primary key is StockNumber and the record you want to update is stocknumber '01'

    update Items set AvailableStocks = AvailableStocks - 1 where StockNumber = '01'

    also, when you post next time, try to provide adequate information about what you want, its hard to solve a problem by just guessing what the problem is.
    On error goto Trap

    Trap:
    in case of emergency, drop the case...

    ****************************************
    If this post has been resolved. Please mark it as "Resolved" by going through the "Thread Tools" above and clicking on the "Mark Thread Resolved " option.
    if a post is helpful to you, Please Rate it by clicking on the Rate link right below the avatar

  4. #4

    Thread Starter
    Member
    Join Date
    Apr 2006
    Posts
    35

    Re: -1 in mysql?

    Code:
    lstrSQL = "UPDATE stock " & _
                 "SET stockcount = '" & stockcount - 1 & "' " _
                & "WHERE itemid = '" & itemid1.Text & "'"
    This just inserts stockcount -1 in to the database.


    Maybe if i call the value first showing how many is stock i'll be able to then -1 from the level that way

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2006
    Posts
    35

    Re: -1 in mysql?

    Right so i've now called the stock count in to the form



    Code:
    lstrSQL = "UPDATE stock " & _
                 "SET stockcount = '" & item1stockcount.text & "' " _
                & "WHERE itemid = '" & itemid1.Text & "'"
    how would i go about -1 from the above code?

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: -1 in mysql?

    The reason that this failed:
    VB Code:
    1. lstrSQL = "UPDATE stock " & _
    2.              "SET stockcount = '" & stockcount - 1 & "' " _
    3.             & "WHERE itemid = '" & itemid1.Text & "'"
    ..is because you tried to use a VB variable called stockcount, instead of the field stockcount.

    What you should have (assuming that the field is numeric, as it should be) is the format that d3gerald showed you, ie:
    VB Code:
    1. lstrSQL = "UPDATE stock " & _
    2.              "SET stockcount = stockcount - 1 " _
    3.             & "WHERE itemid = '" & itemid1.Text & "'"

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2006
    Posts
    35

    Re: -1 in mysql?

    I'll change the database from varchar to int.

    WIll this apply to all values. If i have itemid2, itemid3 etc.. i would just go about changing

    Code:
    lstrSQL = "UPDATE stock " & _
                 "SET stockcount = stockcount - 1 " _
                & "WHERE itemid = '" & itemid1.Text & "'"
    to
    Code:
    lstrSQL = "UPDATE stock " & _
                 "SET stockcount = stockcount - 1 " _
                & "WHERE itemid = '" & itemid2.Text & "'"

  8. #8

    Thread Starter
    Member
    Join Date
    Apr 2006
    Posts
    35

    Re: -1 in mysql?

    Thanks for the help guys i got it work.

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