Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll 		<Asp.net blog>
<personal site>
[prev. Lesson]  VB.net - DataGrid Display of Access or any OLEB Database
     [next Lesson]  VB.net - Typical Search by CC

Easy Grid Display from SQL7/2000 bypassing OLEDB
by Charles Carroll

This page demonstrates the capabilities how to display a SQL7/2000 database as a easy to read grid.

   filename=/experiments/databinding/sqlclientgrid.aspx

<Test Script Below>

 

<%@ Import Namespace="System.Data.sqlclient" %>
<%@ Import Namespace="System.Configuration.ConfigurationSettings" %>
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
    Dim Conn as SQLConnection
    Dim Rdr as SQLDatareader
    TRY
        Dim strConn as string =AppSettings("LearnaspSamples")
        Dim strSQL as string ="select * from publishers where state='NY'"
        Conn=New SQLConnection(strConn)
        Dim Cmd as New SQLCommand(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>SQLClient Data</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>SQLClient Data</h3></font>
<asp:literal id="litexc" EnableViewState="false" runat="server" />
<ASP:DataGrid id="MyDataGrid" EnableViewState="false" runat="server"
    Width="100%" BackColor="white" BorderColor="black" ShowFooter="false" 
    CellPadding=3 CellSpacing="0"
    Font-Name="Verdana" Font-Size="8pt"
    Headerstyle-BackColor="lightblue" Headerstyle-Font-Size="10pt" Headerstyle-Font-Style="bold" />
</body></html>

This assumes you have connection information stored in your web.config ala

<?xml version="1.0"?>
<configuration>
	<appSettings>
		<add key="LearnaspSamples" value="server=206.xxx.xxx.xxx;Initial Catalog=Learnasp;UID=xxxx;Pwd=xxxx;application Name={0}" />
		<add key="LearnaspSamplesFull" value="server=206.xxx.xxx.xxx;Initial Catalog=Learnasp;UID=xxxx;Password=xxxx;application Name={0}" />
	</appSettings>
</configuration>

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/databinding/sqlclientgrid_ub.aspx

<Test Script Below>

 

<%@ Assembly src="/experiments/utilitybelt/vercurrent/utilitybelt.vb" %>
<script language="VB" runat="server">
    dim ub1 as new learnasp.utilitybelt()
    dim strConnect as string="LearnAspSamples"
Sub Page_Load(S As Object, E As EventArgs)
    ub1.DBPopulate(strConnect,"select * from publishers where state='NY'",MyDataGrid)
End Sub
</script>
<html><head>
<title>Grid of New York Data</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>New York Data</h3></font>
<ASP:PlaceHolder id="plcErr" runat="server" />
<ASP:DataGrid id="MyDataGrid" runat="server"
    Width="100%"
    BackColor="white" 
    BorderColor="black"
    ShowFooter="false" 
    CellPadding=3 
    CellSpacing="0"
    Font-Name="Verdana"
    Font-Size="8pt"
    Headerstyle-BackColor="lightblue"
    Headerstyle-Font-Size="10pt"
    Headerstyle-Font-Style="bold"
    MaintainState="false"
/>
</body></html>

Additional formatting besides tables is readily available. Additional Reading:

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. See http://www.aspng.com/learn/bopen.aspx for similar sample.

  • 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.
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.