Can not delete row from SQLite database

Question

I've got a little problem when trying to delete a row from a sqlite database.

I set the database up like this:

CREATE TABLE IF NOT EXISTS patient ( patient_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, vorname VARCHAR(100) NOT NULL,nachname VARCHAR(100) NOT NULL,adresse VARCHAR(100) NOT NULL,ort VARCHAR(100) NOT NULL,plz INTEGER NOT NULL,geburtstag DATE NOT NULL);

There is definitely data in there and I tried to delete a row like this:

public void deletePatientById(long id)
        {
            string deleteQuery = "DELETE FROM patient WHERE patient_id="+id.ToString()+";";
            SQLiteCommand command = new SQLiteCommand(deleteQuery, connection);
            command.ExecuteNonQuery();
            command.Dispose();
            MessageBox.Show("Patient: " + id.ToString() + " gelöscht");
        }

The problem is, VS tells me, there is no such Column named patient_id. But actually there is as far as i can see.

Anyone an idea what might be wrong here?

Can you post the names of the columns from the patient table? — Tanner_Gram
– Tanner_Gram, Commented Jun 3, 2014 at 16:23
The names you can see in the create statement, the first one actually is patient_id — Igl3
– Igl3, Commented Jun 3, 2014 at 16:24

Tsukasa · Accepted Answer · 2014-06-03 16:59:31Z

Are you pointing to the right file and does that file have the table?

Create File and Table

string file = @"C:\TestDB.sqlite";
conn = new SQLiteConnection(String.Format("Data Source={0};Version=3;", file));

if (!File.Exists(file))
{
    SQLiteConnection.CreateFile(file);
}

string createTable = "CREATE TABLE IF NOT EXISTS patient ( patient_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, vorname VARCHAR(100) NOT NULL,nachname VARCHAR(100) NOT NULL,adresse VARCHAR(100) NOT NULL,ort VARCHAR(100) NOT NULL,plz INTEGER NOT NULL,geburtstag DATE NOT NULL);";

conn.Open();

using (SQLiteCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = createTable;
    cmd.ExecuteNonQuery();
}

conn.Close();

Delete

public void deletePatientById(long id)
{
    conn.Open();
    using (SQLiteCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = String.Format("DELETE FROM patient WHERE patient_id={0}", id);
        cmd.ExecuteNonQuery();
    }
    conn.Close();
    MessageBox.Show("Patient: " + id.ToString() + " gelöscht");
}

Igl3 · Accepted Answer · 2014-06-03 18:54:00Z

0

I found the answer to my problem by myself now.

It seemed to be related to the underscore. I changed patient_id to id only and now everything works :) Thanks for your efforts though.

answered Jun 3, 2014 at 18:54

Igl3

5,1085 gold badges39 silver badges69 bronze badges

1 Comment

Tsukasa Over a year ago

That's odd I had no issue using what you provided with my example provided. Glad you have it working.

Collectives™ on Stack Overflow

Can not delete row from SQLite database

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related