Note I can send this in a zip file if you supply me with an email. most
of
the info like table schema and images did not made it. Sorry
I created a project that has an embedded SQl table with the scenario you
described
Database: DavesDataase.mdf
Tables: 1 - SalesSumm
2 - Yeartable
3 - Qttable
4 - Nametable
SaleSumm
col name
data type
year
nchar(4)
salesname
nvarchar(50)
Q1
SMALLINT
Q2
SMALLINT
Then other 3 table are design to denormalize the first
Yeartable
Column Name
Data type
col1
nvarchar(250)
year 1
col2
nvarchar(250)
year 2
col3
nvarchar(250)
col4
nvarchar(250)
col5
nvarchar(250)
col6
nvarchar(250)
col7
nvarchar(250)
seq
smallint
Qttable
Column Name
Data type
col1
nvarchar(250)
col2
nvarchar(250)
col3
nvarchar(250)
1q1
col4
nvarchar(250)
1q2
col5
nvarchar(250)
2q1
col6
nvarchar(250)
2q2
col7
nvarchar(250)
seq
smallint
Nametable
Column Name
Data type
col1
nvarchar(250)
col2
nvarchar(250)
col3
nvarchar(250)
col4
nvarchar(250)
col5
nvarchar(250)
col6
nvarchar(250)
col7
nvarchar(250)
name
seq
smallint
================================================================================================
Class module: DavesDataAccess.vb
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class DavesDataAccess
Public Function AReallyBigSql() As Data.DataTable
TruncateTables()
Dim sql As String = _
"declare @YearOne nvarchar(4) " & _
"declare @YearTwo nvarchar(4) " & _
"declare @SalesName nvarchar(50) " & _
"declare @1Q1 nvarchar(4) " & _
"declare @1Q2 nvarchar(4) " & _
"declare @2Q1 nvarchar(4) " & _
"declare @2Q2 nvarchar(4) " & _
" " & _
"select top 1 @YearOne = cast([year] as nvarchar) from
SalesSumm
" & _
"select top 1 @YearTwo = cast ([year] as nvarchar) from
SalesSumm where year <> @YearOne " & _
"select top 1 @SalesName = salesName from salesSumm " & _
"select top 1 @1Q1 = cast (q1 as nvarchar(2)) from salessumm "
& _
"select top 1 @1Q2 = cast(q2 as nvarchar(2)) from salessumm " &
_
"select top 1 @2Q1 = cast(q1 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
"select top 1 @2Q2 = cast(q2 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
" " & _
"insert into YearTable (seq, col1, col2) values(1, @YearOne,
@YearTwo) " & _
"insert into QtTable (seq,col3,col4,col5,col6) values(2, @1q1,
@1q2,@2q1, @2q2) " & _
"insert into NameTable (seq, col7) values(3, @salesName) " & _
" " & _
"select * from YearTable " & _
"union " & _
"select * from qttable " & _
"union " & _
"select * from NameTable" & _
" order by seq"
Dim cn As New SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\DevCenter\WebSites\DisplayOnItsSide\App_Data\DavesDatabase.mdf;Integrated
Security=True;User Instance=True")
cn.Open()
Dim da As New SqlDataAdapter(sql, cn)
Dim dt As New Data.DataTable
da.Fill(dt)
cn.Close()
Return dt
End Function
Public Sub TruncateTables()
Dim sql As String = _
"delete from nameTable"
Dim cn As New SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\DevCenter\WebSites\DisplayOnItsSide\App_Data\DavesDatabase.mdf;Integrated
Security=True;User Instance=True")
cn.Open()
Dim cmd As New SqlCommand(sql, cn)
cmd.ExecuteNonQuery()
sql = "delete from YearTable"
cmd.CommandText = sql
cmd.ExecuteNonQuery()
sql = "delete from qttable"
cmd.CommandText = sql
cmd.ExecuteNonQuery()
cn.Close()
End Sub
End Class
================================================================================================
Page - Default.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"
Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp
ataList ID="DataList1" runat="server" Height="495px"
Width="288px">
<ItemTemplate>
<table class="style1">
<tr>
<td align="center" colspan="2">
<asp:Label ID="lblYearOne" runat="server"
Text='<%# Eval("col1") %>'></asp:Label>
</td>
<td align="center" colspan="2">
<asp:Label ID="lblYearTwo" runat="server"
Text='<%# Eval("col2") %>'></asp:Label>
</td>
</tr>
<tr>
<td align="center">
<asp:Label ID="lbl1Q1" runat="server" Text='<%#
Eval("col3") %>'></asp:Label>
</td>
<td align="center">
<asp:Label ID="lbl1Q2" runat="server" Text='<%#
Eval("col4") %>'></asp:Label>
</td>
<td align="center">
<asp:Label ID="lbl2Q1" runat="server" Text='<%#
Eval("col5") %>'></asp:Label>
</td>
<td align="center">
<asp:Label ID="lbl2Q2" runat="server" Text='<%#
Eval("col6") %>'></asp:Label>
</td>
</tr>
<tr>
<td colspan="4">
<asp:Label ID="lblName" runat="server"
Text='<%#
Eval("col7") %>'></asp:Label>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
</ItemTemplate>
</asp
ataList>
</div>
</form>
</body>
</html>
Code behind
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
BindDataList()
End Sub
Private Sub BindDataList()
Dim da As New DavesDataAccess
Dim dt As Data.DataTable = da.AReallyBigSql
DataList1.DataSource = dt
DataList1.DataBind()
End Sub
End Class
===========================================================================================
A second example
Page: Repeater.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Repeater.aspx.vb"
Inherits="Repeater" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<asp:Label ID="lblYrOne" runat="server" Text='<%#
Eval("col1") %>'></asp:Label>
<asp:Label ID="lblYrTwo" runat="server" Text='<%#
Eval("col2") %>'></asp:Label>
<br />
<asp:Label ID="lbl1Q1" runat="server" Text='<%#
Eval("col3")
%>'></asp:Label>
<asp:Label ID="lbl1Q2" runat="server" Text='<%#
Eval("col4")
%>'></asp:Label>
<asp:Label ID="lbl2Q1" runat="server" Text='<%#
Eval("col5")
%>'></asp:Label>
<asp:Label ID="lbl2Q2" runat="server" Text='<%#
Eval("col6")
%>'></asp:Label>
<br />
<asp:Label ID="lblName" runat="server" Text='<%#
Eval("col7") %>'></asp:Label>
</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>
Code behind
Partial Class Repeater
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
BindDataList()
End Sub
Private Sub BindDataList()
Dim da As New DavesDataAccess
Dim dt As Data.DataTable = da.AReallyBigSql
Repeater1.DataSource = dt
Repeater1.DataBind()
End Sub
End Class
So what it does if you look at dataaccess module
"declare @YearOne nvarchar(4) " & _
"declare @YearTwo nvarchar(4) " & _
"declare @SalesName nvarchar(50) " & _
"declare @1Q1 nvarchar(4) " & _
"declare @1Q2 nvarchar(4) " & _
"declare @2Q1 nvarchar(4) " & _
"declare @2Q2 nvarchar(4) " & _
" " & _
"select top 1 @YearOne = cast([year] as nvarchar) from
SalesSumm
" & _
"select top 1 @YearTwo = cast ([year] as nvarchar) from
SalesSumm where year <> @YearOne " & _
"select top 1 @SalesName = salesName from salesSumm " & _
"select top 1 @1Q1 = cast (q1 as nvarchar(2)) from salessumm "
& _
"select top 1 @1Q2 = cast(q2 as nvarchar(2)) from salessumm " &
_
"select top 1 @2Q1 = cast(q1 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
"select top 1 @2Q2 = cast(q2 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
" " & _
"insert into YearTable (seq, col1, col2) values(1, @YearOne,
@YearTwo) " & _
"insert into QtTable (seq,col3,col4,col5,col6) values(2, @1q1,
@1q2,@2q1, @2q2) " & _
"insert into NameTable (seq, col7) values(3, @salesName) " & _
" " & _
"select * from YearTable " & _
"union " & _
"select * from qttable " & _
"union " & _
"select * from NameTable" & _
" order by seq"
is create a temp output using a union to join three sql queries making
sure
the rows are in the right order using the order by seq.
The rest of the magic is in the databinding which you can look at in the
aspx stuff.
I did test this and it does give
Untitled Pagehttp://localhost:2248/DisplayOnItsSide/Repeater.aspx
Screen clipping taken: 2/17/2008, 12:19 PM
Hope this help. If you want the entire project I can zip it and email to
a
point of preference.
--
aaa
Dave said:
Yeah that's the point I'm at. I have my data pivoted in SQL, I created a
datatable in code, but getting it to show on the GUI side by side isn't
happening very easy or at all.
