Insert data into sqlite in-memory database

Question

So I have a in-memory database and I'm trying to insert data into the table and it's not working. Am I doing something wrong?

var connection = new SQLiteConnection("DataSource=:memory:;Version=3;New=True;");

connection.Open();

string sql = "CREATE TABLE  recently_viewed(movieid INTEGER,picture TEXT)";

SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();

using ( command = connection.CreateCommand())
{
    command.CommandText = "INSERT into recently_viewed(movieid ,picture) values(@movieid,@picture)";
    command.Prepare();
    command.Parameters.AddWithValue("@movieid", id);
    command.Parameters.AddWithValue("@picture", picture);
}

string   count_table = " SELECT count(*) FROM recently_viewed";

SQLiteCommand   com3 = new SQLiteCommand(count_table, connection);

int   ctable = Convert.ToInt32(com3.ExecuteScalar().ToString());

Response.Write(ctable);

connection.Close();

"It's not working" is not a description of a problem. Please tell us what doesn't work, what happens when you run it, and any exceptions you get. — Ron Beyer
– Ron Beyer, Commented Aug 17, 2015 at 19:13

Mbithy Mbithy · Accepted Answer · 2015-08-17 19:24:42Z

2

I just noticed from your code that this line:

command.ExecuteNonQuery();

is called only once. What happens when you do:

var connection = new SQLiteConnection("DataSource=:memory:;Version=3;New=True;");

connection.Open();

string sql = "CREATE TABLE  recently_viewed(movieid INTEGER,picture TEXT)";

SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();

using ( command = connection.CreateCommand())
{
    command.CommandText = "INSERT into recently_viewed(movieid ,picture) values(@movieid,@picture)";
    command.Prepare();
    command.Parameters.AddWithValue("@movieid", id);
    command.Parameters.AddWithValue("@picture", picture);
    command.ExecuteNonQuery();
}


string   count_table = " SELECT count(*) FROM recently_viewed";

SQLiteCommand   com3 = new SQLiteCommand(count_table, connection);
com3.ExecuteNonQuery();

int   ctable = Convert.ToInt32(com3.ExecuteScalar().ToString());

Response.Write(ctable);

connection.Close();

edited Aug 17, 2015 at 19:24

answered Aug 17, 2015 at 19:21

Mbithy Mbithy

16313 bronze badges

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

1 Comment

Jerry Federspiel Over a year ago

Put the second ExecuteNonQuery inside the using block; after that block command will be Disposed.

Collectives™ on Stack Overflow

Insert data into sqlite in-memory database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related