|
xxx
DataTable to Read Database
by Charles Carroll
This page demonstrates the capabilities
to read data from datatables.
This example uses SQL7/2000 data
sources.
filename=/experiments/datatable/datatable.aspx
<%@ trace="true" %>
<%@ import namespace = "system.data.sqlclient" %>
<%@ import namespace = "system.data" %>
<%@ 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='CA'"
dim strConn as string=AppSettings("LearnaspSamples")
dim dt1 as new datatable("temp")
dim conn as new SQLconnection(strConn)
Dim adapter As New SqlDataAdapter(strSQL,Conn)
adapter.Fill(dt1)
DIM fieldPubidPos as integer=0
DIM fieldNamePos as integer=1
DIM fieldCityPos as integer=2
DIM fieldStatePos as integer=3
DIM fieldZipPos as integer=4
dim fieldpubid,fieldname,fieldcity, fieldstate, fieldzip as string
dim sb as new stringbuilder
Dim datarow1 As DataRow
If dt1.rows.count=0 THEN
sb.append("No data for " & strSQL)
output.text=sb.tostring()
exit sub
END IF
TRY
FOR EACH datarow1 IN dt1.Rows
' alternative code if fields have null values
' fieldpubid=Nullfix(datarow1,fieldpubidPos,"null")
' fieldname=Nullfix(datarow1,fieldnamePos,"null")
' fieldcity=Nullfix(datarow1,fieldcityPos,"null")
' fieldstate=Nullfix(datarow1,fieldstatePos,"null")
' fieldzip=Nullfix(datarow1,fieldzipPos,"null")
fieldpubid=datarow1(fieldpubidPos)
fieldname=datarow1(fieldnamePos)
fieldcity=datarow1(fieldcityPos)
fieldstate=datarow1(fieldstatePos)
fieldzip=datarow1(fieldzipPos)
' Output data to browser
with sb
.append ("pubid=")
.append (fieldpubid)
.append ("<br>")
.append ("name=")
.append (fieldname)
.append ("<br>")
.append ("city=")
.append (fieldcity)
.append ("<br>")
.append ("state=")
.append (fieldstate)
.append ("<br>")
.append ("zip=")
.append (fieldzip)
.append ("<br>")
.append ("<hr>")
end with
NEXT
CATCH ex as exception
errormsg.text=errormsg.text & "<br>Error with DataTable!<br>" & strSQL & "<br>" & ex.tostring() & "<br>"
CATCH sqlex as SqlException
errormsg.text=errormsg.text & "<br>Error with DataTable!<br>" & strSQL & "<br>" & sqlex.tostring() & "<br>"
FINALLY
conn.close()
END TRY
output.text=sb.tostring()
end sub
function NullFix(p1 as datarow,p2 as integer,p3 as string) as string
If p1.isnull(p2) THEN
return(p3)
ELSE
return(p1(p2))
END IF
end function
</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>
|