|
xxx
Flex Database Basics
by Charles Carroll
This database design example shows
the basic of supporting multi-value fields and ad-hoc fields when structuring a
database and how fields could be added without admin access to database/ability
to modify tables would be unecessary.
A sample of Database records with details!
filename=/experiments/dbflexible/PeopleShowall.aspx
<%@ debug="false" trace="false"%>
<%@ import namespace="system.data.oledb"%>
<%@ import namespace = "system.data"%>
<script language="VB" runat="server">
dim strAppConnect as string
Sub Page_Load(Src As Object, E As EventArgs)
trace.tracemode=TraceMode.SortByTime
IF ispostback=true THEN
END IF
strAppConnect="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("people.mdb;") & ";"
Call PersonShow(1)
Call PersonShow(2)
Call PersonShow(3)
END SUB
sub PersonShow(p1 as integer)
dim dtblPerson as datatable
dim dtblPersonDetails as datatable
dtblPerson=DatatableFromQuery("select * from people where keypeople=" & p1, strAppConnect)
litPerson.text &= "<font color='blue'>"
Call DataTableShow(dtblPerson,litPerson)
litPerson.text &= "</font>"
dtblPersonDetails=DetailsForMemberByID(p1)
Call DataTableShow(dtblPersonDetails,litPerson)
litPerson.text &= "<hr>"
end sub
function DatatableFromQuery(pSQL as string, pConn as string) as datatable
dim dt1 as new datatable("temp")
dim conn as new OLEDBconnection(pConn)
Dim adapter As New OLEDBDataAdapter(pSQL,Conn)
adapter.Fill(dt1)
return(dt1)
end function
sub DataTableShow(p1 as datatable,p2 as Literal)
dim dtblrow1 as datarow
If p1.rows.count=0 THEN
p2.text &= "No data!"
END IF
TRY
FOR EACH dtblrow1 IN p1.Rows
p2.text &= " <b>" & dtblrow1(0) & "</b>=" & dtblrow1(1) & "<br>"
NEXT
CATCH ex as exception
trace.warn("SUB Page_Load",ex.tostring() & "<br>Error with DataTable!")
CATCH dbex as OLEDBException
trace.warn("SUB Page_Load",dbex.tostring() & "<br>Error with DataTable!")
FINALLY
END TRY
end sub
function DetailsForMemberByID(p1 as integer) as datatable
Dim dtblTemp As DataTable
Dim dtblrowTemp As DataRow
dtblTemp = New DataTable()
dtblTemp.Columns.Add(new DataColumn("Field", GetType(String)))
dtblTemp.Columns.Add(new DataColumn("Value", GetType(String)))
dim strSQL as string
dim conn as new OLEDBconnection(strAppConnect)
strSQL="select [keyPeopleFields],[value] from PeopleValues where keyPeople=" & p1
dim cmd as new OLEDBcommand(strSQL,Conn)
' Create a DataReader
DIM reader1 as OLEDBdatareader
Conn.Open()
reader1=cmd.executereader()
TRY
'trace.write ("reader", "reader1.read()" & reader1.read())
If NOT reader1.read() THEN
return(dtblTemp)
END IF
DO
' grab the fields
TRY
dtblrowTemp = dtblTemp.NewRow()
dtblrowTemp(0) = executescalar("select [group] from PeopleFields where KeyPeopleFields=" & reader1.getvalue(0),strAppConnect) & ":" & executescalar("select [descriptor] from PeopleFields where KeyPeopleFields=" & reader1.getvalue(0),strAppConnect)
dtblrowTemp(1) = reader1.getvalue(1)
dtblTemp.Rows.Add(dtblrowTemp)
CATCH theExc as exception
trace.write("function DetailsForMember","<br>Error with Reader!<br>" & strSQL & "<br>" & theExc.tostring())
FINALLY
' nothing to do
END TRY
LOOP UNTIL reader1.Read()=false
CATCH ex as exception
trace.write("function DetailsForMember","Error with Reader!<br>" & strSQL & "<br>" & ex.tostring() & "<br>")
CATCH dbex as OLEDBException
trace.write("function DetailsForMember","Error with Reader!<br>" & strSQL & "<br>" & dbex.tostring() & "<br>")
FINALLY
Conn.close
end try
return(dtblTemp)
end function
Function ExecuteScalar(p1 as string,p2 as string)
Dim strTempReturn as string
Dim Conn as New OLEDBConnection(p2)
Dim Cmd as New OLEDBCommand(p1,Conn)
Conn.Open()
strTempReturn=cmd.executescalar()
Conn.Close()
return(strTempReturn)
End Function
</script>
<html><head>
<title>People - Flexible Database Structure</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana">
<form runat="server">
<asp:literal id="litPerson" enableviewstate="false" runat="server" />
</form></font>
</body></html>
The People Table
filename=/experiments/dbflexible/showPeople.aspx
<%@ Import Namespace="System.Data.OLEDB" %>
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim strConn as string ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("people.mdb") & ";"
Dim strSQL as string ="select * from people"
Dim Conn as New OLEDBConnection(strConn)
Dim Cmd as New OLEDBCommand(strSQL,Conn)
Conn.Open()
myDataGrid.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
myDataGrid.DataBind()
End Sub
</script>
<html><head>
<title>People</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>People</h3></font>
<ASP:DataGrid id="MyDataGrid" runat="server" />
</body></html>
The PeopleFields Table
filename=/experiments/dbflexible/showPeopleFields.aspx
<%@ Import Namespace="System.Data.OLEDB" %>
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim strConn as string ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("people.mdb") & ";"
Dim strSQL as string ="select * from PeopleFields"
Dim Conn as New OLEDBConnection(strConn)
Dim Cmd as New OLEDBCommand(strSQL,Conn)
Conn.Open()
myDataGrid.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
myDataGrid.DataBind()
End Sub
</script>
<html><head>
<title>PeopleFields</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>PeopleFields</h3></font>
<ASP:DataGrid id="MyDataGrid" runat="server" />
</body></html>
The PeopleValues table
filename=/experiments/dbflexible/showPeopleValues.aspx
<%@ Import Namespace="System.Data.OLEDB" %>
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim strConn as string ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("people.mdb") & ";"
Dim strSQL as string ="select * from PeopleValues"
Dim Conn as New OLEDBConnection(strConn)
Dim Cmd as New OLEDBCommand(strSQL,Conn)
Conn.Open()
myDataGrid.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
myDataGrid.DataBind()
End Sub
</script>
<html><head>
<title>PeopleValues</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>PeopleValues</h3></font>
<ASP:DataGrid id="MyDataGrid" runat="server" />
</body></html>
 |  |  |
|
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.
| |
 | |  |
|
|
|
|