Resolved Error when Editing and Deleting in SqLite

titojd · Sep 3, 2023

hello guys, I'm having a problem deleting and editing a Contest in SqLite, sometimes it works and sometimes it doesn't.
Catch: Enter the Try Catch exceptions
on Edit: I get the message database is locked
I have the following code;

My Form

C#:

 private void editar()
        {
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                if (txtConcurso.TextLength != 4)
                {
                    MessageBox.Show("Insira um concurso válido para salvar no Banco!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                outro
                {
                    if (ResultText[0].Text != "" && ResultText[1].Text != "" && ResultText[2].Text != "" && ResultText[3].Text != "" && ResultText[4] .Texto! = "" &&
                        ResultText[5].Text != "" && ResultText[6].Text != "" && ResultText[7].Text != "" && ResultText[8].Text != "" && ResultText[9].Text != "" &&
                        ResultText[10].Text != "" && ResultText[11].Text != "" && ResultText[12].Text != "" && ResultText[13].Text != "" && ResultText[14].Text != "")
                    {
                        string Saida = Logica.Update(PreencherInformacoes());
                        MessageBox.Show(Saida, "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    outro
                    {
                        MessageBox.Show("Insira um resultado válido para editar!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }       
            }
            outro
            {
                MessageBox.Show("Insira um Concurso!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        private string Excluir(string Concurso)
        { 
            string Saida = string.Format("O Concuso {0} foi excluído com sucesso!", Concurso);
            tentar
            {
                Lógica.Delete(txtConcurso.Text);
            }
            catch (Exceção exc)
            {
                Saida = string.Format("Ocorreu um erro ao excluir o Concurso {0}", Concurso, exc.Message); //no momento de excluir esta entrada nesta parte
            }
            retornar Saida;
        }

 private void btnEditar_Click(objeto remetente, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                editar();
            }
            outro
            {
                MessageBox.Show("Insira um concurso para editar!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

 private void btnExcluir_Click(objeto remetente, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtConcurso.Text))
            {
                if (MessageBox.Show("Você deseja mesmo excluir o cliente?", "Pergunta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    string saida = Excluir(txtConcurso.Text);
                    MessageBox.Show(saida, "Saida", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }     
            }
            outro
            {
                MessageBox.Show("Insira um valor", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

My Class

C#:

 private static SQLiteConnection sqliteConnection;

        private static SQLiteConnection DbConnection()
        {
            sqliteConnection = new SQLiteConnection("Data Source=" + path);
            sqliteConnection.Open();
            return sqliteConnection;
        }

public static string Update(Resultado result)
        {
            string Saida = "";
            SQLiteCommand cmd = new SQLiteCommand();
            using (cmd = DbConnection().CreateCommand())
            {
                cmd.CommandText = "UPDATE TBResultado SET _01=@_01,_02=@_02,_03=@_03,_04=@_04,_05=@_05,_06=@_06," +
                    " _07=@_07,_08=@_08,_09=@_09,_10=@_10,_11=@_11,_12=@_12,_13=@_13,_14=@_14,_15=@_15 WHERE Concurso=@Concurso";
                cmd.Parameters.AddWithValue("@Concurso", result.Concurso);
                cmd.Parameters.AddWithValue("@_01", result._01.ToString("D2"));
                cmd.Parameters.AddWithValue("@_02", result._02.ToString("D2"));
                cmd.Parameters.AddWithValue("@_03", result._03.ToString("D2"));
                cmd.Parameters.AddWithValue("@_04", result._04.ToString("D2"));
                cmd.Parameters.AddWithValue("@_05", result._05.ToString("D2"));
                cmd.Parameters.AddWithValue("@_06", result._06.ToString("D2"));
                cmd.Parameters.AddWithValue("@_07", result._07.ToString("D2"));
                cmd.Parameters.AddWithValue("@_08", result._08.ToString("D2"));
                cmd.Parameters.AddWithValue("@_09", result._09.ToString("D2"));
                cmd.Parameters.AddWithValue("@_10", result._10.ToString("D2"));
                cmd.Parameters.AddWithValue("@_11", result._11.ToString("D2"));
                cmd.Parameters.AddWithValue("@_12", result._12.ToString("D2"));
                cmd.Parameters.AddWithValue("@_13", result._13.ToString("D2"));
                cmd.Parameters.AddWithValue("@_14", result._14.ToString("D2"));
                cmd.Parameters.AddWithValue("@_15", result._15.ToString("D2"));
                cmd.ExecuteNonQuery();    //database is locked
            }
            try
            {
                Saida = "Concurso editado com sucesso!";
            }
            catch (Exception exc)
            {
                Saida = "Ocorreu um erro inesperado: " + exc.Message;
            }

            return Saida;
        }


        public static string Delete(string Concurso)
        {
            string Saida = "";
            SQLiteCommand cmd = new SQLiteCommand();
            using (cmd = DbConnection().CreateCommand()) ;
            {
                cmd.CommandText = "DELETE FROM TBResultado Where Concurso=@Concurso";
                cmd.Parameters.AddWithValue("@Concurso", Concurso);             
                cmd.ExecuteNonQuery();
            
            } 
                                
            return Saida;
        }

Searching for the Contest to delete My Class

C#:

public static Resultado SearchResult(int contest)
        {
            var result = new Resultado();
            using (var cmd = DbConnection().CreateCommand())
            {
                cmd.CommandText = "SELECT _01,_02,_03,_04,_05,_06,_07,_08,_09,_10,_11,_12,_13,_14,_15 from TBResultado WHERE Concurso = @concurso";

                cmd.Parameters.AddWithValue("@concurso", contest);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        result.Concurso = contest;
                        for (int i = 0; i < 15; i++)
                            result._01 = Convert.ToInt32(reader[0]);
                        result._02 = Convert.ToInt32(reader[1]);
                        result._03 = Convert.ToInt32(reader[2]);
                        result._04 = Convert.ToInt32(reader[3]);
                        result._05 = Convert.ToInt32(reader[4]);
                        result._06 = Convert.ToInt32(reader[5]);
                        result._07 = Convert.ToInt32(reader[6]);
                        result._08 = Convert.ToInt32(reader[7]);
                        result._09 = Convert.ToInt32(reader[8]);
                        result._10 = Convert.ToInt32(reader[9]);
                        result._11 = Convert.ToInt32(reader[10]);
                        result._12 = Convert.ToInt32(reader[11]);
                        result._13 = Convert.ToInt32(reader[12]);
                        result._14 = Convert.ToInt32(reader[13]);
                        result._15 = Convert.ToInt32(reader[14]);                       
                    }
                }               
            }
            //sqliteConnection.Close();
            return result;
        }

Searching for the Contest to delete in the Form

C#:

 private void SearchResult()
        {
            var contest = txtConcurso.Text;
            var result = new Resultado();
            if (!string.IsNullOrWhiteSpace(result.ToString()))
            {
                result = Logica.SearchResult(Convert.ToInt32(contest));

                ResultText[0].Text = result._01.ToString("D2");
                ResultText[1].Text = result._02.ToString("D2");
                ResultText[2].Text = result._03.ToString("D2");
                ResultText[3].Text = result._04.ToString("D2");
                ResultText[4].Text = result._05.ToString("D2");
                ResultText[5].Text = result._06.ToString("D2");
                ResultText[6].Text = result._07.ToString("D2");
                ResultText[7].Text = result._08.ToString("D2");
                ResultText[8].Text = result._09.ToString("D2");
                ResultText[9].Text = result._10.ToString("D2");
                ResultText[10].Text = result._11.ToString("D2");
                ResultText[11].Text = result._12.ToString("D2");
                ResultText[12].Text = result._13.ToString("D2");
                ResultText[13].Text = result._14.ToString("D2");
                ResultText[14].Text = result._15.ToString("D2");
            }
            else
            {
                result = null;
            }
        }

  private void btnBuscar_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(txtConcurso.Text))
            {
                if (Logica.VerificarExistencia(Convert.ToInt32(txtConcurso.Text)) == false)
                {
                    MessageBox.Show("Numero de Concurso não existe");
                    LimpaTexto();
                }
                else
                {
                    SearchResult();
                }
            }
            else
            {
                MessageBox.Show("Insira um Concurso para buscar!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
                LimpaTexto();
            }
        }

cjard · Sep 18, 2023

If OP still wants help, I say let OP repost the file in accordance with admin request..

Pablo · Sep 18, 2023

Skydiver said:
Your text file solution reads and writes everything in one shot. Change your text file solution to not use ReadAllLines() and WriteAllLines(). Switch to opening streams, AND not closing or disposing the steams. Making this change will give you an apples to apples comparison with his code where he was opening the database multiple times and not closing and disposing his connections.

A WinForms program can still be re-entrant (albeit single threaded), if the code using Application.DoEvents() or used ShowDialog() and is not careful with how events are handled.

If you have the original database code he shared, can you share it out? I can take a look as time allows. I was not jumping on it originally because I expected the file share to be persistent at least for the duration when the problem was solved. But alas, he retracted the share.

Hello, @Skydiver
I opened @titojd 's project and fix the issue about the connection being open more than once and not closed, and still got the runtime error 'database is locked'. I hope you can solve that issue, I couldn't. I don't know what is the bad thing with WriteAllLines(), which is called just once in my program which handles the text file as a string in memory.

Here is the file I got by the first link titojd shared: GeradorLotofacil.rar

Pablo

Skydiver · Sep 18, 2023

Thanks for the sharing the file. I'll download now while I can, and then take a look later.

Pablo said:
I don't know what is the bad thing with WriteAllLines()

For a small in-memory database that just gets serialized out to a text file, there is nothing wrong with it. I was just trying to point out that when you call WriteAllLines(), it opens the file, writes out the data, and then closes the file. This is different from the @titojd 's database implementation where he was opening the database... multiple times, and never closing it. Each open call effectively tells the underlying database engine to keep the file open.

Skydiver · Sep 18, 2023

Skydiver said:
I'll download now while I can, and then take a look later.

I've taken the code and move up to .NET Framework 4.8; modified the project to target 64-bit; and removed the absolute paths.

I've put the code in the following repository:

GitHub - azureskydiver/GeradorLotofacil

Contribute to azureskydiver/GeradorLotofacil development by creating an account on GitHub.

github.com

How do I reproduce the problem where the database is reporting that it is locked?

cjard · Sep 19, 2023

Perform an edit apparently

Pablo said:
I opened @titojd 's project and fix the issue about the connection being open more than once

Show us yoir fixed code?

Skydiver · Sep 19, 2023

cjard said:
Show us yoir fixed code?

I'm not sure, but I think @Pablo 's fixes are in the attachment "Logica.txt" in post #12.

Or at least I hope those are his fixes because they look something like this:

From Logica.txt:

public static string path = Directory.GetCurrentDirectory() + "\\ResultadoDB.sqlite";
private static SQLiteConnection sqliteConnection = new SQLiteConnection("Data Source=" + path);

public static string Add(Resultado number)
{
    string Saida = "";
    //SQLiteCommand cmd = new SQLiteCommand();
    using (var cmd = sqliteConnection.CreateCommand())
    {
        cmd.CommandText = "insert into TBResultado(Concurso,_01,_02,_03,_04,_05,_06,_07,_08,_09,_10,_11,_12,_13,_14,_15)" +
            " Values(@Concurso,@_01,@_02,@_03,@_04,@_05,@_06,@_07,@_08,@_09,@_10,@_11,@_12,@_13,@_14,@_15)";

        cmd.Parameters.AddWithValue("@Concurso", number.Concurso);
        :
        if (VerificarExistencia(number.Concurso) == false)
        {
            try
            {
                //conexao.Conectar();
                number.Concurso = Convert.ToInt32(cmd.ExecuteNonQuery());
                sqliteConnection.Close();

                Saida = "Resultado registrado com sucesso!";
            }
            catch (Exception exc)
            {
                Saida = "Ocorreu um erro inesperado: " + exc.Message;
            }
            return Saida;
        }
        else
        {
            //If the Result is already registered, this code will return and nothing will be saved.
            Saida = "Resultado já cadastrado.";
            return Saida;                  
        }
    }
}

which looks better than:

From Logica.cs:

public static string path = Directory.GetCurrentDirectory() + "\\ResultadoDB.sqlite";
private static SQLConnection sqliteConnection;

private static SQLiteConnection DbConnection()
{
   sqliteConnection = new SQLiteConnection("Data Source=" + path);
   sqliteConnection.Open();
   return sqliteConnection;
}

public static string Add(Resultado number)
{
    string Saida = "";
    //SQLiteCommand cmd = new SQLiteCommand();
    using (var cmd = DbConnection().CreateCommand())
    {
        cmd.CommandText = "insert into TBResultado(Concurso,_01,_02,_03,_04,_05,_06,_07,_08,_09,_10,_11,_12,_13,_14,_15)" +
            " Values(@Concurso,@_01,@_02,@_03,@_04,@_05,@_06,@_07,@_08,@_09,@_10,@_11,@_12,@_13,@_14,@_15)";

        cmd.Parameters.AddWithValue("@Concurso", number.Concurso);
        :
        if (VerificarExistencia(number.Concurso) == false)
        {
            try
            {
                //conexao.Conectar();
                number.Concurso = Convert.ToInt32(cmd.ExecuteNonQuery());
                DbConnection().Close();

                Saida = "Resultado registrado com sucesso!";
            }
            catch (Exception exc)
            {
                Saida = "Ocorreu um erro inesperado: " + exc.Message;
            }
            return Saida;
        }
        else
        {
            //If the Result is already registered, this code will return and nothing will be saved.
            Saida = "Resultado já cadastrado.";
            return Saida;                  
        }
    }
}

But in both cases there are still holes because using or try-finally was not used.

Skydiver · Sep 19, 2023

cjard said:
Perform an edit apparently

I hope there's more detailed steps on how to do that edit. I've been randomly pressing buttons because I don't really understand how to use that program and to me it's not intuitive to use.

titojd · Sep 20, 2023

Hi everyone, @Skydiver, @Pablo, @cjard, I apologize for not responding sooner, I don't know why I didn't receive the notifications in my email, I always did, but this time it didn't come, @cjard about the 58MB size of the completed project that I send Sutup together,

#Pablo I was looking for a lighter database that didn't need to be installed, something that Sqlite does,

As for the problem at hand:
Thank you for your effort in helping me, a big hug.

asking a new question on the forum

Pablo · Sep 20, 2023

Hello @Skydiver
To reproduce the error click in the button "Edit Delete Add" (the biggest one on the right) then in the form which appears type a Contest number in the first TextBox (you can see the numbers at the DataGridView first column), then click the "Search" button, and then Click the "Edit" or "Delete" buttons and the Exception will be thrown.

Skydiver · Sep 20, 2023

@Pablo : thanks for the repro steps. No home Internet right now. I'll try them out tomorrow when the phone company fixes their cable.

cjard · Sep 21, 2023

Sooo.. it was a case of "not `using` the connection?

titojd · Sep 22, 2023

Hi everyone, I'm very grateful to everyone, I learned a lot here in this post, I realized that I have to study a lot, and that's what I've been trying to do, but there are so many things that make my brain rack,
I tried every way.
I took the tip from @Pablo and it kept giving the error, the tips from @cjard and @Skydiver did nothing to solve my error, I spent nights researching and didn't solve it, so the best thing to do is change.
I'm thinking about going back to Sql Server, which never gave me any problems,
unfortunately it's a price I'll have to pay,
I thank each and every one of you who helped me this far.
A big hug to everyone,

cjard · Sep 23, 2023

titojd said:
the tip from @cjard ... did nothing to solve my error

It's no surprise, given that you didn't do what I recommended!

titojd · Sep 23, 2023

I'm going to dedicate myself to this post, I'm going to redo the project based on these tips, because my code is all messed up, as soon as I'm finished I'll come back here to inform everyone, thank you all.
a hug.

titojd · Sep 23, 2023

cjard said:
It's no surprise, given that you didn't do what I recommended!

I'm sorry, but that's not exactly what I meant, I'm using Google Translate, my English is weak.

Resolved Error when Editing and Deleting in SqLite

titojd

Well-known member

Skydiver

GitHub - azureskydiver/GeradorLotofacil at minimal-fix-for-repro-case

GeradorLotofacil/Controle/Logica.cs at minimal-fix-for-repro-case · azureskydiver/GeradorLotofacil

cjard

Well-known member

Pablo

Well-known member

Skydiver

Skydiver

GitHub - azureskydiver/GeradorLotofacil

cjard

Well-known member

Skydiver

Skydiver

titojd

Well-known member

Pablo

Well-known member

Skydiver

cjard

Well-known member

titojd

Well-known member

cjard

Well-known member

titojd

Well-known member

titojd

Well-known member

Similar threads

Share this page

Latest posts