SQL with C# - UPDATE not working but INSERT does work

Question

I am using SQLite with C# and have a table in a database that I want to insert a row into if the table is empty otherwise update the row in the table if there is a row in there. This table will only ever have one row so it isn't necessary to constrain the UPDATE command. If I comment out the update part and row count part to just leave the insert part then it inserts a row fine but the code below to implement the functionality I want throws an error in Visual Studio and I can't figure out why.

using (SQLiteConnection con = new SQLiteConnection(cs))
    {
        con.Open();

        using (SQLiteCommand cmd = new SQLiteCommand(con))
        {
            cmd.CommandText = "UPDATE details SET (initials = @init, participantID = @ID, affectedSide = @side, age = @age, comments = @comm) IF @@ROWCOUNT = 0 INSERT INTO details (initials, participantID , affectedSide , age, comments) VALUES (@init, @ID, @side, @age, @comm)";

            cmd.Parameters.AddWithValue("@init", InitalsTextBox.Text);
            cmd.Parameters.AddWithValue("@ID", IDTextBox.Text);
            cmd.Parameters.AddWithValue("@side", side);
            cmd.Parameters.AddWithValue("@age", ageindex);
            cmd.Parameters.AddWithValue("@comm", ParticipantText.Text);

            cmd.ExecuteNonQuery();
        }
        con.Close();
    }

The error:

An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll but was not handled in user code

Additional information: SQL logic error or missing database

near "(": syntax error

I'm not working with SQLite, but I doubt, that command like this will be parsed by client library you use. Split it into two separate commands, run update command first, and, if its `ExecuteNonQuery` will return 0, run insert command. — Dennis, Dec 01 '15 at 10:25
`@@ROWCOUNT` is T-SQL, `changes()` is the equivalent for SQLite, which also has no `IF` construct. Run the insert `where changes() = 0` — Alex K., Dec 01 '15 at 11:32
@Dennis it's working with your suggestion, thanks! I did have to remove the brackets as suggested — thoward, Dec 01 '15 at 11:33
Thanks @AlexK. that explains why it isn't working. I tried your suggestion but it didn't work, can you please give an example of the entire command — thoward, Dec 01 '15 at 11:43
http://stackoverflow.com/questions/20323174/upsert-in-sqlite / http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — Alex K., Dec 01 '15 at 11:50

score 0 · Answer 1 · answered Dec 01 '15 at 10:32

0

Try this one, Change your update query

UPDATE details SET initials = @init, participantID = @ID, affectedSide = @side, age = @age, comments = @comm IF @@ROWCOUNT = 0 INSERT INTO details (initials, participantID , affectedSide , age, comments) VALUES (@init, @ID, @side, @age, @comm)

answered Dec 01 '15 at 10:32

Neeraj Sharma

568
6
18

Still getting an error. Additional information: SQL logic error or missing database: near "IF": syntax error – thoward Dec 01 '15 at 11:18

score 0 · Answer 2 · answered Dec 01 '15 at 15:02

Just close the logic in stored procedure and run it with your parameteres

IF (SELECT COUNT(*) FROM details) > 0 BEGIN UPDATE details SET initials = @init, participantID = @ID, affectedSide = @side, age = @age, comments = @comm END ELSE BEGIN INSERT INTO details (initials ,participantID ,affectedSide ,age ,comments) VALUES (@init ,@ID ,@side ,@age ,@comm) END

score 0 · Accepted Answer · edited May 23 '17 at 12:23

0

As pointed out by @AlexK. in the comments on my question: SQLite does not have @@ROWCOUNT nor an IF construct and so different ways of achieving the desired functionality can be achieved differently. See these links:

UPSERT in SQLite

SQL with C# - UPDATE not working but INSERT does work

edited May 23 '17 at 12:23

Community

1
1

answered Dec 01 '15 at 15:30

thoward

349
3
12

SQL with C# - UPDATE not working but INSERT does work

3 Answers3