C#•7mo ago

✅ Database cmd.ExecuteScalar() ERROR

Im trying to import a database to another

13 Replies

Silme94OP•7mo ago

message.txt

Silme94OP•7mo ago

this part

viceroypenguin•7mo ago

Can you share a full code repository?

Silme94OP•7mo ago

https://github.com/Silme94/project

GitHub

GitHub - Silme94/project

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

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

alr

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

it still return me 0

viceroypenguin•7mo ago

works for me.

Gaming

Programming

✅ Database cmd.ExecuteScalar() ERROR