C# SQLite database locked

Question

I've tried all the solutions I could find to this problem, but for some reason I still get an exception stating the database I'm using is locked. My code is as follows:

string connectionString = "Data Source=D:\\CCIW\\LCM\\Organisational Database\\OrganisationalDB;" +
                        "MultipleActiveResultSets=True";
using (SQLiteConnection OriginatorDBConnection = new SQLiteConnection(connectionString))
{
    string originatorName, originatorOrganisation, originatorAddress, originatorCellNumber, originatorTelNumber, originatorEmail;

    originatorName = originatorNameTextBox.Text;
    originatorOrganisation = originatorOrganisationTextBox.Text;
    originatorAddress = originatorAddressRichTextBox.Text;
    originatorCellNumber = originatorCellTextBox.Text;
    originatorTelNumber = originatorTelTextBox.Text;
    originatorEmail = originatorEmailTextBox.Text;

    OriginatorDBConnection.Open();
    string originatorINSERT = "INSERT INTO Originator (Name, Organisation, Address, CellphoneNumber, TelephoneNumber, Email) VALUES ('" + originatorName + "', '" + originatorOrganisation + "', '" + originatorAddress + "', '" + originatorCellNumber + "', '" + originatorTelNumber + "', '" + originatorEmail + "');";

    using (SQLiteCommand originatorCommand = new SQLiteCommand(originatorINSERT, OriginatorDBConnection))
    {
        originatorCommand.ExecuteNonQuery();                    
    }
    OriginatorDBConnection.Close();
}

The closest solution on here that I could find to the problem was here: SQLite Database Locked exception

It didn't seem to work on my problem, however. What am I doing wrong?

I have an additional function wherein I use the connection:

 public AdminForm()
 {
        //Initialise AdminForm components.
        InitializeComponent();

        //Establish and open connection to ObservationDB.
        string connectionString = "Data Source=D:\\CCIW\\LCM\\Organisational Database\\OrganisationalDB;" +
                                  "MultipleActiveResultSets=True";
        ObservationDBConnection = new SQLiteConnection(connectionString);
        ObservationDBConnection.Open();

        //Query database to find all originators
        string originatorSELECT = "SELECT * FROM Originator;";
        string ECPNumberSELECT = "SELECT * FROM ECP";

        SQLiteCommand command = new SQLiteCommand(originatorSELECT, ObservationDBConnection);
        SQLiteDataReader reader = command.ExecuteReader();

        SQLiteCommand command2 = new SQLiteCommand(ECPNumberSELECT, ObservationDBConnection);
        SQLiteDataReader reader2 = command2.ExecuteReader();

        //Populate OriginatorName combobox with names of existing originators.
        List<string> originatorNames = new List<string>();
        while (reader.Read())
        {
            originatorNames.Add(Convert.ToString(reader["Name"]));
        }

        OriginatorNameComboBox.DataSource = originatorNames;

        //Populate ECP combobox with numbers of existing ECP.
        List<string> ECPNumbers = new List<string>();
        while (reader2.Read())
        {
            ECPNumbers.Add(Convert.ToString(reader2["Number"]));
        }

        ECPNumComboBox.DataSource = ECPNumbers;

        //Populate TC Decision combobox with options.
        List<string> TCDecision = new List<string>();
        TCDecision.Add("Rework");
        TCDecision.Add("Reject");
        TCDecision.Add("Approve");

        TCDecisionComboBox.DataSource = TCDecision;

        ObservationDBConnection.Close();
    }

And here:

private void SaveButton_Click(object sender, EventArgs e)
    {
        ObservationDBConnection.Open();

        ...

        string ImpactTypeINSERT = "INSERT INTO ImpactType (ImpactType, Description) VALUES ('" + impactType + "', '" + impactDescription + "');";
        SQLiteCommand ImpactTypeCommand = new SQLiteCommand(ImpactTypeINSERT, ObservationDBConnection);
        //SQLiteDataReader ImpactTypeReader = ImpactTypeCommand.ExecuteReader();
        ImpactTypeCommand.ExecuteNonQuery();

        ...

        string TCDecisionINSERT = "INSERT INTO TCDecision (Decision, Description) VALUES ('" + TechnicalCommitteeDecision + "', '" + TechnicalCommitteeDescription + "');";
        SQLiteCommand TCDecisionCommand = new SQLiteCommand(TCDecisionINSERT, ObservationDBConnection);
        SQLiteDataReader TCDecisionReader = ImpactTypeCommand.ExecuteReader();
        TCDecisionCommand.ExecuteNonQuery();

        ...

        string OperationalDecisionINSERT = "INSERT INTO OperationalDecision (Decision, Description) VALUES ('" + operationalDecision + "', '" + operationalDescription + "');";
        SQLiteCommand OperationalDecisionCommand = new SQLiteCommand(OperationalDecisionINSERT, ObservationDBConnection);
        //SQLiteDataReader OperationalDecisionReader = OperationalDecisionCommand.ExecuteReader();
        OperationalDecisionCommand.ExecuteNonQuery();

        ...

        ...

            string OriginatorIDSELECT = "SELECT * FROM Originator WHERE Name='" + OriginatorNameComboBox.Text + "';";
            SQLiteCommand OriginatorIDCommand = new SQLiteCommand(OriginatorIDSELECT, ObservationDBConnection);
            SQLiteDataReader OriginatorIDReader = OriginatorIDCommand.ExecuteReader();
            originatorIDOBS = OriginatorIDReader.GetOrdinal("ID");
            //ImpactType
            string impactTypeSELECT = "SELECT * FROM ImpactType WHERE ImpactType='" + impactType + "';";
            SQLiteCommand impactTypeOBSCommand = new SQLiteCommand(impactTypeSELECT, ObservationDBConnection);
            SQLiteDataReader impactTypeOBSReader = impactTypeOBSCommand.ExecuteReader();
            impactTypeOBS = impactTypeOBSReader.GetOrdinal("ID");            

            string operationalDecisionOBSSELECT = "SELECT * FROM OperationalDecision WHERE Decision='" + operationalDecision + "';";
            SQLiteCommand operationalDecisionOBSCommand = new SQLiteCommand(operationalDecisionOBSSELECT, ObservationDBConnection);
            SQLiteDataReader operationalDecisionOBSReader = operationalDecisionOBSCommand.ExecuteReader();
            operationalDecisionOBS = operationalDecisionOBSReader.GetOrdinal("ID");

           ...

            string ECPOBSSELECT = "SELECT * FROM ECP WHERE Number='" + ECPNumComboBox.Text + "';";
            SQLiteCommand ECPCommand = new SQLiteCommand(ECPOBSSELECT, ObservationDBConnection);
            SQLiteDataReader ECPReader = ECPCommand.ExecuteReader();
            ECPOBS = ECPReader.GetOrdinal("ID");


            string CNISObservationINSERT = "INSERT INTO CNISObservation (Title, ReceiveDate, TableDate, OriginatorID, OriginatorReference, OriginatorDate, ObservationNumber, RevisionNumber, Description, Status, ImpactDescription, ImpactType, OperationalRequirementDescription, OperationalImpact, OperationalDecision, ProposedAction, TCDecision, ECP, SolutionOperationalImpact, TechnicalSolutionImpact) VALUES ('" + 
                                                                        titleOBS + "','" 
                                                                        + receiveDateOBS + "','" 
                                                                        + tableDateOBS + "','"
                                                                        + originatorIDOBS + ",'"
                                                                        + originatorReferenceOBS +"','"
                                                                        + originatorDateOBS + "','"
                                                                        + observationNumberOBS + "',"
                                                                        + revisionNumberOBS + ",'"
                                                                        + descriptionOBS + "',"
                                                                        + statusOBS + ",'"
                                                                        + impactDescriptionOBS + "',"
                                                                        + impactTypeOBS + ",'"
                                                                        + operationalRequirementDescriptionOBS + "','"
                                                                        + operationalImpactOBS + "',"
                                                                        + operationalDecisionOBS + ",'"
                                                                        + TCDecisionOBS + ","
                                                                        + ECPOBS + ",'"
                                                                        + solutionOperationalImpactOBS + "','"
                                                                        + technicalSolutionImpactOBS + "');"; 

        ...

        string obsOBSSELECT = "SELECT * FROM CNISObservation ORDER BY ID DESC LIMIT 1;";
        SQLiteCommand CNISObservationIDCommand = new SQLiteCommand(obsOBSSELECT, ObservationDBConnection);
        SQLiteDataReader CNISObservationIDReader = CNISObservationIDCommand.ExecuteReader();
        observationID = CNISObservationIDReader.GetOrdinal("ID");

        ...

        foreach (var capabilityID in capabilitiesSelected)
        {
            string ObservationOperationalCapabilitiesINSERT = "INSERT INTO ObservationOperationalCapabilities (CapabilityID, ObservationID) VALUES (" + capabilityID + "," + observationID + ");";
            SQLiteCommand ObservationOperationalCapabilitiesCommand = new SQLiteCommand(ObservationOperationalCapabilitiesINSERT, ObservationDBConnection);
            // SQLiteDataReader ObservationOperationalCapabilitiesReader = ObservationOperationalCapabilitiesCommand.ExecuteReader();
            ObservationOperationalCapabilitiesCommand.ExecuteNonQuery();

        }

        ...

        string CNISObservationIDSELECT = "SELECT * FROM CNISObservation ORDER BY ID DESC LIMIT 1;";
        SQLiteCommand CNISObservationCommand = new SQLiteCommand(CNISObservationIDSELECT, ObservationDBConnection);
        SQLiteDataReader CNISObservationReader = CNISObservationCommand.ExecuteReader();
        CNISObservationID = CNISObservationReader.GetOrdinal("ID");

        string CNISReleaseINSERT = "INSERT INTO CNISSection VALUES (" + CNISObservationID + "," + CNISRelease + "," + chapter + ",'" + paragraph + "','" + section + "','" + page +"');";
        SQLiteCommand CNISReleaseCommand = new SQLiteCommand(CNISReleaseINSERT, ObservationDBConnection);
        //SQLiteDataReader CNISReleaseReader = CNISReleaseCommand.ExecuteReader();
        CNISReleaseCommand.ExecuteNonQuery();

        ObservationDBConnection.Close();
    }

Are you sure no where else in your code you have it open, or that there isnt another process using it — BugFinder, May 11 '18 at 07:57
I have a separate function where I use it (the code is obscenely long though). But I made sure to close the connection after it executed. These are two disjoint functions so one can be called before the other - not sure if that makes a difference. Should I post what I can of the other code as well for context? — Zenadia Groenewald, May 11 '18 at 08:03
The problem is that some *other* connection or program still has an active transaction, so what *this* code does is not really relevant. — CL., May 11 '18 at 08:08
The other code maybe relevant but as me and @CL have said its more likely its other code. Although if for some reason your code here causes exception you may find the close is not being executed.. — BugFinder, May 11 '18 at 08:10
Please parameterize your SQL - this is absolutely ripe for SQL Injection as you are taking the input from the user - imagine if they typed in ';DROP TABLE Originator - this could allow the user to delete your table — Steven Wood, May 11 '18 at 08:11
2 questions: are you the only one accessing the file? and by any chance, you don't have a tool, like SQLiteDatabaseBrowser opened at the same time, with some unsaved changes? — Antoine, May 11 '18 at 08:12
Only one accessing this file, yes. Also no SQliteDatabaseBrowser open — Zenadia Groenewald, May 11 '18 at 08:18
https://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database?rq=1 — TarHalda, Aug 09 '22 at 16:26

score 0 · Answer 1 · answered Apr 03 '19 at 15:00

0

I know I'm a lot late to the game, but it looks like you're not closing you reader variables in AdminForm() constructor. Consider wrapping the DataReaders in a using().

answered Apr 03 '19 at 15:00

jacDeveloper

47
1
1
7

score 0 · Answer 2 · edited May 05 '19 at 12:07

0

Wrapping around Command and Reader did work for me:

using (SqliteCommand cmd = new SqliteCommand(sQuery, m_Conn))
{
    using (SqliteDataReader reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            ret_type = reader.GetInt32(0);
        }
    }
}

edited May 05 '19 at 12:07

oguz ismail

1
16
47
69

answered May 05 '19 at 12:05

Michele

1
1

score 0 · Answer 3 · answered Aug 06 '21 at 07:39

Please be aware to click on Write changes on SQLite browser if it is running and there are any unsaved changes!
In my case it was very stupid of me, I was making changes in SQLite browser and did not click on write changes, which locked the DB to be modified by the services. After I clicked the Write changes button, all the post request worked as expected.

According to @Rohan Shenoy in this topic: SQLite Database Locked exception

C# SQLite database locked

3 Answers3