Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    31

    Question Unanswered: Adjust values in db base in the value modified on the form?

    Hi everyone,

    Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

    Thanks for your help!

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by mumick
    Hi everyone,

    Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

    Thanks for your help!
    Paste your query or the tables involved and explain what you want in new one?
    Last edited by rudra; 04-03-06 at 12:14.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Jan 2005
    Posts
    31
    I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

    Ex. #1 - Discount value is changed
    Price is $100
    Quantity was 0 (changed to) 2
    Discount was 0% (changed to) 10%
    TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180


    Ex. #2 - Discount and TotalSellPrice values are changed
    Price is $100
    Quantity was 0 (changed to) 2
    Discount was 0% (changed to) 10%
    TotalSellPrice $0 (changed to) $150
    Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180

    Ex. #3 - TotalSellPrcie value is changed
    Price is $100
    Quantity was 0 (changed to) 2
    Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
    TotalSellPrice $0 (changed to) $150

    Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
    Price is $100
    Quantity was 0 (changed to) 2
    Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
    TotalSellPrice $0 (changed to) $250

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by mumick
    I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.
    Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
    An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by rudra
    I think you can easily do it at the front end and send the update query to the database according to its invoice no.
    The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.



    Any idea?

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by mumick

    Any idea?
    And you need not have to create a stored procedure for such a small calculation.I think so...
    Last edited by rudra; 04-03-06 at 13:09.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Jan 2005
    Posts
    31
    Maybe I explained it incorrectly, I am sorry.
    What I do I initially populate those fields with the default data from the database

    Ex. - Initial page load
    Price -> "$100"
    Quantity -> "0"
    Discount -> "0%"
    TotalSellPrice -> "$0"

    After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by mumick
    Maybe I explained it incorrectly, I am sorry.
    What I do I initially populate those fields with the default data from the database

    Ex. - Initial page load
    Price -> "$100"
    Quantity -> "0"
    Discount -> "0%"
    TotalSellPrice -> "$0"

    After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
    So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mumick
    After user updates any of writable fields I do update and populate that form again but base on the rules I described before...
    You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?

    It's not, and you certainly wouldn't design scalable enterprise applications this way.

    Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.

    And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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