SQL Update Problem

[jeterry]

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:

 

[pamoxi]

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?

 

[jeterry]

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

Thanks for looking though

 

[Erel]

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.

 

[RB Smissaert]

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

 

[jeterry]

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: