Resolved SQLite Delete data don't work

[Kostakis45]

I'm trying to delete a record from database.

public void Delete_Info_From_DB()
        {
            Plasteka pa = new Plasteka();//Main Form

            var con = new SQLiteConnection(connection);
            con.Open();
            var cmd = new SQLiteCommand(con);
         
            try
            {
                cmd.CommandText = "DELETE FROM Description WHERE Mould_Code = '"+pa.Search_Box.Text+"'";//Am I missing something here? BTW Mould_Code is PK integer.
                cmd.ExecuteNonQuery();
            }
            catch
            {
                if ((cmd.CommandText = "SELECT * FROM Description WHERE Mould_Code = '" + pa.Search_Box.Text + "'") == null)
                {
                    MessageBox.Show("Επιτυχής διαγραφή.");

                    pa.mould_code_input.Text = null;
                    pa.machine_number_input.Text = null;
                    pa.machine_type_input.Text = null;
                    pa.supplier_input.Text = null;
                    pa.colour_input.Text = null;
                    pa.comboBox1.Text = null;
                    pa.comboBox2.Text = null;
                    pa.comboBox3.Text = null;
                    pa.numericUpDown1.Text = null;
                    pa.numericUpDown2.Text = null;
                    pa.numericUpDown3.Text = null;
                    pa.dateTimePicker1.Text = null;
                    pa.item_name_input.Text = null;
                    pa.pictureBox1.Image = null;
                    pa.pictureBox2.Image = null;
                }
                else
                {
                    MessageBox.Show("Αποτυχία διαγραφής.");
                }
            }    
            con.Close();
        }

My command line seems fine and I'm guessing that the problem lies in the reference I'm doing to get the value from textbox.But I can't figure out why it's not showing any errors,instead the program freezes.
Any idea or suggestion?

 

[Kostakis45]

You're not actually reading the words being posted. You've been told twice now that the specific issue is the single-quotes. No one is telling you that you need to pass the data a different way. They're telling you that you don't wrap numbers in single quotes, so don't. In your C# code, do you put double-quotes around numbers? Of course you don't. That's just for strings. Why, then, do you insist on wrapping numbers in single-quotes in your SQL code? Single-quotes are just for strings in SQL, not numbers.

When someone speaks about single quotes i know those (' ') and double quotes those (" ").Now that we cleared that please read my reply post #8 and you'll understand better my position.

 

[Skydiver]

After some time it's saying database is locked,but it's not related.I made a new db and it's the same.My guess is tha for some reason it's not reading the user input in this line

cmd.CommandText = "DELETE FROM Description WHERE Mould_Code = '"+ val +"'";

How else i can write this code?

It can't be that line that is hanging. That is a simple string assignment and should be nearly instantaneous.

Again, please read what we are writing: When the program freezes, break in with the debugger. Look at the call stack. What is the line of your code that is in the callstack prior to the callstack starting to show the .NET Framework or .NET Core code and the SQLite C# driver code?

Are you sure you don't have some other operation that is running, and that you've closed all other database connections prior to calling your delete method? Your report that "After some time it's saying database is locked" could be an indication that you are in a deadlock because you have another transaction that is still pending.

It may also be a sign that you have a circular dependency with your keys. You said the delete works find if you used the database explorer that comes with SQLite, so that's probably not it.

 

[Kostakis45]

It can't be that line that is hanging. That is a simple string assignment and should be nearly instantaneous.

Again, please read what we are writing: When the program freezes, break in with the debugger. Look at the call stack. What is the line of your code that is in the callstack prior to the callstack starting to show the .NET Framework or .NET Core code and the SQLite C# driver code?

Are you sure you don't have some other operation that is running, and that you've closed all other database connections prior to calling your delete method? Your report that "After some time it's saying database is locked" could be an indication that you are in a deadlock because you have another transaction that is still pending.

It may also be a sign that you have a circular dependency with your keys. You said the delete works find if you used the database explorer that comes with SQLite, so that's probably not it.

Check here.

 

[Kostakis45]

Check here.

You just said something worth to search for.I'll look for not closed conection through my code.Give me some minutes.

 

[Skydiver]

Yes I see that it is running. As I said, break in with the debugger.

 

[Kostakis45]

Yes I see that it is running. As I said, break in with the debugger.
View attachment 2140

 

[Kostakis45]

No. Your Delete_Record class should not know anything about any controls at all. It should be concerned only with data. If you want to save data then just pass in the data and save it. If you want to delete data then it should be the responsibility of the form itself to clear the controls after a successful delete. If your extra class is going to do the UI work as well then that class is pointless and you may as well do the data work in the form. If you're going to create extra classes to do other jobs then they should ONLY do those jobs. The form does the UI stuff and the data class does the data stuff. Never the twain shall meet.

Any example or reference to read?I now know these two methods.Won't hurt to learn another one.And aplly it to my code as you suggested.Thanks.

 

[Skydiver]

Well, looking at the System.Data.SQLite source code, the reason why it would try to sleep is because it thinks that the database is locked or busy:

internal override bool Step(SQLiteStatement stmt)
{
    SQLiteErrorCode n;
    Random rnd = null;
    uint starttick = (uint)Environment.TickCount;
    uint timeout = (uint)(stmt._command._commandTimeout * 1000);

    ResetCancelCount();

    while (true)
    {
        try
        {
            // do nothing.
        }
        finally /* NOTE: Thread.Abort() protection. */
        {
            n = UnsafeNativeMethods.sqlite3_step(stmt._sqlite_stmt);
        }

        if (ShouldThrowForCancel())
        {
            if ((n == SQLiteErrorCode.Ok) ||
                (n == SQLiteErrorCode.Row) ||
                (n == SQLiteErrorCode.Done))
            {
                n = SQLiteErrorCode.Interrupt;
            }

            throw new SQLiteException(n, null);
        }

        if (n == SQLiteErrorCode.Interrupt) return false;
        if (n == SQLiteErrorCode.Row) return true;
        if (n == SQLiteErrorCode.Done) return false;

        if (n != SQLiteErrorCode.Ok)
        {
            SQLiteErrorCode r;

            // An error occurred, attempt to reset the statement.  If the reset worked because the
            // schema has changed, re-try the step again.  If it errored our because the database
            // is locked, then keep retrying until the command timeout occurs.
            r = Reset(stmt);

            if (r == SQLiteErrorCode.Ok)
                throw new SQLiteException(n, GetLastError());

            else if ((r == SQLiteErrorCode.Locked || r == SQLiteErrorCode.Busy) && stmt._command != null)
            {
                // Keep trying
                if (rnd == null) // First time we've encountered the lock
                    rnd = new Random();

                // If we've exceeded the command's timeout, give up and throw an error
                if ((uint)Environment.TickCount - starttick > timeout)
                {
                    throw new SQLiteException(r, GetLastError());
                }
                else
                {
                    // Otherwise sleep for a random amount of time up to 150ms
                    System.Threading.Thread.Sleep(rnd.Next(1, 150));
                }
            }
        }
    }
}

 

[Kostakis45]

Well, looking at the System.Data.SQLite source code, the reason why it would try to sleep is because it thinks that the database is locked or busy:

internal override bool Step(SQLiteStatement stmt)
{
    SQLiteErrorCode n;
    Random rnd = null;
    uint starttick = (uint)Environment.TickCount;
    uint timeout = (uint)(stmt._command._commandTimeout * 1000);

    ResetCancelCount();

    while (true)
    {
        try
        {
            // do nothing.
        }
        finally /* NOTE: Thread.Abort() protection. */
        {
            n = UnsafeNativeMethods.sqlite3_step(stmt._sqlite_stmt);
        }

        if (ShouldThrowForCancel())
        {
            if ((n == SQLiteErrorCode.Ok) ||
                (n == SQLiteErrorCode.Row) ||
                (n == SQLiteErrorCode.Done))
            {
                n = SQLiteErrorCode.Interrupt;
            }

            throw new SQLiteException(n, null);
        }

        if (n == SQLiteErrorCode.Interrupt) return false;
        if (n == SQLiteErrorCode.Row) return true;
        if (n == SQLiteErrorCode.Done) return false;

        if (n != SQLiteErrorCode.Ok)
        {
            SQLiteErrorCode r;

            // An error occurred, attempt to reset the statement.  If the reset worked because the
            // schema has changed, re-try the step again.  If it errored our because the database
            // is locked, then keep retrying until the command timeout occurs.
            r = Reset(stmt);

            if (r == SQLiteErrorCode.Ok)
                throw new SQLiteException(n, GetLastError());

            else if ((r == SQLiteErrorCode.Locked || r == SQLiteErrorCode.Busy) && stmt._command != null)
            {
                // Keep trying
                if (rnd == null) // First time we've encountered the lock
                    rnd = new Random();

                // If we've exceeded the command's timeout, give up and throw an error
                if ((uint)Environment.TickCount - starttick > timeout)
                {
                    throw new SQLiteException(r, GetLastError());
                }
                else
                {
                    // Otherwise sleep for a random amount of time up to 150ms
                    System.Threading.Thread.Sleep(rnd.Next(1, 150));
                }
            }
        }
    }
}

ok.its logical.like i mentioned before the locked error occurs when the program is in freeze mode and after some time passed while the debugging is still running.The locked error occurs in the cmd.ExecuteNonQuery line.I'm at a loss.

 

[Skydiver]

Make sure that you don't have any other open connections on the same database.

Make sure that the file is not read only.

Make sure that you don't have any antivirus program that is currently scanning the database file you are currently working on it.

 

[Skydiver]

Also, I don't recall whether you are using .NET Framework, or .NET Core. If your are using .NET Core, .NET 6 (or was it .NET 5?) added Microsoft.Data.Sqlite as a new Nuget package that contains an SQLite driver that you can use instead of System.Data.Sqlite data driver. I suspect that you'll get the same results anyway, but I don't know if the MS provided driver will give you more diagnostics as to what is causing the lock.

 

[Kostakis45]

Also, I don't recall whether you are using .NET Framework, or .NET Core. If your are using .NET Core, .NET 6 (or was it .NET 5?) added Microsoft.Data.Sqlite as a new Nuget package that contains an SQLite driver that you can use instead of System.Data.Sqlite data driver. I suspect that you'll get the same results anyway, but I don't know if the MS provided driver will give you more diagnostics as to what is causing the lock.

If i recall correctly i think for this project i used .Net Framework 4.8 and i downloaded this Nugget package.
Also to your previous suggestions:
I have only windows firewall and the database file is not only read.

 

[Skydiver]

Is the file currently open in SQLite database explorer?

 

[Kostakis45]

Is the file currently open in SQLite database explorer?

no but i can open it in dbBroswer

 

[Skydiver]

At this point, the only thing I can think of is you have an open connection from another part of your code that is locking the file.

I suggest creating a minimal program to reproduce the problem, and then post that code. That could should simply call your class method for deletion. Ideally your class should simply take the mould code number, but if you insist on putting that value into a text box, and then passing that text box to your class, then so be it.