Adding 2 fields with SQL

[rahulpatel]

I'm trying to add 2 fields from a database using SQL. They are both numbers and once added together and stored in another field, the result will be displayed in a text box. However, I have tried every permutation of fields and adding I can think of (apart from the correct one obviously) and I cannot get the thing to work.

SQL = "UPDATE Stores SET quantity = (quantity + quantityin) WHERE barcode = '" & (txtSearch.Text) & "'"

What I was hoping this would do is update my db and make the field 'quantity' equal to quantity value plus whatever number was in 'quantityin'. But it doesn't work.

Can anyone enlighten me as to how to do it?

Cheers...

 

[JoeAtWork]

Assuming quantity and quantityin are both fields in the Stores table, and that barcode is a text field, the SQL statement looks correct to me.

The crucial thing to know is, in what way is it not working? Are you getting an error, and if so what is it? If no error, then perhaps the SQL is syntatically correct, but not logically - for example maybe the WHERE clause does not apply to any record, or applies to a different record than you think.

Also, show whatever code actually invokes the SQL. Are you using ADO, and what database are you using (Access, SQL Server, etc.)?

 

[rahulpatel]

Thanks JoeAtWork, I checked my fields and barcodein was indeed an integer. Changed it to text and the SQL works like a charm.

How did you know from the code I posted that the 'barcodein' field had to be text as opposed to an int?

 

[JoeAtWork]

It's a very common mistake to treat numeric fields as text or vice-versa. Since the syntax looked valid, I was looking for something where your assumptions about the data might be wrong.