SQLite doesn't save data I inserted

Question

I'm messing around with SQL lite and learning it.

I got a table called People, I got some method that connect to the database and do some stuff, like show all the info.

Now I'm trying to insert some data and here it get wierd for me. I have this method:

private void ExecuteQuery(string txtQuery)
{
    SetConnection();
    sql_con.Open();
    sql_cmd = sql_con.CreateCommand();
    sql_cmd.CommandText = txtQuery;
    sql_cmd.ExecuteNonQuery();
    sql_con.Close();
}

and to see all the data I've got this method:

private void LoadData()
{
    SetConnection();
    sql_con.Open();
    sql_cmd = sql_con.CreateCommand();
    string CommandText = "SELECT * FROM People";
    DB = new SQLiteDataAdapter(CommandText, sql_con);
    DS.Reset();
    DB.Fill(DS);
    DT = DS.Tables[0];
    dataGridView1.DataSource = DT;
    sql_con.Close();
}

When I inset some data and right afther it I call the LoadData(), I can see all the changes I made.

After I close the program, and then open it agian and call LoadData(), I don't see the new info that I inserted before.

I got some data that I used SQL lite GUI app to insert, and I can see that data every time I call the LoadData() method, but not mine.

Do I need to do somthing else to make sure SQL lite saves all the data?

EDIT: this is how i INSERT the data, mybee the problem is from here?

string insetCommand = "INSERT INTO People Values(2,'" + textBox1.Text + "')";

EDIT2: my connectionString Method

public static string connectionString
    {
        get
        {
            string database =
                //AppDomain.CurrentDomain.BaseDirectory + "..\\..\\Database\\ImageLib.s3db";
               AppDomain.CurrentDomain.BaseDirectory + "\\DataBase\\myTable2";
            string connectionString =
                @"Data Source=" + Path.GetFullPath(database) + ";Version=3;";
            return connectionString;
        }
    }

By any chance, do you have the SQLite database as part of the Visual Studio project? — Gigi
– Gigi, Commented Sep 15, 2012 at 18:15
You should be using a .db3 file (or equivalent) somewhere. Is this included in the VS solution, or are you simply loading it as a separate file? — Gigi
– Gigi, Commented Sep 15, 2012 at 21:50

CL. · Accepted Answer · 2012-09-15 18:12:16Z

1

Your problem looks as if your data is stored in a temporary database.

Please check that your connectionString is actually used (SQLite will use a temporary database if the file name is empty).

To check the database file name from inside your application, execute PRAGMA database_list like a query and check that the third column of the result contains your file name. Example output:

> pragma database_list;
seq   name   file
----- ------ ----------
0     main   /tmp/test.db

answered Sep 15, 2012 at 18:12

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

samy Over a year ago

change the name of my dababase file to myTable2.db and in the connection string too and now it works. thank u!

samy Over a year ago

wierd, i did a few test and it still not working. how to i execute the PRAGMA database_list? (i dont know it). thx

CL. Over a year ago

Instead of string CommandText = "SELECT * FROM People"; use string CommandText = "PRAGMA database_list";.

samy Over a year ago

it return: 0 main C:\Users\roei\documents\visual studio 2010\Projects\SmarterSqlLiteTest\SmarterSqlLiteTest\bin\Debug\DataBase\myTable2.db

CL. Over a year ago

There's your file. Check if its time stamp changes after inserting, or if it might be automatically deleted when rebuilding.

|

aweis · Accepted Answer · 2012-09-15 16:29:54Z

0

Try to execute a commit statement, i belive that SQLite needs that! You can also try to wrap it all in a transaction!

answered Sep 15, 2012 at 16:29

aweis

5,6564 gold badges38 silver badges54 bronze badges

2 Comments

samy Over a year ago

as u said i tried do a transction, but i get the same result. i can see the changes in the table when the program work. but, when i close it and then re open it, i cant see the new data i added with the transction. any other ideas?

CL. Over a year ago

When you're not using BEGIN/END TRANSACTION commands, SQLite will automatically commit after each command.

Collectives™ on Stack Overflow

SQLite doesn't save data I inserted

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related