Resolved Error when Editing and Deleting in SqLite

[titojd]

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

 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

 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

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

 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();
            }
        }

 

[titojd]

Hello @Skydiver, @Pablo, @cjard, first I apologize for so much inconvenience.
Following my colleagues' tips, my code continued to give the same error.
So I started commenting on some codes, I came to the conclusion that the error is in this part of the code.

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

Does anyone have any ideas on how to perform a correct validation in this case?
so when you enter a contest that doesn't exist, it displays an error message.
Or would you prefer me to create a new question?
I am very grateful that you helped me.
A hug to everyone

 

[Skydiver]

I only fixed the specific repro case described by @Pablo , and I was trying to minimize the changes in your code to show how to fix the issue with a specific focus on that code path. I did not do a holistic refactoring of all code paths because that would have obscured what the problem was and how to fix it.

What is the specific steps you used to reproduce the problem?

Was this the only code that you changed or did you change something else? (For example, that btnBuscar_Click() not only commented out a whole section of code, but you also change the sense of line 3 and swapped the if[/code] and [icode]else blocks.

What changes did you make to "Logica.cs"? Did you you update all the uses of DbConnection() to dispose properly? Did you make sure that the DataReader was disposed?

In general, you really should open a new question. But given how hard its been to get code from you that shows the minimal repro case, and instead you give us your entire project, it seems to be the lesser of two evils to just keep piling on this thread.

Update after: I just tried the following steps and didn't reproduce the error:
1) Start the program.
2) Click on the "Save Edit Delete" button.
3) Check the "Option: Enter tender number to edit or delete" checkbox.
4) In the dark gray box, enter "1234"
5) Click on the "Search" button.

Results:
No exception thrown.

 

[titojd]

Hi @Skydiver, thanks for your help on this topic.
It was a lot of learning for me, I understood how SqLite works,
but today, correcting it as you and the other colleagues suggested, I realized that the error was not there in the Bank but in my code,
I apologize for that,
I realized that in my Search Button (FrmSalvaResultado) I was checking if the contest exists, if not, it sends a message "that the contest does not exist", if it does exist it searches for the contest entered in txtConcurso.Text and fills in the 15 textBox with the Contest numbers, for me to edit or delete.
I made the Logica class as you mentioned and I also did what @Pablo put in the Text file, I got the same result, however when I commented on the code snippet for the Search Button removing the check (Verificar Existencia()) *(Comment 5/6 )* everything started to work perfectly, both in VS 2015 and VS 2019,
Maybe this happens because my search array only searches for the draw numbers and not the contest, since the contest is entered in a textBox separate from the array, and the checker searches for the contest (that's what I imagine) I don't know if I'm right.
once again @Skydiver, @Pablo, @cjard, I thank each and every one of you, thank you very much from the bottom of my heart

Here I will try to implement a different check for my Search Button, if I can't I will create a new question , Hugs.

 

[cjard]

A "uses Dapper" version, but I don't have any test data, so I can't easily test it.. Also some other refactoring that *should* work, but again, can't test

Install a tool like BeyondCompare to see the differences between old and new files