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.

NewDatabaseandInsertNode.