System.NullReferenceException

How must i declare my variables to have this code work. I get
system.nullreferenceexception every time i click button to add data to
datatable
here below is my code :
Dim wykszt as System.Data.DataTable
Dim zatru as System.Data.DataTable
Private Sub Page_Load(s As Object, e As EventArgs)
if Not IsPostBack Then
make_d_tables()
End if
End Sub
sub make_d_tables()
wykszt = New System.Data.DataTable("wykszt")
wykszt.Columns.Add("od_data", GetType(date))
wykszt.Columns.Add("do_data", GetType(date))
wykszt.Columns.Add("nazwa_szkoly", GetType(String))
wykszt.Columns.Add("nazwa_tytulu", GetType(String))
zatru = New System.Data.DataTable("zatr")
zatru.Columns.Add("od_data", GetType(date))
zatru.Columns.Add("do_data", GetType(date))
zatru.Columns.Add("nazwa_prac", GetType(String))
zatru.Columns.Add("nazwa_stan", GetType(String))
End sub
Sub Button2_Click(sender As Object, e As EventArgs)
row = wykszt.NewRow
row("od_data")=TextBox27.Text + "-01"
row("do_data")=TextBox28.Text + "-01"
row("nazwa_szkoly")=TextBox29.Text
row("nazwa_tytulu")=TextBox30.Text
wykszt.Rows.Add(row)
row = wykszt.NewRow
Mxdatagrid1.datasource=wykszt
Mxdatagrid1.databind
End Sub
Thanks
PatYou have to recreate your tables every time. So take out the if statement
from page_load.
"ven" <venome_@.poczta.onet.pl> wrote in message
news:cif6p8$sf3$1@.news.onet.pl...
> How must i declare my variables to have this code work. I get
> system.nullreferenceexception every time i click button to add data to
> datatable
> here below is my code :
> Dim wykszt as System.Data.DataTable
> Dim zatru as System.Data.DataTable
> Private Sub Page_Load(s As Object, e As EventArgs)
> if Not IsPostBack Then
> make_d_tables()
> End if
> End Sub
> sub make_d_tables()
> wykszt = New System.Data.DataTable("wykszt")
> wykszt.Columns.Add("od_data", GetType(date))
> wykszt.Columns.Add("do_data", GetType(date))
> wykszt.Columns.Add("nazwa_szkoly", GetType(String))
> wykszt.Columns.Add("nazwa_tytulu", GetType(String))
> zatru = New System.Data.DataTable("zatr")
> zatru.Columns.Add("od_data", GetType(date))
> zatru.Columns.Add("do_data", GetType(date))
> zatru.Columns.Add("nazwa_prac", GetType(String))
> zatru.Columns.Add("nazwa_stan", GetType(String))
> End sub
> Sub Button2_Click(sender As Object, e As EventArgs)
> row = wykszt.NewRow
> row("od_data")=TextBox27.Text + "-01"
> row("do_data")=TextBox28.Text + "-01"
> row("nazwa_szkoly")=TextBox29.Text
> row("nazwa_tytulu")=TextBox30.Text
> wykszt.Rows.Add(row)
> row = wykszt.NewRow
> Mxdatagrid1.datasource=wykszt
> Mxdatagrid1.databind
> End Sub
>
> Thanks
> Pat
>
Which line is throwing the exception?
Does the error only happen on postback?
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
"ven" <venome_@.poczta.onet.pl> wrote in message
news:cif6p8$sf3$1@.news.onet.pl...
> How must i declare my variables to have this code work. I get
> system.nullreferenceexception every time i click button to add data to
> datatable
> here below is my code :
> Dim wykszt as System.Data.DataTable
> Dim zatru as System.Data.DataTable
> Private Sub Page_Load(s As Object, e As EventArgs)
> if Not IsPostBack Then
> make_d_tables()
> End if
> End Sub
> sub make_d_tables()
> wykszt = New System.Data.DataTable("wykszt")
> wykszt.Columns.Add("od_data", GetType(date))
> wykszt.Columns.Add("do_data", GetType(date))
> wykszt.Columns.Add("nazwa_szkoly", GetType(String))
> wykszt.Columns.Add("nazwa_tytulu", GetType(String))
> zatru = New System.Data.DataTable("zatr")
> zatru.Columns.Add("od_data", GetType(date))
> zatru.Columns.Add("do_data", GetType(date))
> zatru.Columns.Add("nazwa_prac", GetType(String))
> zatru.Columns.Add("nazwa_stan", GetType(String))
> End sub
> Sub Button2_Click(sender As Object, e As EventArgs)
> row = wykszt.NewRow
> row("od_data")=TextBox27.Text + "-01"
> row("do_data")=TextBox28.Text + "-01"
> row("nazwa_szkoly")=TextBox29.Text
> row("nazwa_tytulu")=TextBox30.Text
> wykszt.Rows.Add(row)
> row = wykszt.NewRow
> Mxdatagrid1.datasource=wykszt
> Mxdatagrid1.databind
> End Sub
>
> Thanks
> Pat
>
Uytkownik "Steve C. Orr [MVP, MCSD]" <Steve@.Orr.net> napisa w wiadomoci
news:OkGuVzNnEHA.1304@.TK2MSFTNGP09.phx.gbl...
> Which line is throwing the exception?
> Does the error only happen on postback?
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://Steve.Orr.net
>
> "ven" <venome_@.poczta.onet.pl> wrote in message
> news:cif6p8$sf3$1@.news.onet.pl...
>
sorry i left behind that i have Dim row as System.Data.DataRow in my
declarations too
but exception is only when i post back and
on this line :
row = wykszt.NewRow
and a full stack :
Szczegy wyjtku: System.NullReferenceException: Odwoanie do obiektu nie
zostao ustawione na wystpienie obiektu.
Bd rda:
Wiersz 35: Sub Button2_Click(sender As Object, e As EventArgs)
Wiersz 36:
Wiersz 37: row = wykszt.NewRow
Wiersz 38: row("od_data")=TextBox27.Text + "-01"
Wiersz 39: row("do_data")=TextBox28.Text + "-01"
Plik rdowy: c:\inetpub\wwwroot\axell\forma.aspx Wiersz: 37
lad stosu:
[NullReferenceException: Odwoanie do obiektu nie zostao ustawione na
wystpienie obiektu.]
ASP.forma_aspx.Button2_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\axell\forma.aspx:37
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument) +58
System.Web.UI.Page. RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
sorry for polish language
Thanks
Pat
In that case I agree with Marina.
You need to recreate your table upon each postback.
Right now you aren't doing that, so you can't add a row to a table that
doesn't exist, thus your error.
Remove the If statement in Page_Load.
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

> sorry i left behind that i have Dim row as System.Data.DataRow in my
> declarations too
> but exception is only when i post back and
> on this line :
> row = wykszt.NewRow
> and a full stack :
> Szczegy wyjtku: System.NullReferenceException: Odwoanie do obiektu nie
> zostao ustawione na wystpienie obiektu.
> Bd rda:
> Wiersz 35: Sub Button2_Click(sender As Object, e As EventArgs)
> Wiersz 36:
> Wiersz 37: row = wykszt.NewRow
> Wiersz 38: row("od_data")=TextBox27.Text + "-01"
> Wiersz 39: row("do_data")=TextBox28.Text + "-01"
>
> Plik rdowy: c:\inetpub\wwwroot\axell\forma.aspx Wiersz: 37
> lad stosu:
> [NullReferenceException: Odwoanie do obiektu nie zostao ustawione na
> wystpienie obiektu.]
> ASP.forma_aspx.Button2_Click(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\axell\forma.aspx:37
> System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
> System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.Raise
Po
> stBackEvent(String eventArgument) +58
> System.Web.UI.Page. RaisePostBackEvent(IPostBackEventHandler
> sourceControl, String eventArgument) +18
> System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
> System.Web.UI.Page.ProcessRequestMain() +1277
> sorry for polish language
> Thanks
> Pat
>

System.NullReferenceException

How must i declare my variables to have this code work. I get
system.nullreferenceexception every time i click button to add data to
datatable
here below is my code :

Dim wykszt as System.Data.DataTable
Dim zatru as System.Data.DataTable

Private Sub Page_Load(s As Object, e As EventArgs)

if Not IsPostBack Then
make_d_tables()
End if
End Sub

sub make_d_tables()

wykszt = New System.Data.DataTable("wykszt")
wykszt.Columns.Add("od_data", GetType(date))
wykszt.Columns.Add("do_data", GetType(date))
wykszt.Columns.Add("nazwa_szkoly", GetType(String))
wykszt.Columns.Add("nazwa_tytulu", GetType(String))

zatru = New System.Data.DataTable("zatr")
zatru.Columns.Add("od_data", GetType(date))
zatru.Columns.Add("do_data", GetType(date))
zatru.Columns.Add("nazwa_prac", GetType(String))
zatru.Columns.Add("nazwa_stan", GetType(String))

End sub

Sub Button2_Click(sender As Object, e As EventArgs)

row = wykszt.NewRow
row("od_data")=TextBox27.Text + "-01"
row("do_data")=TextBox28.Text + "-01"
row("nazwa_szkoly")=TextBox29.Text
row("nazwa_tytulu")=TextBox30.Text
wykszt.Rows.Add(row)
row = wykszt.NewRow
Mxdatagrid1.datasource=wykszt
Mxdatagrid1.databind

End Sub

Thanks

PatYou have to recreate your tables every time. So take out the if statement
from page_load.

"ven" <venome_@.poczta.onet.pl> wrote in message
news:cif6p8$sf3$1@.news.onet.pl...
> How must i declare my variables to have this code work. I get
> system.nullreferenceexception every time i click button to add data to
> datatable
> here below is my code :
> Dim wykszt as System.Data.DataTable
> Dim zatru as System.Data.DataTable
> Private Sub Page_Load(s As Object, e As EventArgs)
> if Not IsPostBack Then
> make_d_tables()
> End if
> End Sub
> sub make_d_tables()
> wykszt = New System.Data.DataTable("wykszt")
> wykszt.Columns.Add("od_data", GetType(date))
> wykszt.Columns.Add("do_data", GetType(date))
> wykszt.Columns.Add("nazwa_szkoly", GetType(String))
> wykszt.Columns.Add("nazwa_tytulu", GetType(String))
> zatru = New System.Data.DataTable("zatr")
> zatru.Columns.Add("od_data", GetType(date))
> zatru.Columns.Add("do_data", GetType(date))
> zatru.Columns.Add("nazwa_prac", GetType(String))
> zatru.Columns.Add("nazwa_stan", GetType(String))
> End sub
> Sub Button2_Click(sender As Object, e As EventArgs)
> row = wykszt.NewRow
> row("od_data")=TextBox27.Text + "-01"
> row("do_data")=TextBox28.Text + "-01"
> row("nazwa_szkoly")=TextBox29.Text
> row("nazwa_tytulu")=TextBox30.Text
> wykszt.Rows.Add(row)
> row = wykszt.NewRow
> Mxdatagrid1.datasource=wykszt
> Mxdatagrid1.databind
> End Sub
>
> Thanks
> Pat
Which line is throwing the exception?
Does the error only happen on postback?

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

"ven" <venome_@.poczta.onet.pl> wrote in message
news:cif6p8$sf3$1@.news.onet.pl...
> How must i declare my variables to have this code work. I get
> system.nullreferenceexception every time i click button to add data to
> datatable
> here below is my code :
> Dim wykszt as System.Data.DataTable
> Dim zatru as System.Data.DataTable
> Private Sub Page_Load(s As Object, e As EventArgs)
> if Not IsPostBack Then
> make_d_tables()
> End if
> End Sub
> sub make_d_tables()
> wykszt = New System.Data.DataTable("wykszt")
> wykszt.Columns.Add("od_data", GetType(date))
> wykszt.Columns.Add("do_data", GetType(date))
> wykszt.Columns.Add("nazwa_szkoly", GetType(String))
> wykszt.Columns.Add("nazwa_tytulu", GetType(String))
> zatru = New System.Data.DataTable("zatr")
> zatru.Columns.Add("od_data", GetType(date))
> zatru.Columns.Add("do_data", GetType(date))
> zatru.Columns.Add("nazwa_prac", GetType(String))
> zatru.Columns.Add("nazwa_stan", GetType(String))
> End sub
> Sub Button2_Click(sender As Object, e As EventArgs)
> row = wykszt.NewRow
> row("od_data")=TextBox27.Text + "-01"
> row("do_data")=TextBox28.Text + "-01"
> row("nazwa_szkoly")=TextBox29.Text
> row("nazwa_tytulu")=TextBox30.Text
> wykszt.Rows.Add(row)
> row = wykszt.NewRow
> Mxdatagrid1.datasource=wykszt
> Mxdatagrid1.databind
> End Sub
>
> Thanks
> Pat
Uytkownik "Steve C. Orr [MVP, MCSD]" <Steve@.Orr.net> napisa w wiadomoci
news:OkGuVzNnEHA.1304@.TK2MSFTNGP09.phx.gbl...
> Which line is throwing the exception?
> Does the error only happen on postback?
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://Steve.Orr.net
>
> "ven" <venome_@.poczta.onet.pl> wrote in message
> news:cif6p8$sf3$1@.news.onet.pl...
> > How must i declare my variables to have this code work. I get
> > system.nullreferenceexception every time i click button to add data to
> > datatable
> > here below is my code :
> > Dim wykszt as System.Data.DataTable
> > Dim zatru as System.Data.DataTable
> > Private Sub Page_Load(s As Object, e As EventArgs)
> > if Not IsPostBack Then
> > make_d_tables()
> > End if
> > End Sub
> > sub make_d_tables()
> > wykszt = New System.Data.DataTable("wykszt")
> > wykszt.Columns.Add("od_data", GetType(date))
> > wykszt.Columns.Add("do_data", GetType(date))
> > wykszt.Columns.Add("nazwa_szkoly", GetType(String))
> > wykszt.Columns.Add("nazwa_tytulu", GetType(String))
> > zatru = New System.Data.DataTable("zatr")
> > zatru.Columns.Add("od_data", GetType(date))
> > zatru.Columns.Add("do_data", GetType(date))
> > zatru.Columns.Add("nazwa_prac", GetType(String))
> > zatru.Columns.Add("nazwa_stan", GetType(String))
> > End sub
> > Sub Button2_Click(sender As Object, e As EventArgs)
> > row = wykszt.NewRow
> > row("od_data")=TextBox27.Text + "-01"
> > row("do_data")=TextBox28.Text + "-01"
> > row("nazwa_szkoly")=TextBox29.Text
> > row("nazwa_tytulu")=TextBox30.Text
> > wykszt.Rows.Add(row)
> > row = wykszt.NewRow
> > Mxdatagrid1.datasource=wykszt
> > Mxdatagrid1.databind
> > End Sub
> > Thanks
> > Pat

sorry i left behind that i have Dim row as System.Data.DataRow in my
declarations too
but exception is only when i post back and
on this line :
row = wykszt.NewRow

and a full stack :

Szczegy wyjtku: System.NullReferenceException: Odwoanie do obiektu nie
zostao ustawione na wystpienie obiektu.

Bd rda:

Wiersz 35: Sub Button2_Click(sender As Object, e As EventArgs)
Wiersz 36:
Wiersz 37: row = wykszt.NewRow
Wiersz 38: row("od_data")=TextBox27.Text + "-01"
Wiersz 39: row("do_data")=TextBox28.Text + "-01"

Plik rdowy: c:\inetpub\wwwroot\axell\forma.aspx Wiersz: 37

lad stosu:

[NullReferenceException: Odwoanie do obiektu nie zostao ustawione na
wystpienie obiektu.]
ASP.forma_aspx.Button2_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\axell\forma.aspx:37
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePo
stBackEvent(String eventArgument) +58
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277

sorry for polish language

Thanks

Pat
In that case I agree with Marina.
You need to recreate your table upon each postback.
Right now you aren't doing that, so you can't add a row to a table that
doesn't exist, thus your error.
Remove the If statement in Page_Load.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

> sorry i left behind that i have Dim row as System.Data.DataRow in my
> declarations too
> but exception is only when i post back and
> on this line :
> row = wykszt.NewRow
> and a full stack :
> Szczegy wyjtku: System.NullReferenceException: Odwoanie do obiektu nie
> zostao ustawione na wystpienie obiektu.
> Bd rda:
> Wiersz 35: Sub Button2_Click(sender As Object, e As EventArgs)
> Wiersz 36:
> Wiersz 37: row = wykszt.NewRow
> Wiersz 38: row("od_data")=TextBox27.Text + "-01"
> Wiersz 39: row("do_data")=TextBox28.Text + "-01"
>
> Plik rdowy: c:\inetpub\wwwroot\axell\forma.aspx Wiersz: 37
> lad stosu:
> [NullReferenceException: Odwoanie do obiektu nie zostao ustawione na
> wystpienie obiektu.]
> ASP.forma_aspx.Button2_Click(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\axell\forma.aspx:37
> System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
> System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePo
> stBackEvent(String eventArgument) +58
> System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
> sourceControl, String eventArgument) +18
> System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
> System.Web.UI.Page.ProcessRequestMain() +1277
> sorry for polish language
> Thanks
> Pat

System.NullReferenceException: Object reference not set to an instance of an object.

This is the error message I get:
System.NullReferenceException: Object reference not set to an instance of an object.
at MyStore.CustomerFunctions.getCustomerOrders(String CustomerID)
at MyStore.Customer_Orders.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain()
And this is my code:

PublicSharedFunction getCustomerOrders(ByVal CustomerIDAsString)As OrderCollection

Dim strSQLAsString
Dim m_strConnectionStringAsString
Dim MyReaderAs SqlDataReader
Dim mySqlParaAs SqlParameter
Dim MyOrdersAsNew OrderCollection

m_strConnectionString = ConfigurationSettings.AppSettings("ConnectionString_checkout")

strSQL = "sp_WWWgetCustomerOrders"

mySqlPara =New SqlParameter("@dotnet.itags.org.CustomerID", CustomerID)

Try
MyReader = SqlHelper.ExecuteReader(m_strConnectionString, CommandType.StoredProcedure, strSQL, mySqlPara)
MyOrders.Fill(MyReader)

Catch exAs Exception
' Rethrow Exception for the moment
Throw ex

Finally
MyReader.Close()
MyReader =Nothing

EndTry

Return MyOrders

EndFunction


Code that calls the method:

DimaCustomerAs Customer
Dim MyOrdersAs OrderCollection
aCustomer =CType(HttpContext.Current.User, CustomPrincipal).Customer

MyOrders = CustomerFunctions.getCustomerOrders(aCustomer.CustomerID)

If MyOrders.Count > 0Then
Me.rptOpenRecentOrders.DataSource = MyOrders
Me.rptOpenRecentOrders.DataBind()
pnlNoOrdersToDisplay.Visible =False
Else
pnlNoOrdersToDisplay.Visible =True
EndIf

I only get the error occasionally at busy times on the internet, the Stored Proc is valid the connection string is valid because it works some times. The problem is not incorrect data being passed because if no data or incorrect data is past a different exception will be displayed because I have tested. I have no idea why this is happening, I have a feeling its to do with SQL Server because it only happens when it is at its busiest.
If the Stored Proc returns an empty reader there is no error, I have alreadyinitialized My returning object (the Orders Collection) so it shouldn't matter.
I know that the customer id is valid because I have an email sent to me if there is an error in the application via the globalApplication_Errormethod, it gets the customer id from the currently logged in person
Dim aCustomerAs Customer =CType(HttpContext.Current.User, CustomPrincipal).Customer
strCustID = aCustomer.CustomerID
This is displayed correctly when emailed. As I say this only happens at busy times and I am pulling my hair out to identify whats happening, can any one help?
Cheers
Scott

elbandit--
Regarding this...

elbandit wrote:

...As I say this only happens at busy times and I am pulling my hair out to identify whats happening, can any one help?

...I am wondering-- exactly what line is causing the error?
(The line can be discovered by doing a step-through of the code.)
I am going to guess that it is this line...
Do While MyReader.Read = True
...that causes the error.
If so, then you may be able to check for Nothing before the operation, like this...
If (MyReader Is Nothing) Then
'Reader cannot be used, probably due to a database connection issue.
'Maybe retry 1x automatically and if no connection then show message to user and ask the user to retry.
Else
Do While MyReader.Read = True
'Continue.
End If
...which may or may not work based on your use case.
HTH.
Thank you.
--Mark Kamoski
Sorry Mark I posted the wrong function, I have now editied. Please can you have another look,
Thanks
Scott
i can not see in your code
Dim con as New Sqlconenction(connection string her)

is it some where else or that what you miss!!??
elbandit--
Regarding this...

elbandit wrote:

Sorry Mark I posted the wrong function, I have now editied. Please can you have another look...

...I still think that the issue may be that the Reader is not getting instantiated properly when then database is too busy. As I mentioned above, you may need to check for null (Nothing) before using it.
If the database is busy, you may need to increase the timeout (a property on the connection string, seehttp://www.ConnectionStrings.com for details).
Alternately, if the database is really busy maybe your licensing has reached the maximum number of of connections and that's why the Reader is not instantiated properly.
Another idea would be to use a DataAdapter and a DataSet. Yours is a simple case and these objects might be a bit more stable given that the Reader stays open until it is closed and you really do not need an open connection to the database in this case.
The code looks something like this...
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(COMMAND_TEXT, myConnection);
System.Data.OleDb.OleDbDataAdapter myDataAdapter = new System.Data.OleDb.OleDbDataAdapter(myCommand);
DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet);
...or you can seehttp://www.WebLogicArts.com/DemoReadingData01.aspx for a full working sample with all the code.
HTH.
Thank you.
--Mark Kamoski

Thanks Mark, I think you are probably right, as it only errors in busy periods I have changed the code to:

MyReader = SqlHelper.ExecuteReader(m_strConnectionString, CommandType.StoredProcedure, strSQL, mySqlPara)

If MyReaderIsNothingThen
Email.sendEmail()
EndIf
MyOrders.Fill(MyReader)

This way it will still error but I will get an email if the problem is that the reader is null, then I will be able to test with populating a dataset then populating my custom collection from that. The reason I didn't do it like that to begin with is that I thought a reader populated a dataset, but I guess if that is true the reader will populate a dataset faster than could populate my custom collection and therefore no error.
I will check out the time out property as well and let you know how I got on, and post the code that works. Cheers.
Thanks for the replies
Scott
P.S.
Fadil, I use MS Data Access Application Block to handle all connections to the database and opening the database and closing it is all done within in the most effiecient manner take a look athttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp for more info.