insert data to SQLite with button_click in C#

Question

I want to insert data to SQLite with my WPF project, and I hope after I click a button, I could save a data into the SQLite, here's my code.

    private void AddToDB_Click(object sender, RoutedEventArgs e)
    {
        sqlite_Conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

        sqlite_Conn.Open();

        sqlite_Cmd = sqlite_Conn.CreateCommand();

        sqlite_Cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";

        sqlite_Cmd.ExecuteNonQuery();

        sqlite_Cmd.CommandText = "INSERT INTO test (id, text) VALUES (1,'Test Text 1');";

        sqlite_Cmd.ExecuteNonQuery();

        sqlite_Cmd.CommandText = "SELECT * FROM test";

        sqlite_DataRdr = sqlite_Cmd.ExecuteReader();

        while (sqlite_DataRdr.Read())
        {
            System.Console.WriteLine(sqlite_DataRdr["text"]);
        }

        sqlite_Conn.Close();
    }

And my problem is, no matter how many times I click the button, it will store only 1 data, and I think the problem is because I create a new table after I click the button each time, so I change my code to :

        if (!created)
        {
            sqlite_Cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
            sqlite_Cmd.ExecuteNonQuery();
            created = true;
        }

but it get worse, it could only execute successfully for 1 time, in the second time I press the button to insert data, the program will stop at

sqlite_Cmd.ExecuteNonQuery();

after

sqlite_Cmd.CommandText = "SELECT * FROM test";

and showed {"no such table: test"} error.

Does anyone have any good idea to solve this? Thanks alot!

`id` is a primary key, but you're inserting multiple rows with the same id, so any subsequent inserts will fail due to a uniqueness constraint. — Pieter Witvoet, Jan 28 '15 at 08:04
@LasseV.Karlsen Oops! That's my fault... Sorry for my carelessness, and thank you for your remindness! I've solve it after I change the "New" to False, and comment sqlite_Cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));"; sqlite_Cmd.ExecuteNonQuery(); Very appreciate!! — Alanight, Jan 28 '15 at 08:28
@UlugbekUmirov Thanks for your remindness!! That's my fault, and you made it more obvious. Thanks alot!! — Alanight, Jan 28 '15 at 08:29
@PieterWitvoet Thanks for your advice, I've tried to change the "id", which is the primary key, but it still has only one data in the table. However I've found the problem! Very appreciate! — Alanight, Jan 28 '15 at 08:31

Ryan C · Answer 1 · 2015-01-28T04:14:41.320

I have dealt mainly with SQL and MySQL in the past, but both of those would require that you create separate SQLCommands for each command you would like to pass.

Instead try the following:

    sqlite_Conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

        sqlite_Conn.Open();
        sqlite_CreateCmd = new SQLiteCommand();
        sqlite_InsertCmd = new SQLiteCommand();
        sqlite_QueryCmd = new SQLiteCommand();
        sqlite_CreateCmd.Connection = sqlite_Conn;
        sqlite_InsertCmd.Connection = sqlite_Conn;
        sqlite_QueryCmd.Connection = sqlite_Conn;
        var sqlite_CreateTran = sqlite_Conn.BeginTransaction();
        var sqlite_InsertTran = sqlite_Conn.BeginTransaction();
        sqlite_CreateCmd.Transaction = sqlite_CreateTran;
        sqlite_InsertCmd.Transaction = sqlite_InsertTran;
        sqlite_CreateCmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
        sqlite_InsertCmd.CommandText = "INSERT INTO test (id, text) VALUES (1,'Test Text 1');";
        sqlite_QueryCmd.CommandText = "SELECT text FROM test";
        sqlite_CreateCmd.ExecuteNonQuery();
        sqlite_InsertCmd.ExecuteNonQuery();
        sqlite_DataRdr = sqlite_QueryCmd.ExecuteReader();
        sqlite_CreateTran.Commit();
        sqlite_InsertTran.Commit();
        System.Console.WriteLine(sqlite_QueryCmd.ExecuteScalar());
        sqlite_Conn.Close();

Thanks for your answer, I've tried but it still doesn't work. The logic was quite the same, isn't it? I hope there's a method to separate the "open connection" part from "insert" part, and I could still insert data as usual. — Alanight, Jan 28 '15 at 04:01
Check the answer from here: [link](http://stackoverflow.com/questions/6749270/system-data-sqlite-not-supporting-multiple-transactions). I believe you have to first create variables for the transactions and then assign each command a transaction. — Ryan C, Jan 28 '15 at 04:08

score 0 · Accepted Answer · answered Jan 28 '15 at 08:40

I've figure it out! I change the New property to False and eliminate

sqlite_Cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";

sqlite_Cmd.ExecuteNonQuery();

then the data could be inserted correctly. However, because I remove the part which will create the table, the table must be created before I execute this program. Thanks for your help!

insert data to SQLite with button_click in C#

2 Answers2