C#•2mo ago

Issue with SQlite command

List<(string IDClash, int IDDiscord)> joueursIDs = new List<(string, int)>();

using (SqliteConnection connection = new SqliteConnection(connectionString))
{
    connection.Open();

    // Requête pour récupérer uniquement les IDClash et IDDiscord
    string query = "SELECT IDClash, IDDiscord FROM Joueur";

    using (SqliteCommand command = new SqliteCommand(query, connection))
    using (SqliteDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Récupérer IDClash et IDDiscord et les ajouter à la liste
            string idClash = reader.GetString(0);
            int idDiscord = reader.GetInt32(1);
            joueursIDs.Add((idClash, idDiscord));
        }
    }
}

List<(string IDClash, int IDDiscord)> joueursIDs = new List<(string, int)>();

using (SqliteConnection connection = new SqliteConnection(connectionString))
{
    connection.Open();

    // Requête pour récupérer uniquement les IDClash et IDDiscord
    string query = "SELECT IDClash, IDDiscord FROM Joueur";

    using (SqliteCommand command = new SqliteCommand(query, connection))
    using (SqliteDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Récupérer IDClash et IDDiscord et les ajouter à la liste
            string idClash = reader.GetString(0);
            int idDiscord = reader.GetInt32(1);
            joueursIDs.Add((idClash, idDiscord));
        }
    }
}

20 Replies

Kurisu 🐧OP•2mo ago

When I try to execute that Query I've this error. The table "Joueur" doesn't exist but when I do a .schema in my DB file, the table exist What's the issue? The connection string point to the good file

✿ Scarlet ✿•2mo ago

What is the casing of Joeur?

Kurisu 🐧OP•2mo ago

Player in french

✿ Scarlet ✿•2mo ago

Also the schema Casing

Kurisu 🐧OP•2mo ago

.schema is an internal command in SQlite To see the currents tables

✿ Scarlet ✿•2mo ago

Casing like, is it "Joeur", "JOEUR", "joeur"

Kurisu 🐧OP•2mo ago

Nah I verified that

✿ Scarlet ✿•2mo ago

What is the schema where the table is located? Have you tried [schema].Joeur?

SG97•2mo ago

what's the actual connection string

Kurisu 🐧OP•2mo ago

@"Data Source=C:\Users\Utilisateur\source\repos\DiscordBotIEN\IENDB.sqlite, Version=3"

Jimmacle•2mo ago

if it's a relative path there's a very good chance you're opening a different file than you think you are

Kurisu 🐧OP•2mo ago

That's the schema

SG97•2mo ago

in-memory db? hmm, nvm

Kurisu 🐧OP•2mo ago

That's a sqlite file located in my project When I execute my query in Sqlite itself, it works

Buddy•2mo ago

Note that your database must exist in bin/debug or bin/release depending on your config AKA the current working directory

Jimmacle•2mo ago

not with an absolute path

Buddy•2mo ago

Oh it was absolute, my bad

Jimmacle•2mo ago

i have seen things like one tool creating the database with a WAL file and the C# code not reading it leading to wrong data is there just the .sqlite file or are there other db-related files there too?

Buddy•2mo ago

I mean you can try with relative path and just copy the db to output option in properties of the db file

Kurisu 🐧OP•2mo ago

just that DB Same error with a relative path

CREATE TABLE Joueur (
    IDDiscord INTEGER,
    IDClash TEXT PRIMARY KEY, -- Clé primaire
    Clan TEXT,
    Role TEXT,
    MembreDepuis DATE,
    AQuitte DATE
);
CREATE TABLE Sanction (
    IDSanction INTEGER PRIMARY KEY,    -- Clé primaire
    Type TEXT NOT NULL,
    DateSanction DATE NOT NULL,
    IDClash TEXT,                 -- Clé étrangère qui référence la table Joueur
    FOREIGN KEY (IDClash) REFERENCES Joueur(IDClash) ON DELETE CASCADE
);

CREATE TABLE Joueur (
    IDDiscord INTEGER,
    IDClash TEXT PRIMARY KEY, -- Clé primaire
    Clan TEXT,
    Role TEXT,
    MembreDepuis DATE,
    AQuitte DATE
);
CREATE TABLE Sanction (
    IDSanction INTEGER PRIMARY KEY,    -- Clé primaire
    Type TEXT NOT NULL,
    DateSanction DATE NOT NULL,
    IDClash TEXT,                 -- Clé étrangère qui référence la table Joueur
    FOREIGN KEY (IDClash) REFERENCES Joueur(IDClash) ON DELETE CASCADE
);

List<(string IDClash, int IDDiscord)> joueursIDs = new List<(string, int)>();

using (SqliteConnection connection = new SqliteConnection(connectionString))
{
    connection.Open();

    // Requête pour récupérer uniquement les IDClash et IDDiscord
    string query = "SELECT IDClash, IDDiscord FROM Joueur";

    using (SqliteCommand command = new SqliteCommand(query, connection))
    using (SqliteDataReader reader = command.ExecuteReader()) //the exception appear here

    {
        while (reader.Read())
        {
            // Récupérer IDClash et IDDiscord et les ajouter à la liste
            string idClash = reader.GetString(0);
            int idDiscord = reader.GetInt32(1);
            joueursIDs.Add((idClash, idDiscord));
        }
    }
}

// Affichage des IDClash et IDDiscord
foreach (var joueurID in joueursIDs)
{
    Console.WriteLine($"IDClash: {joueurID.IDClash}, IDDiscord: {joueurID.IDDiscord}");
}

List<(string IDClash, int IDDiscord)> joueursIDs = new List<(string, int)>();

using (SqliteConnection connection = new SqliteConnection(connectionString))
{
    connection.Open();

    // Requête pour récupérer uniquement les IDClash et IDDiscord
    string query = "SELECT IDClash, IDDiscord FROM Joueur";

    using (SqliteCommand command = new SqliteCommand(query, connection))
    using (SqliteDataReader reader = command.ExecuteReader()) //the exception appear here

    {
        while (reader.Read())
        {
            // Récupérer IDClash et IDDiscord et les ajouter à la liste
            string idClash = reader.GetString(0);
            int idDiscord = reader.GetInt32(1);
            joueursIDs.Add((idClash, idDiscord));
        }
    }
}

// Affichage des IDClash et IDDiscord
foreach (var joueurID in joueursIDs)
{
    Console.WriteLine($"IDClash: {joueurID.IDClash}, IDDiscord: {joueurID.IDDiscord}");
}

private static readonly string connectionString = @"Data Source=C:\Users\Utilisateur\source\repos\DiscordBotIEN\IENDB.sqlite";

private static readonly string connectionString = @"Data Source=C:\Users\Utilisateur\source\repos\DiscordBotIEN\IENDB.sqlite";

The exception throw when we reach the using with the reader

Gaming

Programming

Issue with SQlite command