Advice Needed: Where to put ADO Code

I need some advice on a project that I am working on...

First, here is what I am trying to achieve: A Web Form with two controls: A DropDownList with two items added at design time (Fruits and Vegetables) and an empty ListBox. When the user chooses a "category" from the DropDownList, the ListBox will be populated with a list of either "Fruits" or "Vegetables" retrieved from a SQL database. (Note: Since the data in the SQL database must be converted and formatted programatically, simply databinding the ListBox will not work here.)

I believe that I can do this with the following code (stolen from an MSDN article):

'Create ADO.NET objects.Private myConnAs SqlConnectionPrivate myCmdAs SqlCommandPrivate myReaderAs SqlDataReaderPrivate resultsAs String'Create a Connection object. myConn =New SqlConnection("Initial Catalog=Northwind;" & _"Data Source=localhost;Integrated Security=SSPI;")'Create a Command object. myCmd = myConn.CreateCommand myCmd.CommandText ="SELECT FirstName, LastName FROM Employees"'Open the connection. myConn.Open() myReader = myCmd.ExecuteReader()'Concatenate the query result into a string.Do While myReader.Read() results = results & myReader.GetString(0) & vbTab & _ myReader.GetString(1) & vbLfLoop'Display results. MsgBox(results)'Close the reader and the database connection. myReader.Close() myConn.Close()

Now here is the part that I am not sure about: Is the FormLoad event the best place to put this code? If I do, is this not a lot of overhead (creating, opening and closing a connection) everytime there is a page refresh/PostBack? Would I be better off putting this code in the DropDownList SelectedIndexChanged event? Although that seems like it could make the process of selecting a category take a fairly long time.

Finally, if the is a better way of doing this, I am certainly open to suggestions.

All advice is greatly appreciated.

hello,

you may like to read "ASP.Net Tutorial - If Not Page.IsPostBack" article at,

http://aspnet101.com/aspnet101/tutorials.aspx?id=3

ALSO,

i'd like to suggest you to read this too,

1) "Examining the Data Access Application Block" at,

http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

2) "Working with the Enterprise Library's Data Access Application Block" at ,

http://aspnet.4guysfromrolla.com/articles/030905-1.aspx

regards,

Niraj sikotara.