E:\web\learnaspcom\htdocs\freebook\learn\ubtoc.xml LearnAsp.com - ASP ASP.net Free Lessons
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>
xxx

Using SQLReader to Read Data by Charles Carroll

Here is code to read data from SQL7/2000 database with data-reader.

   filename=/experiments/datareader/datareader.aspx

<Test Script Below>

 

<%@ trace="false"%>
<%@ import namespace = "system.data.sqlclient" %> 
<%@ Import Namespace="System.Configuration.ConfigurationSettings" %> 
<script language="VB" runat="server"> 
Sub Page_Load(Src As Object, E As EventArgs)
    errormsg.text=""
    If ispostback=true THEN
        ' Page is nth interaction
    ELSE
        ' Page is first timer
    END IF
    

    dim strSQL as string="select pubid,name,city,state,zip from publishers where state='NY'"
    dim strConn as string=AppSettings("LearnaspSamples")
    dim conn as new SQLconnection(strConn)
    dim cmd as new sqlcommand(strSQL,Conn)

    ' Create a DataReader
    DIM reader1 as SQLdatareader
    Conn.Open()
    reader1=cmd.executereader()    

    ' Variables to Gather Data
    dim field0, field1 as string
    dim fieldcity, fieldstate, fieldzip as string
    dim sb as new stringbuilder
    DIM fieldCityPos as integer=reader1.getordinal("city")
    DIM fieldStatePos as integer=reader1.getordinal("state")
    DIM fieldZipPos as integer=reader1.getordinal("zip")
    
    TRY
        'trace.write ("reader", "reader1.read()" & reader1.read())
        If NOT reader1.read() THEN
            output.text="No Records found!"
            exit sub
        END IF
    
        DO 
            ' grab the fields
            TRY
                field0=reader1.getvalue(0)
                field1=reader1.getvalue(1)
                fieldcity=reader1.getvalue(fieldCityPos)
                fieldstate=reader1.getvalue(fieldStatePos)
                fieldzip=reader1.getvalue(fieldZipPos)
            CATCH theExc as exception
                errormsg.text=errormsg.text & "<br>Error with Reader!<br>" & strSQL & "<br>" & theExc.tostring() & "<br>"
            FINALLY
                ' nothing to do
            END TRY
            
            ' Output data to brower
            with sb
                .append ("field0=")
                .append (field0)
                .append ("<br>")            

                .append ("field1=")
                .append (field1)
                .append ("<br>")

                sb.append ("city=")
                sb.append (fieldcity)
                sb.append ("<br>")
                
                sb.append ("<hr>")
            end with

        LOOP WHILE reader1.Read()
    CATCH ex as exception
        errormsg.text=errormsg.text & "<br>Error with Reader!<br>" & strSQL & "<br>" & ex.tostring() & "<br>"
    CATCH sqlex as SqlException
        errormsg.text=errormsg.text & "<br>Error with Reader!<br>" & strSQL & "<br>" & sqlex.tostring() & "<br>"
    FINALLY
        reader1.close()
        conn.close()
    END TRY
    
    output.text=sb.tostring()
end sub

</script> 
<html><head> 
<title><asp:literal id="titlequery" runat="server" /></title> 
</head> 
<body bgcolor="#FFFFFF"> 
<asp:literal id="errormsg" runat="server" /><br>
<form runat="server">
<asp:literal id="output" runat="server" />
</form>
</body><html>

Here is simpler but slower code to read data from SQL7/2000 database with data-reader using .item method

   filename=/experiments/datareader/datareaderitem.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)
    errormsg.text=""
    If ispostback=true THEN
        ' Page is nth interaction
    ELSE
        ' Page is first timer
    END IF
    

    dim strSQL as string="select pubid,name,city,state,zip from publishers where state='NY'"
    dim strConn as string=AppSettings("LearnaspSamples")
    dim conn as new SQLconnection(strConn)
    dim cmd as new sqlcommand(strSQL,Conn)

    ' Create a DataReader
    DIM reader1 as SQLdatareader
    Conn.Open()
    reader1=cmd.executereader()    

    ' Variables to Gather Data
    dim field0, field1 as string
    dim fieldcity, fieldstate, fieldzip as string
    dim sb as new stringbuilder

    TRY
    
        If NOT reader1.read() THEN
                output.text="No Records found!"
                exit sub
        END IF
        
        DO 
            ' grab the fields
            TRY
                field0=reader1.item(0)
                field1=reader1.item(1)
                fieldcity=reader1.item("city")
                fieldstate=reader1.item("state")
                fieldzip=reader1.item("zip")
            CATCH theExc as exception
                errormsg.text=errormsg.text & "<br>Error with Reader!<br>" & strSQL & "<br>" & theExc.tostring() & "<br>"
            FINALLY
                ' nothing to do
            END TRY
            
            ' Output data to brower
            with sb
                .append ("field0=")
                .append (field0)
                .append ("<br>")            

                .append ("field1=")
                .append (field1)
                .append ("<br>")

                sb.append ("city=")
                sb.append (fieldcity)
                sb.append ("<br>")
                
                sb.append ("<hr>")
            end with

        LOOP WHILE reader1.Read()
    CATCH ex as exception
        errormsg.text=errormsg.text & "<br>Error with Reader!<br>" & strSQL & "<br>" & ex.tostring() & "<br>"
    CATCH sqlex as SqlException
        errormsg.text=errormsg.text & "<br>Error with Reader!<br>" & strSQL & "<br>" & sqlex.tostring() & "<br>"
    FINALLY
        reader1.close()
        conn.close()
    END TRY
    
    output.text=sb.tostring()
end sub

</script> 
<html><head> 
<title><asp:literal id="titlequery" runat="server" /></title> 
</head> 
<body bgcolor="#FFFFFF"> 
<asp:literal id="errormsg" runat="server" /><br>
<form runat="server">
<asp:literal id="output" runat="server" />
</form>
</body><html>
Chaz Wish List
Tall Tip $5
Grande Tip $20
Venti Tip $39
Tip Jar Thanks
2004 Thanks
2005 Thanks
HUGE Tip -love site