Your problem is that you have a global connection object that you use everywhere - and somewhere in your app you are not "completing" an operation. That may be a Reader you don't close, a Transaction you haven't Committed or Rolled back, or some other operation - we can't tell.
But you can't complete the new DB operation until the old one is finished because the connection is busy with the "old" job.
The best way round this is to always construct your Connection objects when you need them inside a
Using block so that the Connection is automatically Closed and Disposed when you are finished with it.
But ... that code is not good - the mere fact that you pass just a string to it as an SQL Command implies that you are vulnerable to SQL Injection, because you provide no mechanism to pass parameters to the query - so you probably do something like
Dim result As Integer = ExecuteScalar("SELECT stockLevel FROM Stock WHERE productName = '" & prodName.Text & "'")And that's very bad.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
Submit an article or tip