CODEWITHSUNDEEP
c#databasesqlite
Charley R.
Charley R.

Reputation: 207

C# claims my SQLite table doesn't exist but it does

I'm trying to read a table I created with DB Browser for SQLite, but there's a runtime error claiming the database doesn't have the table. But it does! I created it and I can see it in DB Browser.

Here's the code:

    private void PopulateGridCustomers()
    {
        String conString = Properties.Resources.database;
        var con = new SqliteConnection(conString);
        try
        {
            con.Open();
            Console.WriteLine(con.State);
            SqliteCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM Clientes";
            using (SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd.CommandText, conString))
            {
                DataTable dt = new DataTable();
                dataAdapter.Fill(dt);
                gridCustomers.DataSource = dt;
            }
        }

And a picture showing the table exists indeed:

enter image description here

What could be causing this issue and how to fix it?

Thanks

P.S.: Please cut me some slack, I'm not a professional developer. I code for myself.

Update: Here's the actual error message:

System.Data.SqlServerCe.SqlCeException: 'The specified table does not exist. 
[ Clientes ]'

UPDATE 2:

Here's the path of the database:

Data Source=C:\Users\charl\OneDrive\Documentos\SBM\Database\SBMTeste.db;

As you can see, it is the very same that shows in DB Browser window. The error I first described occurs if I omit the ".db". If I put the file extension in the string, the following error is thrown:

System.Data.SqlServerCe.SqlCeException: 'The database file may be corrupted. 
Run the repair utility to check the database file. 
[ Database name = C:\Users\charl\OneDrive\Documentos\SBM\Database\SBMTeste.db ]'

UPDATE 3:

Here's the CREATE statement of the table:

CREATE TABLE "Clientes" (
    "id"    INTEGER NOT NULL UNIQUE,
    "nome"  TEXT NOT NULL,
    "telefone1" TEXT,
    "telefone2" TEXT,
    "email" TEXT,
    "endereço"  TEXT,
    "bairro"    TEXT,
    "cidade"    TEXT,
    "data_nascimento"   TEXT,
    "data_cadastro" TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY("id" AUTOINCREMENT),
    UNIQUE("nome","data_nascimento")
)

Upvotes: 2

Views: 863

Answers (2)

Thierry Brémard
Thierry Brémard

Reputation: 899

SQL Server CE is not SQLite.

This is an extract of my code using SQLite:

public Equity SearchEquityByEpic(string epic)
{
    string query = string.Format("SELECT * from " + Tables.ASSETS_TABLE + " WHERE epic='{0}'", epic);
    Log(query);

    var command = _connection.CreateCommand(query);
    var equities = command.ExecuteQuery<Equity>();

    if (equities.Count == 0)
    {
        return null;
    }

    Equity ret = equities.First();

    return ret;
}

Upvotes: 1

Charley R.
Charley R.

Reputation: 207

The thing is I messed up when trying to install the packages needed for my application to work with the SQlite database. I endend up installing a lot of unnecessary things and missing using System.Data.SQLite. That way my application was not being able to recognize the .db file hence the "corrupted file" message described in my 3rd update.

Upvotes: 1

Related Questions