System.Data.SQLite

 Hello,

I ahve a table called 'allPurchases' with  3 fields, two integer fieldscalled Barcode and numberPurchased, and a date field called Date.

I also have an array of barcode(integers) called items.

I am trying to create a query that

1. Inserts the barcode, the number purchased (1) and the date into a the table 'allPurchases'  -  if there is not a record with the same barcode and date
   
2. Else if the row already exists, add 1 to the number purchased.

I have read many threads on the internet about upsert and not being able to implement it in sqlite. Also i have tried various methods already and not had any luck.

My current code is:

date = getdate();

SQLiteConnection DB = new SQLiteConnection("Data source=TransactionDatabase.db");
            DB.Open();

            using (SQLiteCommand DBc = DB.CreateCommand())
            {
                DBc.CommandText = "CREATE TABLE IF NOT EXISTS 'allPurchases' (" +
                    "'Barcode' integer, " +
                    "'numberSold' integer, " +
                    "'Date' date)";
                DBc.ExecuteNonQuery();
            }
            foreach (int barcode in itemsPurchased)
            {    
                using (SQLiteCommand DBc = DB.CreateCommand())
                {
                    DBc.CommandText =  "IF EXISTS UPDATE 'allPurchases' SET numberSold = numberSold + 1 " +
                                                     "WHERE Barcode = "+ barcode +" and Date = "+ date +"" +
                                                     "ELSE INSERT INTO 'allPurchases' (Barcode, numberSold, Date) VALUES ('" + barcode + "', 1, '" + date + "')";                    DBc.ExecuteNonQuery();
                }
            }

This is not working. Does anyone have nay suggestions??

Many thanks

Dave        

 I have carried on trying to get thisto work.

The following satement seems to do the update or insert correctly however it seems to make the numberSold equal to 2 when it does an insert. This is the case 99% of the time but the other 1% of the time it does work.

DBc.CommandText = "replace into 'allPurchases' (Barcode, Date, numberSold)" +
                                "values ('" + item + "', '" + dateTime + "', coalesce((select numberSold from 'allPurchases' " +
                                "where Barcode = '" + item + "' and Date = '" + dateTime + "') + 1,1))";
DBc.ExecuteNonQuery();

 Any ideas as to why?? Im confussed

 use the command   INSERT OR REPLACE

See:

http://www.sqlite.org/lang_insert.html

I have read and tried this and it still doesnt give me the result i want.

I want to insert a row into a table. If the row is already there, i want to increment the numberSold.

:(

You're right.  The field you need for incrementing can't be referenced in this kind of query.