SQLite Error: no such table

Question

I use System.Data.SQLite to work with my database. Here is how I've created tables:

CREATE TABLE spare_samples (
    sampleId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    sampleNr INTEGER UNSIGNED UNIQUE NOT NULL,
    sampleName VARCHAR(255) UNIQUE NOT NULL COLLATE NOCASE,
    spareState VARCHAR(255) NULL,
    sides VARCHAR(255) NULL,
    notes TEXT,
    dispatch VARCHAR(32) NULL,
    ebayId VARCHAR(255) NULL
);

CREATE TABLE spare_sample_photo_examples (
    exampleId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    exampleType VARCHAR(32) NOT NULL,
    exampleImage VARCHAR(128) UNIQUE NOT NULL COLLATE NOCASE,
    exampleTitle VARCHAR(128) NULL,
    exampleDescr TEXT,
    exampleOrder INTEGER NOT NULL DEFAULT 0,
    sampleId INTEGER NOT NULL,
    FOREIGN KEY (sampleId)
        REFERENCES spare_samples(sampleId)
        ON UPDATE CASCADE ON DELETE CASCADE
);

To add new row I'm using this:

SQLiteConnection conn = new SQLiteConnection(LoadForm.connString);
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "Insert Into spare_sample_photo_examples (exampleType, exampleImage, exampleTitle, exampleDescr, exampleOrder, sampleId) values('"
                + spareSamplePhotoExampleToUpdate.exampleType + "', '"
                + spareSamplePhotoExampleToUpdate.exampleImage + "', '"
                + spareSamplePhotoExampleToUpdate.exampleTitle + "', '"
                + spareSamplePhotoExampleToUpdate.exampleDescr + "', "
                + spareSamplePhotoExampleToUpdate.exampleOrder + ", "
                + spareSamplePhotoExampleToUpdate.sampleId + "); Select last_insert_rowid();";
Clipboard.SetText(command.CommandText);
try
{
    conn.Open();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    return;
}

try
{
    spareSamplePhotoExampleToUpdate.exampleId = Convert.ToInt16(command.ExecuteScalar());
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    return;
}
finally
{
    if (conn.State.ToString() != "Closed")
    {
        conn.Close();
    }
}

And spareSamplePhotoExampleToUpdate is the instance of object:

public class SpareSamplePhotoExample
{
    public int exampleId { get; set; }
    public string exampleType { get; set; }
    public string exampleImage { get; set; }
    public string exampleTitle { get; set; }
    public string exampleDescr { get; set; }
    public int exampleOrder { get; set; }
    public int sampleId { get; set; }
}

I don't actually know what's wrong with this code, with me and anything else, but when I got generated code from app and ran it in the FF SQLite Manager it was executed successfully. Also I have read another similar questions but all of them concern android and ios.

Insert Into spare_sample_photo_examples (exampleType, exampleImage, exampleTitle, exampleDescr, exampleOrder, sampleId) values('image', 'images\c438ldpuojpywln1.jpg', '', '', '0', '32'); Select last_insert_rowid();

Thanks a lot for any help.

Comments:

This code checks database at start:

SQLiteCommand command = conn.CreateCommand();
command.CommandText = "SELECT count( name ) FROM sqlite_master WHERE (type = 'table' AND name = 'cars')"
                + " OR (type = 'table' AND name = 'makes')"
                + " OR (type = 'table' AND name = 'models')"
                + " OR (type = 'table' AND name = 'spares')"
                + " OR (type = 'table' AND name = 'spare_brands')"
                + " OR (type = 'table' AND name = 'spare_samples')"
                + " OR (type = 'table' AND name = 'export_templates')"
                + " OR (type = 'table' AND name = 'users')"
                + " OR (type = 'table' AND name = 'user_meta')"
                + " OR (type = 'table' AND name = 'spare_sample_photo_examples')";
            if (Convert.ToInt16(command.ExecuteScalar().ToString()) != 10)
            {
                //something like exit with some stuff
            }

And definitive update for my problem. As you can see I have another tables in database, so before I start adding photo examples, all functions works properly. After I get error, I can't do anything, which involves database operations. All operations return the same error, but for their tables.

I suggest you look into parametrized queries: http://stackoverflow.com/questions/4141599/parameterized-queries-in-sqlite. Ever heard of SQL injection? — iamkrillin, Dec 18 '12 at 15:12
Have you confirmed that the DB location in the connection string and the DB you're checking in SQLite Manager are the same? — RandomEngy, Dec 18 '12 at 15:25
iamkrillin, I think there's no need to hack sqlite database while you can use pretty amount of sqlite managers. PinnyM, I created all tables with FF sqlite manager. Anything wrong? RandomEngy, moreover I have a function, which checks if all tables are present in database based on master_table. If any table are missing, app will not start. — Pavel K, Dec 18 '12 at 16:42
I know this isn't code review, but instead of `if (conn.State.ToString() != "Closed")`, do it like this `if (conn.State != ConnectionState.Closed)`. Also, you should at the very least look at parameterized queries or to save yourself a whole lot of repeated ADO code, use an ORM, little ones like MicroLite, PetaPoco, Dapper etc will save you a lot of time! — Trevor Pilley, Dec 18 '12 at 18:21
Thanks for ConnectionState notice, Trevor. I have tons of unreviewed code yet. Of course I will use Dapper in near future. Anyway I want to know why I got the error. — Pavel K, Dec 18 '12 at 19:44

score 1 · Accepted Answer · answered Dec 18 '12 at 22:58

I've got the answer! I have connection options form where I can select the type of database to connect. And one of the parameters is a path. I saved it next way:

fDialog.FileName.Replace(Path.GetDirectoryName(Application.ExecutablePath) + "\\", "");

So path looked more clean when database file was inside app folder. And there is file dialog in the form, where I can create photo examples. Next story you already know. App began searching of database file in folder, where I opened pictures. Be careful with relative path.

score 0 · Answer 2 · answered Dec 18 '12 at 18:10

0

Does your LoadForm.ConnString use the correct account? If you login one way in sqlite manager and create the tables in one schema, but your app logs in with a different account/schema, that table will not be found by the application in the current schema.

answered Dec 18 '12 at 18:10

cdkMoose

1,646
19
21

Look at my comment at the very bottom of my question. My app checks if all tables present in schema preventing work with broken db files etc. – Pavel K Dec 18 '12 at 18:59

SQLite Error: no such table

2 Answers2