C#•2y ago

✅ Database cmd.ExecuteScalar() ERROR

Im trying to import a database to another

13 Replies

Silme94OP•2y ago

message.txt

Silme94OP•2y ago

this part

viceroypenguin•2y ago

Can you share a full code repository?

Silme94OP•2y ago

https://github.com/Silme94/project

GitHub

GitHub - Silme94/project

Contribute to Silme94/project development by creating an account on GitHub.

viceroypenguin•2y ago

@Silme94 order of operations: * line 463: connection.Open() * line 476: InsertThematic(), which both opens and closes connection * line 477: attempts to use the connection that was closed inside of InsertThematic()

Silme94OP•2y ago

alr

viceroypenguin•2y ago

also, the fact that you close the connection inside of InsertThematic() means that when you reopen it, the last_insert_rowid() is no longer relevant. that valud is only valid for a single open session. closing the connection means that information is lost and can't be accessed by opening a new connection to the db. you might update InsertThematic to return an int, and inside of InsertThematic(), before closign the connection, do that GetLastInsertId()

private int ExecuteNonQueryWithId(string query, params SQLiteParameter[] parameters)
{
    try
    {
        connection.Open();
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            command.Parameters.AddRange(parameters);
            command.ExecuteNonQuery();
        }
        using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", connection))
        {
            return Convert.ToInt32(command.ExecuteScalar());
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return -1;
    }
    finally
    {
        connection.Close();
    }
}


public int InsertThematic(string name)
    => ExecuteNonQueryWithId("INSERT INTO Thematic (Name) VALUES (@Name)", new SQLiteParameter("@Name", name));

private int ExecuteNonQueryWithId(string query, params SQLiteParameter[] parameters)
{
    try
    {
        connection.Open();
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            command.Parameters.AddRange(parameters);
            command.ExecuteNonQuery();
        }
        using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", connection))
        {
            return Convert.ToInt32(command.ExecuteScalar());
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return -1;
    }
    finally
    {
        connection.Close();
    }
}


public int InsertThematic(string name)
    => ExecuteNonQueryWithId("INSERT INTO Thematic (Name) VALUES (@Name)", new SQLiteParameter("@Name", name));

Silme94OP•2y ago

it will work the same for

string themeQuery = "SELECT * FROM Theme";
using (SQLiteCommand command = new SQLiteCommand(themeQuery, externalConnection))
using (SQLiteDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        int oldThematicId = Convert.ToInt32(reader["ThematicId"]);
        int newThematicId = thematicMapping[oldThematicId];
        string name = reader["Name"].ToString();
        InsertTheme(newThematicId, name);
        int newId = GetLastInsertId();
        themeMapping[Convert.ToInt32(reader["Id"])] = newId;
    }
}

string questionQuery = "SELECT * FROM Question";
using (SQLiteCommand command = new SQLiteCommand(questionQuery, externalConnection))
using (SQLiteDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        int oldThemeId = Convert.ToInt32(reader["ThemeId"]);
        int newThemeId = themeMapping[oldThemeId];
        string text = reader["Text"].ToString();
        InsertQuestion(newThemeId, text);
        int newId = GetLastInsertId();
        questionMapping[Convert.ToInt32(reader["Id"])] = newId;
    }
}

string themeQuery = "SELECT * FROM Theme";
using (SQLiteCommand command = new SQLiteCommand(themeQuery, externalConnection))
using (SQLiteDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        int oldThematicId = Convert.ToInt32(reader["ThematicId"]);
        int newThematicId = thematicMapping[oldThematicId];
        string name = reader["Name"].ToString();
        InsertTheme(newThematicId, name);
        int newId = GetLastInsertId();
        themeMapping[Convert.ToInt32(reader["Id"])] = newId;
    }
}

string questionQuery = "SELECT * FROM Question";
using (SQLiteCommand command = new SQLiteCommand(questionQuery, externalConnection))
using (SQLiteDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        int oldThemeId = Convert.ToInt32(reader["ThemeId"]);
        int newThemeId = themeMapping[oldThemeId];
        string text = reader["Text"].ToString();
        InsertQuestion(newThemeId, text);
        int newId = GetLastInsertId();
        questionMapping[Convert.ToInt32(reader["Id"])] = newId;
    }
}

viceroypenguin•2y ago

yeah, you have to do the same for Theme and Question. because the principle of the connecitno closing is the same across all of them

Silme94OP•2y ago

it still return me 0

viceroypenguin•2y ago

works for me.

Gaming

Programming

✅ Database cmd.ExecuteScalar() ERROR

Did you find this page helpful?