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();
            }
        }
Nora
  • 46
  • 5
  • 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?](https://stackoverflow.com/q/35163361/1260204) – Igor May 09 '18 at 18:13
  • 1
    and you need single quotes around string values – Brad May 09 '18 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. – Alex K. May 09 '18 at 18:14
  • I just can't believe how many Sql injectable queries I see every day. This needs to stop. – Steve May 09 '18 at 18:16
  • I updated the code with all the connection lines and the rest of the function – Nora May 09 '18 at 18:17
  • 1
    Connections & Commands need to be disposed, preferably by using a `using () {}` construct. – Alex K. May 09 '18 at 18:17
  • I tried using the using construct however it is still now working. I placed the updated code above. – Nora May 09 '18 at 18:33
  • Place a `using` block around the connection. `using (SQLiteConnection myconn = new SQLiteConnection(/*connection*/)) { ..... /*code from question in here*/ }`. Also pass the parameters using the correct type and pass the values in their native type. if `@cid` is an `int` then convert the value to an `int` and pass the `int`. – Igor May 09 '18 at 18:34
  • I updated the code to what you said and I am still getting the database locked appearing 2 times. – Nora May 09 '18 at 18:40

2 Answers2

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.

CL.
  • 173,858
  • 17
  • 217
  • 259
0

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

Nora
  • 46
  • 5