Last post Oct 04, 2007 04:56 AM by shakti5385
Oct 01, 2007 07:49 AM|azrina|LINK
I am using VB.net 2002 and SQL Server 2005 Express Edition as database.
I am learning how to create a class function that is reusable in an application.
For that, I tried to put a connection to a database in a public class.
And also, disconnect database in a class too.
But , I got a problem to read from a DataReader.
Below are the functions that I create to connect and disconnect from database.
Public Class Class1
Dim conn As New SqlClient.SqlConnection()
Public Function ConnectDB()
conn = New SqlClient.SqlConnection("Server=SEN-M092082D001\SQLEXPRESS;Database=test1;Trusted_Connection=True;")
Public Function CloseDB()
And, also a Page_Load event where the error comes from. The commented line is where the error lies.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here Dim conn As New SqlConnection()
Dim comm As New SqlCommand() Dim dtReader As SqlDataReader Dim item As ListItem Dim c As New Class1() If Not Page.IsPostBack Then
c.ConnectDB() comm = New SqlCommand("select * from tblCountry order by name asc", conn) dtReader = comm.ExecuteReader() 'error with this line Do While dtReader.Read item = New ListItem() item.Text = dtReader("name") item.Value = dtReader("id") ddlCountry.Items.Add(item) Loop
dtReader.Close() c.CloseDB() End If End Sub
The error tells: ExecuteReader requires an open and available Connection. The connection's current state is Closed.
Why is that? I already called the function to open the database.
Hope somone here can help me to solve this prob. May be the way I think of the logic is wrong. Or may be the syntax there is wrong too.
Oct 01, 2007 11:23 AM|shakti5385|LINK
public SqlDataReader ReturnDataReader(string queryString)
SqlCommand SqlCommand = new
SqlCommand = ReturnCommand();
SqlCommand.CommandText = queryString;
sqlDataReader = SqlCommand.ExecuteReader();
catch (Exception ex)
/*THIS PRIVATE FUNCTION RETURN A NEW SQLCOMMAND */
private SqlCommand ReturnCommand()
SqlConnection SqlConnection =
SqlConnection = OpenNewConnection();//OPEN CONNECTION HERE
SqlCommand.CommandType = CommandType.Text;
SqlCommand.Connection = SqlConnection;
catch (Exception Ex)
Oct 02, 2007 12:22 AM|azrina|LINK
Thank you for ur explaination.
Just remember that every Data reader require a different connection.To use a datareader you:
That means every time I want to connect to datareader, I have to create new connection for that. And this results to
creating a public class to call a database connection is not possible. Is this what u mean?
I dont really understand ur code. Can u explain more? From your code, wheres the code to open connection and close connection and also where to put the query. Or may be can u translate it into VB.Net.
Oct 02, 2007 06:17 AM|shakti5385|LINK
#Region "Private Variables"
Private _ConnectionString As
#Region "Public Properties"
'Public Property ConnectionStringName() As String
' Return _ConnectionStringName
' End Get
' Set(ByVal value As String)
' If value Is Nothing Or value = "" Then
' Throw New Exception("Can not set null value in Connection String!!!")
' End If
' _ConnectionStringName = value
' _ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings(value).ConnectionString
' 'THIS GET THE CONNECTION FROM THE CONFIGURATION FILE
' End Set
#Region "Private Functions"
Private Function ReturnCommand()
Dim connection As SqlConnection =
connection = OpenNewConnection()
Dim Command As SqlCommand = connection.CreateCommand()
Command.CommandType = CommandType.Text
Command.Connection = connection
Catch ex As Exception
Public Function ReturnDataReader(ByVal QueryString
Dim command As SqlCommand = ReturnCommand()
command.CommandText = QueryString
Dim dataReader As SqlDataReader
dataReader = command.ExecuteReader()
Catch ex As SqlClient.SqlException
Throw New Exception("SQL Exception ", ex)
Catch exx As Exception
Throw New Exception("Other ", exx)
#Region "Public Functions"
Public Function OpenNewConnection()
_ConnectionString = "Server=SEN-M092082D001\SQLEXPRESS;Database=test1;Trusted_Connection=True;"
'YOU CAN US THE PROPERTY ALSO THAT I MENTION ABOVE FOR GETTING THE CONNECTION FROM THE WEB CONFIG FILE
Dim NewConnection As
Next show the way how to use
'NOW ON PAGE LOAD
Dim databaseHelper As
Dim reader As SqlDataReader
reader = databaseHelper.ReturnDataReader("PASS THE QUERY HERE")
Oct 02, 2007 08:34 AM|azrina|LINK
Now I got what u mean.
I tried the class that u create and it works.
I even tried to call the class u create from other web form and it works too. This is what I exactly wanted to do. But I did it wrongly.
Thank you for your help. Really appreciate it.
Oct 02, 2007 08:49 AM|azrina|LINK
Something is bothering me.
How to close the database connection?
Oct 03, 2007 05:22 AM|shakti5385|LINK
GOOD LUCK [Yes]
Oct 03, 2007 09:00 AM|azrina|LINK
Thanks for ur reply.
For every form make a new object so no need to close the connection, when you close the old form then DOT NET garbage collection automatically dispose the unused memory.
Yes we must close the connection if we are not using the class method and opening a connection behind the form
From what I learn thru internet tutorial, there must be Open Connection and Close Connection of database. That is why I asked you coz I could see u open the connection to the database when u create a sql command by calling the OpenNewConnection function
and no closing for the connection. What u mean behind the form is e.g we create the connection in the Page_load event, is it?
Based on ur reply, the main reason why theres no need to close connection is because this action will be taken care by the GC . Is this correct?
Since we are using the class method so leave the closing action to the GC. Correct?
Oct 03, 2007 09:26 AM|shakti5385|LINK
1) What u mean behind the form is e.g we create the connection in the Page_load event, is it?
Oct 03, 2007 11:25 AM|shakti5385|LINK
Read this also
Oct 04, 2007 02:27 AM|azrina|LINK
Hi, thanks for ur patience in aswering my questions. :) (I am not that good in programming)
After reading the articles u provided, it has given me the knowledge about the importance of the GC in .Net.
And especially how it relates to close or dispose the sql connection.
Thanks again, I will try to implement what i have read thru the articles.
Oct 04, 2007 04:56 AM|shakti5385|LINK
Your Welcome [Yes]