How do I append a new id to record

Question

I have a problem, basically I want the id in database to autoincrement and add more users, however when i try to add a 2nd record, it drops an error such as:

constraint failed
Unique constraint failed: DANE.id

this is my inserting method:

 private void LoadToDb_Click(object sender, EventArgs e)
        {
            var modelData = new Data();
            var db = new SqlDb();
            var sqLiteConnection = db.Connect();
            var sqLiteCommand = sqLiteConnection.CreateCommand();

            try
            {
                modelData.Name = firstName.Text;
                modelData.Age = Convert.ToInt32(DisplayAge.Text);
                modelData.LastName = LastName.Text;

                sqLiteCommand.CommandText =
                    $"insert into DANE values('{modelData.Name}', '{modelData.LastName}', '{modelData.Age}', {Interlocked.Increment(ref modelData.Id)})";
                sqLiteCommand.ExecuteNonQuery();

                db.Disconnect();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

This is my Data Model

    public class Data
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
        public int id = 0;
    }

and this is sqLite database pattern (I use db browser for that)

CREATE TABLE "DANE" (
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL,
    "Age"   INTEGER NOT NULL,
    "id"    INTEGER NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

first use parameters .. second do not add id parameter ... execute non query should return new id — Selvin, Nov 23 '20 at 22:46
[This will sound familiar:](https://stackoverflow.com/questions/64971745/cant-insert-the-record-to-database-c-sharp-sqlite), that is a very dangerous and very, very error prone way to concoct SQL queries. Use SQL Parameters always. Your connections also ought to be disposed of when you are done with them. — Ňɏssa Pøngjǣrdenlarp, Nov 23 '20 at 22:54

score 0 · Answer 1 · answered Nov 23 '20 at 23:13

You are using Data.Id as primary key, but you create a new instance every time. So your primary key will always be 1 (0 as soon as you create the new instance, then 1 when you call Interlocked.Increment on it, which btw is probably not needed).

Also you are generating the primary key both in your code and in the database. Choose the one you prefer, no need to do it twince.

T.S. · Answer 2 · 2020-11-24T00:02:27.293

your table defined with "id" INTEGER NOT NULL, PRIMARY KEY("id" AUTOINCREMENT)

So, your insert needs to be like

$"insert into DANE (FirstName, LastName, Age) 
  values('{modelData.Name}', '{modelData.LastName}', '{modelData.Age}')";

Basically, you don't list auto increment column in INSERT. But you need to retrieve inserted record value based on SqlLite-specific DB syntax and provider capabilities. Each DB engine and its .NET provider has its own way of doing it.

This should help you

TIP: SQLiteConnection.LastInsertRowId

score 0 · Answer 3 · answered Nov 24 '20 at 14:31

DEFINE TABLE LIKE THIS:

CREATE TABLE "DANE" (`enter code here`
     "id"    INTEGER NOT NULL AUTO_INCREMENT,
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL,
    "Age"   INTEGER NOT NULL,
);

OR

var NewID =  DB.DANE.Max(x => x.id) + 1;

and than add NewID as id in your insert query.

How do I append a new id to record

3 Answers3