C#•6mo ago

✅ Database cmd.ExecuteScalar() ERROR

Im trying to import a database to another

13 Replies

Silme94OP•6mo ago

message.txt

Silme94OP•6mo ago

this part

viceroypenguin•6mo ago

Can you share a full code repository?

Silme94OP•6mo ago

https://github.com/Silme94/project

GitHub

GitHub - Silme94/project

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

viceroypenguin•6mo 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•6mo ago

alr

viceroypenguin•6mo 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•6mo 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•6mo 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•6mo ago

it still return me 0

viceroypenguin•6mo ago

works for me.

Gaming

Programming

✅ Database cmd.ExecuteScalar() ERROR