C# SQlite INSERT INTO does not work

Question

I am a beginner in SQLite. Now I am trying to insert new record in sample northwind database. My C# Code is below:

       SQLiteConnection myConnection = new SQLiteConnection();

        myConnection.ConnectionString = @"Data Source=|DataDirectory|\northwindEF.db";

        using (myConnection)
        {
            SQLiteCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "INSERT INTO Categories(CategoryID,CategoryName,Description) VALUES(11,'Bakery','Baked goods such as bread and cakes')";
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }

When i run this program, i didn't get any error. But my new record is not inserted into table. What wrong with my code. Please help me.

I am not sure but if CategoryId is primary key, change your query like this : "INSERT INTO Categories(CategoryName,Description) VALUES('Bakery','Baked goods such as bread and cakes')" — Müslüm ÖZTÜRK
– Müslüm ÖZTÜRK, Commented Mar 20, 2013 at 22:13
CategoryID is primary key. I change my query as you said, but the problem still occur. :-( — Zaw Thurein Tun
– Zaw Thurein Tun, Commented Mar 20, 2013 at 22:23
SQLiteCommand may require the CommandText property be terminated with a semicolon. — pixelbadger
– pixelbadger, Commented Mar 20, 2013 at 22:24
Do you have the database file listed in your project files? If is listed there, what is the value for the property 'Copy to the output directory' ? and what is your connection string? — Steve
– Steve, Commented Mar 20, 2013 at 22:53

Steve · Accepted Answer · 2013-03-20 23:07:51Z

5

It is a common pitfall. A connection in server Explorer is build to the database file stored in the project folder. Then, at Runtime the database is copied to the output directory and every insert, update, delete works against that database. In Server Explorer nothing changes because the database is different. The same happens also if the property 'Copy to the output directory' is set to Copy always. In this case at every run the database in the output directory is overwritten with a fresh copy taken from the project root folder and again every delete, update or insert disappears at the next run.

answered Mar 20, 2013 at 23:07

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

Dour High Arch · Accepted Answer · 2013-03-20 23:06:12Z

0

Why do you think the "new record is not inserted"? ExecuteNonQuery will tell you how many records were affected; what does it return?

How are you running the program? From the debugger? DataDirectory is the folder the data was installed to. If you have not changed something in your .sdf file project properties, Visual Studio installs data to the project\debug\bin\ folder. Look for an .sdf file there, is your "new record" there?

answered Mar 20, 2013 at 23:06

Dour High Arch

21.8k31 gold badges78 silver badges93 bronze badges

Comments

user8617441 · Accepted Answer · 2017-09-17 12:38:38Z

try coding like this, it is always check if connection has an error or your query has an error.

using (SQLiteConnection con = new SQLiteConnection { ConnectionString = "connectionstring" })
{
     using(SQLiteCommand cmd = new SQLiteCommand { Connection = con })
     {
         // check connection state if open then close, else close proceed
         if(con.State == ConnectionState.Open)
           con.Close();

         //then
         try
         {
            // try connection to Open
            con.Open();
         }
         catch
         {
            // catch connection error.
         }

         try
         {
             // your insert query
         }
         catch
         {
             // catch query error
         }

     } // Close Command

}  // Close Connection

Collectives™ on Stack Overflow

C# SQlite INSERT INTO does not work

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related