Data is not showing/updating in the SQLite table when the data is inserted through my C# code

Question

Currently, I have been doing some simple coding related to SQL and C#. I have created this a registration form which will store the data, username and password, in an SQLite database. This is the code that I am currently using:

private void AddLoginInfo(string username, string password)
    {
        auth = new Authentication();
        auth.getConnection();

        using (SQLiteConnection con = new SQLiteConnection(auth.connectionstring))
        {
            SQLiteCommand cmd = new SQLiteCommand();
            con.Open();

            string que = @"INSERT INTO LoginInfo(Username, Password) VALUES (@username, @password)";
            cmd.CommandText = que;
            cmd.Connection = con;
            cmd.Parameters.Add(new SQLiteParameter("@Username", username));
            cmd.Parameters.Add(new SQLiteParameter("@Password", password));
            cmd.ExecuteNonQuery();

            MessageBox.Show("Account Created!");
        }
    }

This code currently works and does add to the correct table in the database however whenever a new user is added. The user information can be used to login but it does not show within the table, which is viewed using DB Browser for SQLite.

For example, if I create a new user; 'admin' as the username and 'password' as the password, through the form, I get the message box saying 'Account Created' and I can use that very account to login. However, if I go view that very data in the DB browser the data doesn't show even after refreshing the table.

After doing some digging, I found this and saw that they were using sqlCommand.Parameters.AddWithValue so I tried this within my code:

SQLiteCommand cmd = new SQLiteCommand(@"INSERT INTO LoginInfo(Username, Password) VALUES (@username, @password)", con);
cmd.Parameters.AddWithValue(new SQLiteParameter("@Username", txtBoxUsername.Text));

I tried this and I got a CS7036 error. Then I realised that they had not used the new SQLiteParameter() part and so I removed it cmd.Parameters.AddWithValue("@Username", txtBoxUsername.Text); and tried again but it still wouldn't update in the table but the data could still be used to log in.

I have also found a similar post but no one had answered it. Now I don't know what to do, so I am asking for your help.

Have you tried another browser to look at you SQLite database? Maybe it's a SQLite program bug? Since you can use the new user and password to login, I guess that the query works. I use http://www.sqliteexpert.com/ to open my SQLite databases — Danielle Paquette-Harvey, Feb 28 '19 at 17:58
@DaniellePaquette-Harvey I just downloaded SQL Expert and opened the database and table and it still doesn't show the table with the new data. Maybe it is just a bug but I still want to see if I can fix it. — Sonny P., Feb 28 '19 at 18:04

score 7 · Accepted Answer · answered Mar 02 '19 at 17:23

7

I figured out what was the problem; I was checking the database that was stored in the solution but what I should have checked is the database stored in the ...\bin\debug folder. The database within the folder shows the added data.

answered Mar 02 '19 at 17:23

Sonny P.

105
2
11

2

Nobody has mentioned this anywhere else and this just saved me so much time. Thanks a ton! – Ocheezy Dec 18 '20 at 18:42

Neil · Answer 2 · 2019-02-28T18:22:04.237

2

Are you retrieving the db file from another system? i.e. Android Emulator or a network location?

Are there any other files in the folder, you are specifically looking for a filename that contains '-wal'. Additional information on SQLite Wal format

the data is actually contained in this other file. DB Browser knows to look for this file. However, if you are downloading the db to your system before you take a look, you are probably not downloading the -wal file as well.

Depending on how you are configuring your SQLite db, the limit by default for checkpoint threshold is 1kb. Until the checkpoint is triggered, the data doesn't get transferred to the main db file. There are ways to manually trigger the checkpoint, see the link I have included above.

edited Feb 28 '19 at 18:22

answered Feb 28 '19 at 18:06

Neil

629
5
14

I have not retrieved the db file from another system, I have created it myself through DB Browser. As for this '-wal' file, I am unable to find such file in the area I had stored the .db file – Sonny P. Feb 28 '19 at 18:12
and you don't see a '-wal' file in the folder? – Neil Feb 28 '19 at 18:16
No, I do not see the file. – Sonny P. Feb 28 '19 at 18:17
I have just found the file, it was hidden behind a hidden folder. – Sonny P. Feb 28 '19 at 18:19
hmmm... Sorry Sonny. I had run into a similar problem a day or two ago when I was pulling the db from an Android Emulator. I will post back if I think of something else. – Neil Feb 28 '19 at 18:19
Oh that's fantastic! put the db file and the other file in the same folder. Now you should be able to view the data that is being added to the system. – Neil Feb 28 '19 at 18:20
Your link doesn't seem to work on my end. Can you repost it please. – Sonny P. Feb 28 '19 at 18:20
Just updated the link in the answer, but here is the link again - https://www.sqlite.org/walformat.html – Neil Feb 28 '19 at 18:22
Do I put the db file in the folder of where I found the '-wal' file or vice versa? – Sonny P. Feb 28 '19 at 18:29
It doesn't matter which way you go. As long as they are both in the same folder. – Neil Feb 28 '19 at 18:39
I put them in the same folder but the data still doesn't appear. – Sonny P. Feb 28 '19 at 18:53
I had to update my version of DB Browser, see if you have the latest version. There might be permission issues if DB Browser doesn't have permissions to write in the folder or delete a file. – Neil Feb 28 '19 at 19:28

Data is not showing/updating in the SQLite table when the data is inserted through my C# code

2 Answers2