0

I am currently writing a code using C# and SQLite. There is an error being throwing stating that the database is locked twice in a message box.

The query works on SQLite DB Browser however, when it is placed in C# code it throws the error.

Here is the code that is giving me an error:

cmd.CommandText = "UPDATE customers SET Bill = Bill - "+textBox2.Text+" WHERE customers.CustomerID = " + textBox1.Text + ";";

There seems to be an issue with the equals sign, might be something wrong with the arithmetic process.

Complete code:

 SQLiteConnection myconn = new SQLiteConnection(@"Data Source = C:\Users\chick\Newspaper.db");
        SQLiteCommand cmd = myconn.CreateCommand();
        cmd.CommandText = "UPDATE customers SET Bill = (Bill - "+textBox2.Text+") WHERE customers.CustomerID = " + textBox1.Text + ";";
        myconn.Open();
        try
        {
            cmd.ExecuteNonQuery();
            MessageBox.Show("Succesfully Update");
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

UPDATE: Changed format to using() {} however it is still not working. Program is crashing

New Code:

   using (SQLiteConnection myconn = new SQLiteConnection(@"Data Source = C:\Users\chick\Newspaper.db"))
        {

            var sql = "Update customers SET Bill = Bill - @pay WHERE customers.CustomerID = @cid;";
            myconn.Open();
            using (var cmd = new SQLiteCommand(sql, myconn))
            {
                cmd.Parameters.AddWithValue("@cid", textBox1.Text);
                cmd.Parameters.AddWithValue("@pay", textBox2.Text);
                cmd.ExecuteNonQuery();
            }
        }
9
  • 1
    Please use parameterized queries. This generally solves many of the formatting and syntax errors in queries from the get go and as an added benifit it will protect your system from sql injection attacks. Please refer to How can I add user-supplied input to an SQL statement? Commented May 9, 2018 at 18:13
  • 1
    and you need single quotes around string values Commented May 9, 2018 at 18:14
  • 1
    You have SQL Injection vulnerabilities. The locking issue is likely related to how you use the connection/command objects, which you have not shared. Commented May 9, 2018 at 18:14
  • I just can't believe how many Sql injectable queries I see every day. This needs to stop. Commented May 9, 2018 at 18:16
  • 1
    Connections & Commands need to be disposed, preferably by using a using () {} construct. Commented May 9, 2018 at 18:17

2 Answers 2

1

The problem is that some other part of the program (or some other program) still has an active transaction.

You must properly clean up all commands that access the database, i.e., use using everywhere.

Sign up to request clarification or add additional context in comments.

Comments

0

Thank you to all that have answered. We realized the issue was that we never closed the reader in previous code.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.