|
Easy Grid Display from
Access/OLEDB Databases
by Charles Carroll
This page demonstrates the capabilities how to
display an access database or any OLEDB database as a easy to read grid. Notice
unlike Classic ASP there is no LOOPs, Movenexts or HTML <tr>, <td>s
to deal with. Here is a code sample connecting to an access database:
filename=/experiments/databinding/oledbgrid.aspx
<%@ Import Namespace="System.Data.OLEDB" %>
<script language="VB" runat="server">
Sub Page_Load(S As Object, E As EventArgs)
Dim Conn as OLEDBConnection
Dim Rdr as OLEDBDataReader
TRY
Dim strConn as string ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strConn &= server.mappath("/experiments/data/biblio.mdb") & ";"
' Download the relevant database from http://www.learnasp.com/biblio
' and save on C:\inetpub\wwwroot\biblio.mdb
' change the line above to become
' strConn &= server.mappath("\biblio.mdb") & ";"
Dim strSQL as string
strSQL="select * from publishers where state='NY'"
Conn=New OLEDBConnection(strConn)
Dim Cmd as New OLEDBCommand(strSQL,Conn)
Conn.Open()
Rdr=Cmd.ExecuteReader()
myDataGrid.DataSource = Rdr
myDataGrid.DataBind()
CATCH exc1 as exception
litExc.text=exc1.ToString()
FINALLY
IF Not(rdr Is Nothing)
IF rdr.IsClosed=false THEN Rdr.Close()
End If
If not(conn is Nothing)
IF Conn.State=System.Data.Connectionstate.Open THEN Conn.Close()
End If
END TRY
End Sub
</script>
<html><head>
<title>OLEDB Database Example</title>
</head>
<body bgcolor="#FFFFFF">
<form runat="server">
<font face="Verdana"><h3>OLEDB Database Example</h3></font>
<asp:literal id="litExc" runat="server" />
<ASP:DataGrid id="MyDataGrid" runat="server"/>
</form></body></html>
Here is a page that reduces the code and has improved error trapping utilizing the UtilityBelt Library available for FREE from our site:
filename=/experiments/utilitybelt/vercurrent/ubdemo_bind_oledb.aspx
<%@ debug="true" %>
<%@ Assembly src="utilitybelt.vb" %>
<%@ Import Namespace="LearnAsp.UtilityBelt"%>
<script language="VB" runat="server">
dim strConnect as string="\experiments\data\biblio.mdb"
dim ub1 as new learnasp.utilitybelt()
dim strSQL as string
sub Page_Load(S As Object, E As EventArgs)
ub1.Options("Debug-on")
strSQL="select * from publishers where state='NY'"
ub1.DBPopulate(strConnect,strSQL,grdSample)
End Sub
</script>
<html><head>
<title>Utility Belt Demo</title></head>
<body bgcolor="#FFFFFF"><form runat="server">
<asp:datagrid id="grdSample" EnableviewState="false" runat="server" />
</form></body></html>
As a side note, ASP.net has two
assemblies/namespaces (see http://www.aspng.com/learn/assemblynamespace.aspx)
to talk to databases:
system.data.oledb - OLEDBconnection,
OLEDBDataSetCommands to talk to any OLEDB database and SQLserver 6.5
system.data.sqlclient -
SQLconnection, SQLDataSetCommands to talk to SQL2000 and SQL7 directly very fast without
OLEDB as middle layer. Use the high speed system.data.sqlclient
when back-end is SQL7 or SQL2000 only. Grid display using SQLclient is at http://www.aspng.com/learn/dbopensqlserver.aspx
Microsoft also supports system.data.odbc for
ODBC drivers. Get it @
http://www.microsoft.com/downloads/release.asp?ReleaseID=31125
HUGE TIP: every possible connection string @
http://www.connectionstrings.com/
Read
more:
|