SQL Update Problem

jeterry

Member
Licensed User
Longtime 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:
 

pamoxi

Member
Licensed User
Longtime 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?
 

jeterry

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

Thanks for looking though
 

RB Smissaert

Well-Known Member
Licensed User
Longtime 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
 

jeterry

Member
Licensed User
Longtime 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:
 
Top