Using a dataset frequently where more efficient ways exist is taxing AND TIES UP THE database LONGER to access which is very bad at handling simultaneous use (65 users at any given millisecond is the max, the 66th user would get such an error).
STOP STOP STOP using Binding code like =>
(lousy SLOW way that really eats resources, but all over the MS docs despiet it being discouraged by the inventors of ASP.net since Beta2 introduced executereader. Also since at lacks error trapping/recovery IT WILL orphan connections if the 66th user onward gets denied database access)
Dim DS As DataSet
Dim MyConnection As OLEDBConnection
Dim MyCommand As OLEDBDataAdapter
MyConnection = New OLEDBConnection("....")
MyCommand = New OLEDBDataAdapter("select * from publishers where state='NY'", MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, "NYStateOfMind")
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
MYConnection.Close()
needs to be replaced with => Executereader
This is MUCH MUCH FASTER than Dataset fills and Bind; substantially lighter "in memory"/JITing footprint. Also the robust error trapping absolutely assures no orphaned connections when too many simultaneous connections are made on busy website.
Dim Conn as OLEDBConnection
Dim Cmd as OLEDBCommand
Dim Rdr as OLEDBDataReader
TRY
Dim strConn as string ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("/whatever/xxx.mdb") & ";"
Dim strSQL as string ="select * from publishers where state='NY'"
Conn=New OLEDBConnection(strConn)
Cmd=New OLEDBCommand(strSQL,Conn)
Conn.Open()
Rdr=Cmd.ExecuteReader()
myDataGrid.DataSource = Rdr
myDataGrid.DataBind()
CATCH ex1 as exception
trace.warn("SUB whatever",stSQL,ex1)
FINALLY
Rdr.close()
Conn.close()
END TRY
If you don't want to write that code again and again and want more tested perfect robust code with very pretty error trapping (and can even email you runtime errors), just use my "Utility Belt" library which does all that with one SUB called DBPopulate, i.e.
<%@ Assembly src="utilitybelt.vb" %>
<script language="VB" runat="server">
dim ub1 as new utilitybelt()
dim strConnect as string
Sub Page_Load(S As Object, E As EventArgs)
ub1.plcexception=plcMyErrorsMsgsHere
strConnect=utlty1.DBAccess2Oledb("/whatever/xxx.mdb")
ub1.DBPopulate(strConnect,"select * from publishers where state='NY'",MyDataGrid)
.....
<body bgcolor="#FFFFFF">
<asp:PlaceHolder id="plcMyErrorsMsgsHere" runat="server" />
http://www.learnasp.com/freebook/learn/utilitybelt_lib.aspx
has all you need to include.
http://www.learnasp.com/freebook/learn/utilitybelt_docs.aspx
Utility Belt has a dozen vital functions with state of the art error trapping including the ability to email runtime errors to the programmer so the user is not the only one that sees a runtime error). It also supports caching so that a busy database ca display results without re-opening the database constantly.
Keep in mind you can use Executereader to fill, BUT still use DataAdapters to read and change dataset and update source. Just don't use the datasets on the fill part.