connection string question

Being new to .net but not new to classic asp I have a question about the connection object.

I'd like to be able to reference the same connection object with any SQL command on my code behind page, but it seems like it closes after the first command is completed. I get this error:

ExecuteReader requires an open and available Connection. The connection's current state is closed.

Below is the code I'm using, this is just basic stuff for a test. Any help would be appreciated Oh, by the way, when I did this with JUST the first Sub being called it worked fine.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Partial Class AZone_ConnectionTest
Inherits System.Web.UI.Page

Public con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
con.Open()
FirstLabel()
SecondLabel()
con.Close()
End Sub

Sub FirstLabel()
' command
Dim cmd As New SqlCommand
cmd.Connection = con
cmd.CommandText = "select top 1 PropertyName from tblProperties"

' using con
Using con
Dim r As SqlDataReader = cmd.ExecuteReader
r.Read()
lblFirst.Text = r("PropertyName")
r.Close()
End Using
' clear command
cmd.Dispose()
End Sub

Sub SecondLabel()
' command
Dim cmd As New SqlCommand
cmd.Connection = con
cmd.CommandText = "SELECT top 1 DevID FROM tblProperties"

' using con
Using con
Dim r As SqlDataReader = cmd.ExecuteReader
r.Read()
lblSecond.Text = r("DevID")
r.Close()
End Using
' clear command
cmd.Dispose()
End Sub

End Class

You are calling the connection using a directive (no pun intended).

When you use a directive the object gets diposed at the end of the scopoe. hence, your statement End Using. meanind the connection is now closed at this point.

|||

I'm not very familiar with the syntax of Visual Basic.NET, but most often it is extremely like C#. In C# (and I believe it is the same with VB.NET) the using statement will dispose the object at the end. Thus the following:

using (SqlConnection connection =new SqlConnection(myConnectionString)){ connection.Open(); ...//Do stuff with the connection here.}

Would in fact be the same as writing:

try{ SqlConnection connection =new SqlConnection(myConnectionString); connection.Open(); ...//Do stuff with the connection here.}finally{ ((IDisposable)connection).Dispose();//Which effectively will close the connection}

The call to Dispose in the end of theusing statement will thus close the connection.|||

ExecuteReader closes its connection automatically when it reaches end of file. Strictly speaking though the connection was actually returned to a pool of connections for use by another read request. Provided all the connection strings are defined exactly the same, connections are pooled automatically. Thus you do not need to (and should not) define a connection globally. In this way ADO.NET becomes much faster than ADO in connections.

|||

OK, well in the second sub I tried opening the connection again and got THIS message

The ConnectionString property has not been initialized.

|||

The object got disposed, there is no reference to an object to open.

Take out the using statements, your not using them correctly.

|||

Thanks, that did the trick (removing 'using') can you explain what "using" is for?

|||

The using statement is there to make sure that the object is disposed, to make sure you don't end up forgetting to, for example, close a connection to a database. Look at my previous post and you'll see what the using statement actually does.

|||

In addition, look up the term "Directives" in .Net, this will clear it up for you.

|||

thanks, I appreciate everyone's help and information