0

I'm using C# and SQLite and struggling to create a memory database and add two blank tables using one method, and add data to the tables using a second method (in the same class).

When I run the code below I get the error:

"SQL logic error no such table: node_table"

Does anyone know why this error occurs?

Methods provided below (with some comments where I have tried different things with no luck)

    public static void NewDatabase()
    {

        Debug.WriteLine("NewDatabase has run");
        //
        //using "file:memdb1?mode=memory&cache=shared" triggered the error:
        //Data Source cannot be empty.  Use :memory: to open an in-memory database
        var m_dbConnection = new SQLiteConnection("Data Source=:memory:");
        m_dbConnection.Open();

        string sql;

        sql = "CREATE TABLE node_table(id INTEGER PRIMARY KEY, node_name TEXT, x REAL, y REAL, z_cover REAL)";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command.ExecuteNonQuery();

        sql = "CREATE TABLE conduit_table(id INTEGER PRIMARY KEY, conduit_name TEXT, usmh_id REAL, dsmh_id REAL, length REAL)";
        command = new SQLiteCommand(sql, m_dbConnection);
        command.ExecuteNonQuery();

        m_dbConnection.Close(); //Tried toggling this on and off


    }

    public static void InsertNode(string node_name, float x, float y, float z_cover)
    {
        var m_dbConnection = new SQLiteConnection("Data Source=:memory:");
        m_dbConnection.Open();
        string sql = "INSERT INTO node_table(node_name, x, y, z_cover) VALUES (@node_name, @x, @y, @z_cover)";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command.Parameters.AddWithValue("@node_name", node_name);
        command.Parameters.AddWithValue("@x", x);
        command.Parameters.AddWithValue("@y", y);
        command.Parameters.AddWithValue("@z_cover", z_cover);
        command.ExecuteNonQuery();
        //Error reads: SQL logic error no such table: node_table
    }

EDIT:

NewDatabase is called in the main entreypoint as follows:

namespace MainForm
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Parent());
            SqlConnections.NewDatabase();

        }
    }
}

and InsertNode is called on a click event in winform

    private void Plan_Load(object sender, EventArgs e) //need to change the name of this function
    {
        //Thic code gets the cursor position relative to the screen
        //this.Cursor = new Cursor(Cursor.Current.Handle);

        if (Globals.addNode == true)
        {
            var relativePoint = this.PointToClient(Cursor.Position);

            float x = relativePoint.X;
            float y = relativePoint.Y;
            SqlConnections.InsertNode("1", x, y, 2);
        }
    }

EDIT: Installed version of SQLite shown below. Documentation isn't readily available so been relying on the main SQLite documentation.

SQL Version Information

1
  • Please show us the code that calls NewDatabase and InsertNode. Commented Jul 24, 2018 at 21:27

1 Answer 1

3

When you close the last connection, the in-memory database is gone.

Keep the connection open and pass it between your functions.

Alternatively, keep at least one connection open, maybe a different one.

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

1 Comment

Hi, thanks for the suggestions. I usually have the m_dbConnection.Close(); line commented out and this doesn't help. I have also tried declaring m_dbConnection = new SQLiteConnection("Data Source=:memory:"); as a global variable so I don't have to redeclare the connection and I can just connect and it still throws the table name error If I put it all in one method it works fine I feel it might be lacking a caching setting but it throws an error if I try adding one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.