Clarification on how module variables work

[Julia B]

Hi

I have an intermittent problem with an intranet application and I'm
wondering if it has anything to do with the way I've created a shared module.

Here's a sample of it...

Module CommonPageUtilities

Private Connection As New OleDb.OleDbConnection
Private PopCommand As New OleDb.OleDbCommand
Private PopDataReader As OleDb.OleDbDataReader

Friend Sub PopEmailList(ByVal List As ListBox)
'populates a list box with a list of names from the users table
Connection.ConnectionString = Global.ConnectionString
PopCommand.Connection = Connection
Connection.Open()
PopCommand.CommandText = "SELECT Forename +' '+ Surname AS FullName,
UserID FROM UserPermissions " + _
"ORDER BY Forename +' '+ Surname"
PopDataReader = PopCommand.ExecuteReader(CommandBehavior.CloseConnection)
List.DataSource = PopDataReader
List.DataTextField = "FullName"
List.DataValueField = "UserID"
List.DataBind()
Connection.Close()
End Sub

Occasionally I get a problem where a user cannot open a page and gets the
error message that the Connection object connectionstring property cannot be
changed whilst the connection is open (state fetching).

I can't reproduce the error so am taking a stab in the dark here and
wondering if I've misunderstood how modules work... It's probably a
completely stupid question, but I've got to ask.

If one user runs the sub PopEmailList and another tries to run it at the
same time, do they use the same connection or each one of them creates a new
connection? I always believed it was the latter, but with the problem I'm
having I'm now wondering.

Thanks in advance.
Julia

 

[Julia B]

That's great thanks for your help!

No this is the first answer. Basically a module is a class that can only
have shared members. So in an ASP.NET application all users will share the
same connection.

In this particular case a quick fix would be to move the Connection
declaration inside your method as a local variable (anyway you already open
close the connection inside the method).