SQL Update Problem

Discussion in 'Questions (Windows Mobile)' started by jeterry, Jan 20, 2009.

  1. jeterry

    jeterry Member Licensed User

    I have a application for the device that allows the user to sale and print invoices for a store.

    The application prints the invoice and then updates the inventory table.

    The code that I am having a problem with is below in step 2.

    Step 1. text = "Update Sohead set soprt= "&numbprt&",sohpost = 'Y' where ROWID = "&sohrec
    cmd.CommandText = text
    cmd.ExecuteNonQuery
    reader.Close
    Step 2. text = "update Inventory set UnitsSold = UnitsSold+ (select sobody.soshipqty from sobody where sobody.sobnum = '"&sonumb&"'and sobody.soprod = inventory.ProductCode and sobpost = 'N')"
    cmd.CommandText = text
    cmd.ExecuteNonQuery
    reader.Close
    Step 3. Text = "update Sobody set sobpost = 'Y' where sobnum ="&sonumb&" and sobpost = 'N'"
    cmd.CommandText = text
    cmd.ExecuteNonQuery
    reader.Close
    Step 1 is to set the number of times the user printed the invoice.
    Step 2 is to set the inventory quantity sold.
    Step 3 is to set the sales order body indicator as posted to inventory

    The sobody.soshipqty is quantity sold to a customer.

    The update inventory part works great if the user prints the invoice one time. However if the user prints the invoice a second time, the UnitsSold field is set to zero.

    Does anyone have any ideals on what is wrong with this code and any suggested fixes.

    Thanks:sign0085:
     
  2. pamoxi

    pamoxi Member Licensed User

    In the first line you put:

    Step 1. text = "Update Sohead set soprt= "&numbprt&",sohpost = 'Y' where ROWID = "&sohrec

    but then use Sobpost, is this a type or code error or a different variable?
     
  3. jeterry

    jeterry Member Licensed User

    No it's just a different variable, to distinguish between the two tables.

    Thanks for looking though
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    I'm not a SQL expert, but I recommend you to break the second query into two queries.
    You will need two Command objects and two Reader objects.
    Read all the new sales and for each one update the UnitsSold value.
     
  5. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    Not sure it will solve the problem you are having but I can see 2 area's of improvement.
    Firstly, you don't have to do Reader.Close as it is not used as this are all non-select queries. Actually, Reader.Close might cause an error if it wasn't opened before, come to think of it.
    Secondly, instead of adding all the variables in the SQL I would use AddParameter and SetParameter, which might be faster (but not much if you don't run it in a loop) and will avoid the hassle of dealing with the single quotes.

    RBS
     
  6. jeterry

    jeterry Member Licensed User

    Thanks everyone for the input.

    I had originally started down the path as suggested by Erel. I just thought that one line of sql code would be simpler.

    Works now using Erel suggestion.

    Thanks All :sign0188:
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice