Typically, all code except for simple variable declarations should
occur within Try Blocks. This impoves the user experience when
problems occur and greatly simplifies debugging problems. However,
exception handling does incur a slight performance penalty.
Problem 1 your code:
Variable Declaration should be moved "OutSide" of the Try Block. This
is neccessary because the Finally block cannot access variables that
are declared within the Try block.
Example:
Dim cmd as New SqlConnection
Try
Catch
Finally
End Try
Problem 2 your code:
To close database connects, use the connection object's Close method.
Technically, you can also call the Dispose method of the connection
object to close the connection, but the preferred techinique is to call
the Close method.
You do not need to use Both SQLConn.Close, and SQLConn.Dispose
The OverKill:
The Finally block runs after the try block and any catch blocks have
finished executing. You should use the Finally block to close any open
objects. You do not need to place a try block in the Finally block.
Advice:
1. You can inspect the value of the connections current state. By
creating an event handler, or use an if statement, such as:
If sqlCon..State = ConnectionState .Open then
'do something
End if
2. You can catch an SQL exception. And inspect the
SQLException.Er rors property to access a collection of error that are
returned from the SQL server.
Morten Snedker wrote:
The use of Try in the Finally part - is that overkill?. I think of the
code failing before opening sqlCon - that would generate an error in
the Finally part. How would Finally handle that?
Try
Dim cmd As New SqlCommand
cmd.CommandText = "spSetAdLin ks"
cmd.CommandType = Data.CommandTyp e.StoredProcedu re
cmd.Connection = sqlCon
sqlCon.Open()
AdList = cmd.ExecuteRead er
Catch ex As Exception
AdList = Nothing
Finally
Try
sqlCon.Close()
sqlCon.Dispose( )
Catch ex As Exception
End Try
End Try
/Morten
