SQLite Database is not open Error

Question

I have a constructor that takes data from a SQL Server database and puts it in a local SQLite database:

public ForemanController()
        {
            connectionString.DataSource = "dxdb02v";
            connectionString.InitialCatalog = "QTRAX4619410";
            connectionString.UserID = "tunnelld";
            connectionString.Password = "david";

            string queryString = "SELECT * FROM [QTRAXAdmin].[vwQT_Foreman]";

            List<Foreman> list;
            // Creates a SQL connection
            using (var connection = new SqlConnection(connectionString.ToString()))
            {
                using (var command = new SqlCommand(queryString, connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        list = new List<Foreman>();
                        while (reader.Read())
                        {
                            list.Add(new Foreman { ForeBadge = reader.GetString(0), ForeName = reader.GetString(1) });
                        }
                    }
                }
                connection.Close();
                allForeman = list.ToArray();
            }
            string deleteSQL = "DELETE FROM Foreman;";
            using (SQLiteConnection SQLconn1 = new SQLiteConnection(SQLiteConnectionString))
            {
                using (var command = new SQLiteCommand(deleteSQL, SQLconn1))
                {
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
            }

            using (SQLiteConnection SQLconn2 = new SQLiteConnection(SQLiteConnectionString))
            {
                SQLiteCommand cmd2 = SQLconn2.CreateCommand();
                foreach (Foreman row in allForeman)
                {
                    cmd2.CommandText = "INSERT INTO Foreman (ForeBadge, ForeName) VALUES (@param1, @param2);";
                    cmd2.Parameters.Add(new SQLiteParameter("@param1", row.ForeBadge));
                    cmd2.Parameters.Add(new SQLiteParameter("@param2", row.ForeName));
                    cmd2.ExecuteNonQuery();
                }
            }
        }

Everything seems to be working fine until the last using statement:

            using (SQLiteConnection SQLconn2 = new SQLiteConnection(SQLiteConnectionString))
            {
                SQLiteCommand cmd2 = SQLconn2.CreateCommand();
                foreach (Foreman row in allForeman)
                {
                    cmd2.CommandText = "INSERT INTO Foreman (ForeBadge, ForeName) VALUES (@param1, @param2);";
                    cmd2.Parameters.Add(new SQLiteParameter("@param1", row.ForeBadge));
                    cmd2.Parameters.Add(new SQLiteParameter("@param2", row.ForeName));
                    cmd2.ExecuteNonQuery();
                }
            }

I'm getting this error:

enter image description here

Its pretty evident, you don't open it like you do the others. You need a SQLconn2.Open() or cmd2.Connection.Open() somewhere. — Ron Beyer
– Ron Beyer, Commented May 26, 2015 at 20:16
Side note - that's WAY too much to be doing in a class constructor. I would seriously reconsider making it a separate method (or set of methods). Constructors should be light and should not throw any exceptions (since they are harder to debug) — D Stanley
– D Stanley, Commented May 26, 2015 at 20:18

walther · Accepted Answer · 2015-05-26 20:14:42Z

7

That's because that's the only place you forgot to open the connection.

add this: SQLconn2.Open();

answered May 26, 2015 at 20:14

walther

13.6k5 gold badges44 silver badges67 bronze badges

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

2 Comments

David Tunnell Over a year ago

It may be obvious but why don't I need to open the connection for the DELETE statement?

Tim Over a year ago

You DO open it for the DELETE statement: command.Connection.Open();

vmg · Accepted Answer · 2015-05-26 20:15:11Z

1

You forgot to open the connection.

SQLConn2.Open();

answered May 26, 2015 at 20:15

vmg

10.6k15 gold badges67 silver badges91 bronze badges

Collectives™ on Stack Overflow

SQLite Database is not open Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related