Results 1 to 10 of 10

Thread: [RESOLVED] UPDATE Query Throws error "Database is locked". help!

  1. #1

    Thread Starter
    Hyperactive Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    288

    Resolved [RESOLVED] UPDATE Query Throws error "Database is locked". help!

    Hello!

    I'm having trouble with my UPDATE Query, I'm using an SQLite DB, it has a table called Brand_Table, this table has only 2 columns: [ID] and [Name]. I can INSERT and DELETE records from it, but updating is giving me a headache.
    When I run my UPDATE Query it hangs for about 31 seconds then it throws this error:
    Name:  01c.png
Views: 196
Size:  17.1 KB

    I've been googling and checking lots of possible causes, but I found nothing to help in my particular scenario.
    this my SQLite DB:
    Name:  01a.png
Views: 199
Size:  6.0 KB
    Name:  01.png
Views: 183
Size:  9.3 KB

    This is my form:
    Name:  01b.jpg
Views: 181
Size:  15.0 KB

    And this my code:
    VB.NET Code:
    1. Using connection_1 As New SQLiteConnection("Data Source=C:\WinMiniSuper\DB2\MiniSuper.db;Version=3;")
    2.             If (connection_1.State = ConnectionState.Closed) Then connection_1.Open()
    3.             Dim ID_Scalar As Integer = 0
    4.             Using Command1 As New SQLiteCommand("SELECT ID_ FROM Brand_Table WHERE Name = '" & ComboBox1.Text & "'", connection_1)
    5.                 ID_Scalar = Convert.ToInt32(Command1.ExecuteScalar())
    6.             End Using
    7.             Using Command2 As New SQLiteCommand("UPDATE Brand_Table SET Name = @TB1 WHERE ID_ = '" & ID_Scalar & "'", connection_1)
    8.                 Command2.Parameters.AddWithValue("@TB1", TextBox1.Text)
    9.                 Command2.ExecuteNonQuery()
    10.             End Using
    11.         End Using

    I'll appreciate any help!

    Thank you.
    Last edited by Spybot; Aug 19th, 2021 at 07:54 PM. Reason: Forgot to include my code

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,803

    Re: UPDATE Query Throws error "Database is locked". help!

    Couple of things,

    Have you checked the value of "ID_Scalar"

    What data type is ID_ ?? You create ID_Scalar as an Integer but then you try to use it as a String '" & ID_Scalar & "'.

    Don't know why your getting a Database Locked error but there is definitely something wrong with your logic.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,774

    Re: UPDATE Query Throws error "Database is locked". help!

    Why are you using string concatenation to build your SQL code when you obviously know how to use parameters because you're doing it right there? Don't mix and match. ALWAYS use parameters. That way, you wouldn't accidentally put a number in quotes and turn it into text.

    Also, there seems no reason to be using two separate commands to begin with. You appear to be assuming that there is one and only one record with the specified Name, presumably because you retrieved those Name values previously, so just use that in an UPDATE statement:
    sql Code:
    1. UPDATE Brand_Table SET Name = @NewName WHERE Name = @OldName
    Notice that I used parameter names that are relevant to the actual data they represent. Naming a SQL parameter after the UI control its value comes from is terrible, especially when the name of that control is terrible to begin with.

  4. #4

    Thread Starter
    Hyperactive Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    288

    Re: UPDATE Query Throws error "Database is locked". help!

    Hi wes4!
    You're right, my logic was wrong. I've changed my query to:
    vb.net Code:
    1. Using Command2 As New SQLiteCommand("UPDATE Brand_Table SET Name = @TB1 WHERE ID_ = @IDSC", connection_1)
    2.      Command2.Parameters.AddWithValue("@TB1", TextBox1.Text)                
    3.      Command2.Parameters.AddWithValue("@IDSC", ID_Scalar)
    4. Command2.ExecuteNonQuery()
    5.             End Using
    But still not working!
    Last edited by Spybot; Aug 19th, 2021 at 08:44 PM.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,774

    Re: UPDATE Query Throws error "Database is locked". help!

    I suspect that the issue may be related to the fact that you're using an existing connection. Don't do that. ADO.NET has been designed such that you should create, use and discard connection objects in a local scope. Get rid of that class-level connection and create a connection where and when you need it:
    vb.net Code:
    1. Using connection As New SQLiteConnection("connection string here"),
    2.       command As New SQLiteCommand("SQL statement here", connection)
    3.     connection.Open()
    4.     command.ExecuteNonQuery()
    5. End Using
    That's the sort of thing you should be doing every time you access the database, not using a single connection and checking its state all the time. You may find that it's best to create a connection in one palce and then invoke that everywhere else, e.g.
    vb.net Code:
    1. Private Function GetConnection() As SQLiteConnection
    2.     Dim connection As New SQLiteConnection("connection string here")
    3.  
    4.     connection.Open()
    5.  
    6.     Return connection
    7. End Function
    and:
    vb.net Code:
    1. Using connection = GetConnection(),
    2.       command As New SQLiteCommand("SQL statement here", connection)
    3.     command.ExecuteNonQuery()
    4. End Using

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,803

    Re: UPDATE Query Throws error "Database is locked". help!

    Sorry, don't know why you would get a Database Locked error, if you are able to ADD and DELETE to the same database. Is the connectionstring different? Or have you done something that would lock the database.

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,774

    Re: UPDATE Query Throws error "Database is locked". help!

    There's some interesting but not really definitive information here:

    https://www.arysontechnologies.com/b...ed%20Error.%20

    That says:
    When you attempt to write in a table on which the SELECT operation is still active.
    It seems likely that there is some lingering effect from the SELECT statement when you try to execute the UPDATE statement but I'm not sure what that is or why it's happening. That said, I just saw a major flaw in what you're doing you are getting the Name value from a ComboBox and using that in a query to get the corresponding ID. Why would you need to do that in the first place? You presumably populated that ComboBox from the database in the first place so you should already have the ID value. If you don't fix that. Query the database to get the Name and ID and bind the result to the ComboBox:
    vb.net Code:
    1. Dim brandTable As New DataTable
    2.  
    3. Using adapter As New SQLiteDataAdapter("SELECT ID, Name FROM Brand_Table", "connection string here")
    4.     adapter.Fill(brandTable)
    5. End Using
    6.  
    7. With brandComboBox
    8.     .DisplayMember = "Name"
    9.     .ValueMember = "ID"
    10.     .DataSource = brandTable
    11. End With
    You can then get the ID of the selected brand from the SelectedValue of the ComboBox:
    vb.net Code:
    1. Using connection As New SQLiteConnection("connection string here"),
    2.       command As New SQLiteCommand("UPDATE Brand_Table SET Name = @Name WHERE ID_ = @ID", connection)
    3.     With command.Parameters
    4.         .Add("@Name", SQLiteType.Text).Value = brandNameTextBox.Text
    5.         .Add("@ID", SQLiteType.Integer).Value = brandComboBox.SelectedValue
    6.     End With
    7.  
    8.     connection.Open()
    9.     command.ExecuteNonQuery()
    10. End Using
    Of course, if you have the brands in a ComboBox and you're changing the name of one, you will presumably want to change that ComboBox too, so it seems more logical to just change the appropriate Name in the DataTable that is bound to the ComboBox first, then use a data adapter to save those changes back to the database. This is the proper way to make such changes in most cases, rather than modifying the database first and then pulling data from the database again that you already had.

  8. #8

    Thread Starter
    Hyperactive Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    288

    Re: UPDATE Query Throws error "Database is locked". help!

    Hi jmc!

    Thank you for your suggestion.
    I got rid of 3 SELECT statements that were before the UPDATE statement and it just worked along with this query:

    VB.NET Code:
    1. Using connection As New SQLiteConnection("Data Source=C:\WinMiniSuper\DB2\MiniSuper.db;Version=3;"),
    2.      command As New SQLiteCommand("UPDATE Brand_Table SET Name = @Name WHERE ID_ = @IDD", connection)
    3.             connection.Open()
    4.             With command.Parameters
    5.                 .AddWithValue("@Name", TextBox1.Text)
    6.                 .AddWithValue("@IDD", ComboBox1.SelectedValue)
    7.             End With
    8.             command.ExecuteNonQuery()
    9.         End Using

    Thank you all.

  9. #9

    Thread Starter
    Hyperactive Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    288

    Re: UPDATE Query Throws error "Database is locked". help!

    Just for the record, I just find out how to fix this "Database is Locked" error... as simple as finalizing everything withing the SQLite Query, I mean... if I use a connection I "End using" it, if I use a command, I "End using" it, if I use a DataReader, I "End using" it. you see what I mean. It doesn't matter if I have 3 SELECT statements before an UPDATE under the same connection, as long as I finalize all the "Usings" I don't get the error anymore.
    like this:
    VB.NET Code:
    1. Using Conection1 As New SQLiteConnection("Data Source=C:\WinMiniSuper\DB2\MiniSuper.db;Version=3;")
    2.    Conection1.Open()
    3.    Using Command As New SQLiteCommand("SELECT column_name FROM Products_Table WHERE column_name = '" & ComboBox1.Text & "'", Conection1)
    4.           Using Reader As SQLiteDataReader = Command.ExecuteReader
    5.                  If Reader.HasRows = True Then
    6.                      'has rows
    7.                  Else
    8.                     'No rows
    9.                  End If
    10.           End Using
    11.    End Using
    12. End Using

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,774

    Re: UPDATE Query Throws error "Database is locked". help!

    Quote Originally Posted by Spybot View Post
    Just for the record, I just find out how to fix this "Database is Locked" error... as simple as finalizing everything withing the SQLite Query, I mean... if I use a connection I "End using" it, if I use a command, I "End using" it, if I use a DataReader, I "End using" it. you see what I mean. It doesn't matter if I have 3 SELECT statements before an UPDATE under the same connection, as long as I finalize all the "Usings" I don't get the error anymore.
    like this:
    VB.NET Code:
    1. Using Conection1 As New SQLiteConnection("Data Source=C:\WinMiniSuper\DB2\MiniSuper.db;Version=3;")
    2.    Conection1.Open()
    3.    Using Command As New SQLiteCommand("SELECT column_name FROM Products_Table WHERE column_name = '" & ComboBox1.Text & "'", Conection1)
    4.           Using Reader As SQLiteDataReader = Command.ExecuteReader
    5.                  If Reader.HasRows = True Then
    6.                      'has rows
    7.                  Else
    8.                     'No rows
    9.                  End If
    10.           End Using
    11.    End Using
    12. End Using
    I'm not sure that that is a proper solution. You already had Using statements to create your command objects so you were already disposing them. I suspect that this might seem like a solution because if you dispose the connection then you close it, so that would release a lock on the database. The problem is that you then have to open another connection to execute another command. That's not how ADO.NET is supposed to work and it also would prevent you from executing multiple commands in a transaction. I suspect that there is some other "proper" solution to this issue but, not having used SQLite myself much, I don't really know what it would be. Maybe explicitly creating a transaction would help but, again, that's not something that you should have to do.

    As for the code you posted, It's not wrong but it could be cleaned up a little. There's no need to open the connection before you've even created the commmand, so you can actually us a single Using statement to create both the connection and the command, then open the connection. I also have a big problem with code like this:
    vb.net Code:
    1. If Reader.HasRows = True Then
    HasRows is already a Boolean so why compare it to another Boolean to get a third Boolean that you know will be the same as the first? Just use the Boolean you already have. That is why you should use names for Booleans that are like a true/false question, e.g. HasRows.
    VB.NET Code:
    1. Using connection As New SQLiteConnection("Data Source=C:\WinMiniSuper\DB2\MiniSuper.db;Version=3;"),
    2.       command As New SQLiteCommand("SELECT column_name FROM Products_Table WHERE column_name = '" & ComboBox1.Text & "'", connection)
    3.     connection.Open()
    4.  
    5.     Using reader As SQLiteDataReader = Command.ExecuteReader()
    6.         If reader.HasRows Then
    7.             'has rows
    8.         Else
    9.             'No rows
    10.         End If
    11.     End Using
    12. End Using
    You should also never be using string concatenation to insert values into SQL code like that either. If the values are hard-coded then you're less likely to encounter SQL injection but there are other pitfalls. If you just do the right thing every time then you can never accidentally do the wrong thing at the wrong time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width