Closing a database in VB .Net

  • Thread starter Thread starter Vegar Hansen
  • Start date Start date
V

Vegar Hansen

Hello everone.

Can somebody help me?
When I run my code I can read, write to and delete from database, but when I
have done one of these things and try to copy the databasefile from my
folder to an other folder with Windows Explorer the system "hangs up" for a
couple of minutes. It seems like the databasefile is not closed. But after
2 - 3 minutes the system recover, and I can copy the file without any system
"hangups".

This is a problem because when I try to write to or delete from the database
with my program, it seems like the program also "hangs up" and I must wait
2 - 3 minutes till it recover again. Then the changes is stored.

I use oledb.oledbconnection.

This is the code I use when I try to write to the database:

Dim sSQL As String

Dim connection As String

connection = "provider=Microsoft.JET.OLEDB.4.0;Data
Source=J:\Bestilling.mdb"

sSQL = "INSERT INTO Bestillinger (StudentNummer, DatamaskinType, LeieTid)
VALUES ('" & studentNr & "', '" & typeMaskin & "', '" & leiePeriode & "')"

Dim testConn As New System.Data.OleDb.OleDbConnection(connection)

Dim testCmd As New System.Data.OleDb.OleDbCommand(sSQL, testConn)

Try

testConn.Open()

Catch myExceptions As System.Exception

Console.WriteLine(myExceptions.Message)

End Try

Console.Write("Test", sSQL)

Console.ReadLine()

If testConn.State = ConnectionState.Open Then

Try

testCmd.ExecuteNonQuery()

Catch myExceptions As System.Exception

Console.WriteLine(myExceptions.Message)

Console.ReadLine()

End Try

testConn.Close()

End If
 
I'd make the following changes:

Change you SQL Statement

sSQL = "INSERT INTO Bestillinger (StudentNummer, DatamaskinType, LeieTid)
VALUES (?, ?,?)

testCmd.Parameters.Clear

With testCmd.Parameters
.Clear
.Add(studentNR)
.Add(typeMaskn)
.Add(leiePeriode)
End With

Now, add a Finally block in your try Catch and move the testConnection.Close
in that. If you get an Exception, your .Close may not get hit and therefore
the connection may not be getting closed properly. If the Application
bombs, all of the files and their handles are supposed to be cleaned up, but
if the app doesn't bomb and you don't close it, it may not close until after
the app is totally closed.

Now, the last line of your code right before the End Try add this..
Debug.Assert(testConnection.State = ConnectionState.Closed)

This won't fire in release code, but in debug it will. Basically, if you
don't see a big ugly dialog box, then your connection has been closed.

One other thing about Access, remember that its a Desktop Database so if you
are accessing it (pardon the pun) across the network, it's shoving a whole
lot of data over the network depending on the DB Size. this isn't the
fastest process--- but if it's taking a couple of minutes, then this
probably isn't the issue.

Anyway, make those changes and let me know what happens at the end...if you
don't see the box (make sure you build in Debug) from the failed assertion,
then it's not failing and the connection is closed so the problem is
something else.

One other thing, are there any other connections to the DB in your app, or
is there anyone/another program accessing the DB?

Let me know, hopefully we can get it worked out.

Bill
 
Ok, I`ll try this and I hope it works. The networkconnection I work on is a
100Mbit connection, so I know that it`s not that who are slowing down.
 
How do I add a Finally block in the try/catch? I quite not understand what u
mean with that?
 
Try

Catch myExceptions

Finally
Debug.Assert(testConnection.State = ConnectionsState.Closed
testConnection.Close

End Try
 
Back
Top