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

 

[Skydiver]

Cannot download file. It says "No preview available. File is in owner's bin".

I got the same. This is why we tell people to post the code to this forum instead of linking to an external location. The long term value of the thread is at serious risk.

 

[cjard]

The file was 58MB mind.. how you get that from a few hundred kilobytes app to read and write lottery numbers in a text file is anyone's guess

 

[Skydiver]

how you get that from a few hundred kilobytes app to read and write lottery numbers in a text file is anyone's guess

I know lots of beginners who focus on the UI and put background images and music in with their WinForms programs. That media takes up a lot of room. Also a lot of beginners zip up the entire solution project including the binaries instead of just the minimum source files needed to build the app.

 

[Pablo]

I've only seen SQLite "bugs" when people don't follow the documentation, specifically when people insist on putting the SQLite database on a file/SMB network share, and then are surprised that their database gets corrupted when there are concurrent writes.

Hi, @Skydiver
There are not concurrent writes in @titojd 's program. And, the same, there is that error 'database is locked'. If it was a web application which may have concurrent writes, it could be what you say, but being a simple desktop application that access the database, it can't, al least it's my vision. Also I don't have a network and got the error.
Anyway, if someone had the solution for that issue, I think titojd would be very thankful. I had no problem downloading the project with the first link he gave us. It would be very nice to find a solution for this thread, which is taking several weeks and at the beggining it already had a solution by handling a text file in memory (as a string, that's what it is) and only rewritting the file when a button was clicked, but it was said that code was not good and titojd was told to use a database that, by now, doesn't work. Regardless it is (for me at least) too much to use a database for so simple operations, reality is that it (SQLite) doesn't work, ... by now. If someone resolved the issue, I will be glad, too.
Regards
Pablo

 

[Skydiver]

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.

 

[cjard]

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

 

[Pablo]

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]

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

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]

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]

Perform an edit apparently

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

Show us yoir fixed code?

 

[Skydiver]

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:

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:

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]

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]

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

 

[Skydiver]

If you look at the GitHub link, that is all you really need to package together when you really need to share an entire project, instead of jsut the relevant code. There is no need to include the bin or obj directories. There is no need to include the packages directory. Everything else can be re-built as needed. (Granted the setup project will only work with VS2015 since that style of setup project has been deprecated in favor for the more mature and modern Wix style setup projects.)

Despite the tips on how to package up files to share a project, we still highly encourage people to just post the minimal amount of code to reproduce the problem. That is a lot of code in your project to wade through, when all I want to do is to reproduce the problem and get to the root cause of what is causing the problem.

 

[Pablo]

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.