Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > visual basic .net > answers

Columns and Values (Urgent)

scorpion53061
Guest
  
Posts: n/a
#1: Nov 19 '05
Very urgent and I am very close but need a little help to get me over the
edge........

I need to write these columns to a html file with each row containing these
columns (seperated by breaks). Currently this code write all columns to a
single row which works but is not what the boss wants. If anyone has
suggestions how you would do a report like this I very much would like to
hear them as well. vb.net windows app.

sw.WriteLine("<html>")
sw.WriteLine("<head>")
sw.WriteLine("</head>")
sw.WriteLine("<body>")
Dim i, r, c As Integer
For i = 0 To dshistory1.Tables.Count - 1
sw.WriteLine("<center>")
sw.WriteLine("<table border=1 width='100%'
style='border-collapse: collapse' cellspacing='3'
bordercolorlight='#000000'>")
sw.WriteLine("<tr>")
sw.WriteLine("<b>")
For c = 0 To dshistory1.Tables(i).Columns.Count - 1
sw.Write("<td>{0}</td>",
dshistory1.Tables(i).Columns(c).ColumnName)
Next
sw.WriteLine("</b>")
sw.WriteLine("</td>")
sw.WriteLine("</tr>")
For r = 0 To dshistory1.Tables(i).Rows.Count - 1

sw.WriteLine("<tr>")
For c = 0 To dshistory1.Tables(i).Columns.Count - 1
sw.Write("<td>{0}</td>",
dshistory1.Tables(i).Rows(r).Item(c))
Next
sw.WriteLine("</tr>")
Next

sw.WriteLine("</table>")
sw.WriteLine("</center>")
'sw.WriteLine("</hr>")
Next
sw.WriteLine("</body>")
sw.WriteLine("</html>")
sw.Close()

What I am trying to do is this(and make it all fit in a landscape print
situation:)

Row 1 is the title ("Sales Report" - not in loop)

(Row 1)
CUSTNO CUSTNAME SLSMNO
(Row 2 is the values in the columns above)

(Row 3)
LYTMSALES LYTDSALES DECSALES NOVSALES OCTSALES SEPTSALES AUGSALES JULSALES
JUNSALES MAYSALES APRSALES MARSALES FEBSALES JANSALES
(row 4 is the values in the columns above)

etc on and so forth with the profit, orders, arbal, and GM columns.

These are the names of the columns in the table.

<xs:element name="CUSTNO" type="xs:int" minOccurs="0" />
<xs:element name="CUSTNAME" type="xs:string" minOccurs="0" />
<xs:element name="SLSMN" type="xs:int" minOccurs="0" />
<xs:element name="LYTMSALES" type="xs:double" minOccurs="0" />
<xs:element name="LYTMPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="LYTMGM" type="xs:double" minOccurs="0" />
<xs:element name="LYTMORDERS" type="xs:int" minOccurs="0" />
<xs:element name="LYTMARBAL" type="xs:double" minOccurs="0" />
<xs:element name="LYTDSALES" type="xs:double" minOccurs="0" />
<xs:element name="LYTDPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="LYTDGM" type="xs:double" minOccurs="0" />
<xs:element name="LYTDORDERS" type="xs:int" minOccurs="0" />
<xs:element name="LYTDARBAL" type="xs:double" minOccurs="0" />
<xs:element name="JANSALES" type="xs:double" minOccurs="0" />
<xs:element name="JANGM" type="xs:double" minOccurs="0" />
<xs:element name="JANPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="JANORDERS" type="xs:int" minOccurs="0" />
<xs:element name="JANARBAL" type="xs:double" minOccurs="0" />
<xs:element name="FEBSALES" type="xs:double" minOccurs="0" />
<xs:element name="FEBPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="FEBGM" type="xs:double" minOccurs="0" />
<xs:element name="FEBORDERS" type="xs:int" minOccurs="0" />
<xs:element name="FEBARBAL" type="xs:double" minOccurs="0" />
<xs:element name="MARSALES" type="xs:double" minOccurs="0" />
<xs:element name="MARPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="MARGM" type="xs:double" minOccurs="0" />
<xs:element name="MARORDERS" type="xs:int" minOccurs="0" />
<xs:element name="MARARBAL" type="xs:double" minOccurs="0" />
<xs:element name="APRSALES" type="xs:double" minOccurs="0" />
<xs:element name="APRPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="APRGM" type="xs:double" minOccurs="0" />
<xs:element name="APRORDERS" type="xs:int" minOccurs="0" />
<xs:element name="APRARBAL" type="xs:double" minOccurs="0" />
<xs:element name="MAYSALES" type="xs:double" minOccurs="0" />
<xs:element name="MAYPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="MAYGM" type="xs:double" minOccurs="0" />
<xs:element name="MAYORDERS" type="xs:int" minOccurs="0" />
<xs:element name="MAYARBAL" type="xs:double" minOccurs="0" />
<xs:element name="JUNESALES" type="xs:double" minOccurs="0" />
<xs:element name="JUNEPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="JUNEGM" type="xs:double" minOccurs="0" />
<xs:element name="JUNEORDERS" type="xs:int" minOccurs="0" />
<xs:element name="JUNEARBAL" type="xs:double" minOccurs="0" />
<xs:element name="JULYSALES" type="xs:double" minOccurs="0" />
<xs:element name="JULYPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="JULYGM" type="xs:double" minOccurs="0" />
<xs:element name="JULYORDERS" type="xs:int" minOccurs="0" />
<xs:element name="JULYARBAL" type="xs:double" minOccurs="0" />
<xs:element name="AUGSALES" type="xs:double" minOccurs="0" />
<xs:element name="AUGPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="AUGGM" type="xs:double" minOccurs="0" />
<xs:element name="AUGORDERS" type="xs:int" minOccurs="0" />
<xs:element name="AUGARBAL" type="xs:double" minOccurs="0" />
<xs:element name="SEPTSALES" type="xs:double" minOccurs="0" />
<xs:element name="SEPTPROFIT" type="xs:double" minOccurs="0"
/>
<xs:element name="SEPTGM" type="xs:double" minOccurs="0" />
<xs:element name="SEPTORDERS" type="xs:int" minOccurs="0" />
<xs:element name="SEPTARBAL" type="xs:double" minOccurs="0" />
<xs:element name="OCTSALES" type="xs:double" minOccurs="0" />
<xs:element name="OCTPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="OCTGM" type="xs:double" minOccurs="0" />
<xs:element name="OCTORDERS" type="xs:int" minOccurs="0" />
<xs:element name="OCTARBAL" type="xs:double" minOccurs="0" />
<xs:element name="NOVSALES" type="xs:double" minOccurs="0" />
<xs:element name="NOVPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="NOVGM" type="xs:double" minOccurs="0" />
<xs:element name="NOVORDERS" type="xs:int" minOccurs="0" />
<xs:element name="NOVARBAL" type="xs:double" minOccurs="0" />
<xs:element name="DECSALES" type="xs:double" minOccurs="0" />
<xs:element name="DECPROFIT" type="xs:double" minOccurs="0" />
<xs:element name="DECGM" type="xs:double" minOccurs="0" />
<xs:element name="DECORDERS" type="xs:int" minOccurs="0" />
<xs:element name="DECARBAL" type="xs:double" minOccurs="0" />

Cor
Guest
  
Posts: n/a
#2: Nov 19 '05

re: Columns and Values (Urgent)

Scorpion,
Although you send a lot of information, I have to asume a lot.
I asume you make a file on a media like a disk, that you read with a
browser, a text editor or something like that.

In the code I see one little error, but as far as I know does that mostly
affects nothing (the bold tags are to not fine placed)
sw.WriteLine("<tr>")
sw.WriteLine("<b>")
For c = 0 To dshistory1.Tables(i).Columns.Count - 1
sw.Write("<td>{0}</td>",
dshistory1.Tables(i).Columns(c).ColumnName)
Next
sw.WriteLine("</b>")
-----> sw.WriteLine("</td>") ' is not opened
sw.WriteLine("</tr>")

Maybe if you give some more information I or someone else can help you.
Cor
Cor
Guest
  
Posts: n/a
#3: Nov 19 '05

re: Columns and Values (Urgent)

Scorpion,
I write is not opened, but I mean I think it has not to be closed
Cor
Jay B. Harlow [MVP - Outlook]
Guest
  
Posts: n/a
#4: Nov 19 '05

re: Columns and Values (Urgent)

Scorpion,
I don't see any real problems. I can run your code 'as is' and the output
appears mostly as you describe it. When the HTML is viewed in design mode or
the browser, in source view there is only one line, but 'who cares' is html
source. (if you care, then change the Write to WriteLine...)

Maybe I am reading your description wrong.

Do you want:

c1 c2 c3 c4
v1 v2 v3 v4

Or do you want:

c1 v1
c2 v2
c3 v4
c4 v4

Where c1, c2, c3, c4 are column names and v1, v2, v3, v4 are row values.

I did notice a couple of potential problems:

Is sw a StreamWriter? Why aren't you using a System.Web.UI.HtmlTextWriter
instead? Although this is a windows app, you can reference the System.Web
assembly and still use the HtmlTextWriter. This way you are better assured
of having well formed HTML. Granted a StreamWriter is easier...

When you write the column names, you have an extra /td on the end of the
row.
[color=blue]
> sw.WriteLine("</b>")
> sw.WriteLine("</td>") <- ???
> sw.WriteLine("</tr>")[/color]

Rather than then use <b> around the headers, consider using <th> for each
header cell.
[color=blue]
> sw.WriteLine("<tr>")
> For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> sw.Write("<th>{0}</th>",
> dshistory1.Tables(i).Columns(c).ColumnName)
> Next
> sw.WriteLine("</tr>")[/color]

This also allows you to control how it is displayed via a CSS file.

Rather than use For To, consider a For Each loop, IMO it cleans up the code,
even more so in VS.NET 2003

' VS.NET 2003 syntax
' (some stuff omitted for clarity)
For Each table As DataTable In dshistory.Tables
For Each column As DataColumn In table.Columns
sw.Write("<th>{0}</th>", column.ColumnName)
Next
For Each row As DataRow In table.Rows
For Each column As DataColumn In table.Columns
sw.Write("<td>{0}</td>", row.Item(column))
Next
Next
Next

For VS.NET 2002 you will need to use some dims:
Dim table As DataTable
Dim column As DataColumn
Dim row As DataRow

Which has the added benefit of causing the program to run quicker, as you
are not dereferencing 3 or 4 objects to get at a value, you are accessing
the object directly. And I've seen reports that indexing a DataRow by a
DataColumn is the faster way to index.

Hope this helps
Jay

"scorpion53061" <scorpion_53061@yahoo.com> wrote in message
news:%23jcNABmaDHA.1600@TK2MSFTNGP09.phx.gbl...[color=blue]
> Very urgent and I am very close but need a little help to get me over the
> edge........
>
> I need to write these columns to a html file with each row containing[/color]
these[color=blue]
> columns (seperated by breaks). Currently this code write all columns to a
> single row which works but is not what the boss wants. If anyone has
> suggestions how you would do a report like this I very much would like to
> hear them as well. vb.net windows app.
>
> sw.WriteLine("<html>")
> sw.WriteLine("<head>")
> sw.WriteLine("</head>")
> sw.WriteLine("<body>")
> Dim i, r, c As Integer
> For i = 0 To dshistory1.Tables.Count - 1
> sw.WriteLine("<center>")
> sw.WriteLine("<table border=1 width='100%'
> style='border-collapse: collapse' cellspacing='3'
> bordercolorlight='#000000'>")
> sw.WriteLine("<tr>")
> sw.WriteLine("<b>")
> For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> sw.Write("<td>{0}</td>",
> dshistory1.Tables(i).Columns(c).ColumnName)
> Next
> sw.WriteLine("</b>")
> sw.WriteLine("</td>")
> sw.WriteLine("</tr>")
> For r = 0 To dshistory1.Tables(i).Rows.Count - 1
>
> sw.WriteLine("<tr>")
> For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> sw.Write("<td>{0}</td>",
> dshistory1.Tables(i).Rows(r).Item(c))
> Next
> sw.WriteLine("</tr>")
> Next
>
> sw.WriteLine("</table>")
> sw.WriteLine("</center>")
> 'sw.WriteLine("</hr>")
> Next
> sw.WriteLine("</body>")
> sw.WriteLine("</html>")
> sw.Close()
>
> What I am trying to do is this(and make it all fit in a landscape print
> situation:)
>
> Row 1 is the title ("Sales Report" - not in loop)
>
> (Row 1)
> CUSTNO CUSTNAME SLSMNO
> (Row 2 is the values in the columns above)
>
> (Row 3)
> LYTMSALES LYTDSALES DECSALES NOVSALES OCTSALES SEPTSALES AUGSALES JULSALES
> JUNSALES MAYSALES APRSALES MARSALES FEBSALES JANSALES
> (row 4 is the values in the columns above)
>
> etc on and so forth with the profit, orders, arbal, and GM columns.
>
> These are the names of the columns in the table.
>
> <xs:element name="CUSTNO" type="xs:int" minOccurs="0" />
> <xs:element name="CUSTNAME" type="xs:string" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="SLSMN" type="xs:int" minOccurs="0" />
> <xs:element name="LYTMSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="LYTMPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="LYTMGM" type="xs:double" minOccurs="0" />
> <xs:element name="LYTMORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="LYTMARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="LYTDSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="LYTDPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="LYTDGM" type="xs:double" minOccurs="0" />
> <xs:element name="LYTDORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="LYTDARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JANSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JANGM" type="xs:double" minOccurs="0" />
> <xs:element name="JANPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JANORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="JANARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="FEBSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="FEBPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="FEBGM" type="xs:double" minOccurs="0" />
> <xs:element name="FEBORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="FEBARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="MARSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="MARPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="MARGM" type="xs:double" minOccurs="0" />
> <xs:element name="MARORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="MARARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="APRSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="APRPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="APRGM" type="xs:double" minOccurs="0" />
> <xs:element name="APRORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="APRARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="MAYSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="MAYPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="MAYGM" type="xs:double" minOccurs="0" />
> <xs:element name="MAYORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="MAYARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JUNESALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JUNEPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="JUNEGM" type="xs:double" minOccurs="0" />
> <xs:element name="JUNEORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="JUNEARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JULYSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="JULYPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="JULYGM" type="xs:double" minOccurs="0" />
> <xs:element name="JULYORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="JULYARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="AUGSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="AUGPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="AUGGM" type="xs:double" minOccurs="0" />
> <xs:element name="AUGORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="AUGARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="SEPTSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="SEPTPROFIT" type="xs:double" minOccurs="0"
> />
> <xs:element name="SEPTGM" type="xs:double" minOccurs="0" />
> <xs:element name="SEPTORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="SEPTARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="OCTSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="OCTPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="OCTGM" type="xs:double" minOccurs="0" />
> <xs:element name="OCTORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="OCTARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="NOVSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="NOVPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="NOVGM" type="xs:double" minOccurs="0" />
> <xs:element name="NOVORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="NOVARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="DECSALES" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="DECPROFIT" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
> <xs:element name="DECGM" type="xs:double" minOccurs="0" />
> <xs:element name="DECORDERS" type="xs:int" minOccurs="0" />
> <xs:element name="DECARBAL" type="xs:double" minOccurs="0"[/color]
/>[color=blue]
>
>[/color]
scorpion53061
Guest
  
Posts: n/a
#5: Nov 19 '05

re: Columns and Values (Urgent)

The way I have it currently written all the values appear straight across as
if in an excel spreadsheet. What I am tring to do is group all the values
that go together (LYTDSALES, JANSALES, FEBSALES etc)

in one line and then the values in the next line then in the next line:
(LYTDPROFIT, JANPROFIT, FEBPROFIT etc)
then the values from those columns.

I hope I am making sense.

"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow@email.msn.com> wrote in message
news:ultNx2maDHA.3768@tk2msftngp13.phx.gbl...[color=blue]
> Scorpion,
> I don't see any real problems. I can run your code 'as is' and the output
> appears mostly as you describe it. When the HTML is viewed in design mode[/color]
or[color=blue]
> the browser, in source view there is only one line, but 'who cares' is[/color]
html[color=blue]
> source. (if you care, then change the Write to WriteLine...)
>
> Maybe I am reading your description wrong.
>
> Do you want:
>
> c1 c2 c3 c4
> v1 v2 v3 v4
>
> Or do you want:
>
> c1 v1
> c2 v2
> c3 v4
> c4 v4
>
> Where c1, c2, c3, c4 are column names and v1, v2, v3, v4 are row values.
>
> I did notice a couple of potential problems:
>
> Is sw a StreamWriter? Why aren't you using a System.Web.UI.HtmlTextWriter
> instead? Although this is a windows app, you can reference the System.Web
> assembly and still use the HtmlTextWriter. This way you are better assured
> of having well formed HTML. Granted a StreamWriter is easier...
>
> When you write the column names, you have an extra /td on the end of the
> row.
>[color=green]
> > sw.WriteLine("</b>")
> > sw.WriteLine("</td>") <- ???
> > sw.WriteLine("</tr>")[/color]
>
> Rather than then use <b> around the headers, consider using <th> for each
> header cell.
>[color=green]
> > sw.WriteLine("<tr>")
> > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > sw.Write("<th>{0}</th>",
> > dshistory1.Tables(i).Columns(c).ColumnName)
> > Next
> > sw.WriteLine("</tr>")[/color]
>
> This also allows you to control how it is displayed via a CSS file.
>
> Rather than use For To, consider a For Each loop, IMO it cleans up the[/color]
code,[color=blue]
> even more so in VS.NET 2003
>
> ' VS.NET 2003 syntax
> ' (some stuff omitted for clarity)
> For Each table As DataTable In dshistory.Tables
> For Each column As DataColumn In table.Columns
> sw.Write("<th>{0}</th>", column.ColumnName)
> Next
> For Each row As DataRow In table.Rows
> For Each column As DataColumn In table.Columns
> sw.Write("<td>{0}</td>", row.Item(column))
> Next
> Next
> Next
>
> For VS.NET 2002 you will need to use some dims:
> Dim table As DataTable
> Dim column As DataColumn
> Dim row As DataRow
>
> Which has the added benefit of causing the program to run quicker, as you
> are not dereferencing 3 or 4 objects to get at a value, you are accessing
> the object directly. And I've seen reports that indexing a DataRow by a
> DataColumn is the faster way to index.
>
> Hope this helps
> Jay
>
> "scorpion53061" <scorpion_53061@yahoo.com> wrote in message
> news:%23jcNABmaDHA.1600@TK2MSFTNGP09.phx.gbl...[color=green]
> > Very urgent and I am very close but need a little help to get me over[/color][/color]
the[color=blue][color=green]
> > edge........
> >
> > I need to write these columns to a html file with each row containing[/color]
> these[color=green]
> > columns (seperated by breaks). Currently this code write all columns to[/color][/color]
a[color=blue][color=green]
> > single row which works but is not what the boss wants. If anyone has
> > suggestions how you would do a report like this I very much would like[/color][/color]
to[color=blue][color=green]
> > hear them as well. vb.net windows app.
> >
> > sw.WriteLine("<html>")
> > sw.WriteLine("<head>")
> > sw.WriteLine("</head>")
> > sw.WriteLine("<body>")
> > Dim i, r, c As Integer
> > For i = 0 To dshistory1.Tables.Count - 1
> > sw.WriteLine("<center>")
> > sw.WriteLine("<table border=1 width='100%'
> > style='border-collapse: collapse' cellspacing='3'
> > bordercolorlight='#000000'>")
> > sw.WriteLine("<tr>")
> > sw.WriteLine("<b>")
> > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > sw.Write("<td>{0}</td>",
> > dshistory1.Tables(i).Columns(c).ColumnName)
> > Next
> > sw.WriteLine("</b>")
> > sw.WriteLine("</td>")
> > sw.WriteLine("</tr>")
> > For r = 0 To dshistory1.Tables(i).Rows.Count - 1
> >
> > sw.WriteLine("<tr>")
> > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > sw.Write("<td>{0}</td>",
> > dshistory1.Tables(i).Rows(r).Item(c))
> > Next
> > sw.WriteLine("</tr>")
> > Next
> >
> > sw.WriteLine("</table>")
> > sw.WriteLine("</center>")
> > 'sw.WriteLine("</hr>")
> > Next
> > sw.WriteLine("</body>")
> > sw.WriteLine("</html>")
> > sw.Close()
> >
> > What I am trying to do is this(and make it all fit in a landscape print
> > situation:)
> >
> > Row 1 is the title ("Sales Report" - not in loop)
> >
> > (Row 1)
> > CUSTNO CUSTNAME SLSMNO
> > (Row 2 is the values in the columns above)
> >
> > (Row 3)
> > LYTMSALES LYTDSALES DECSALES NOVSALES OCTSALES SEPTSALES AUGSALES[/color][/color]
JULSALES[color=blue][color=green]
> > JUNSALES MAYSALES APRSALES MARSALES FEBSALES JANSALES
> > (row 4 is the values in the columns above)
> >
> > etc on and so forth with the profit, orders, arbal, and GM columns.
> >
> > These are the names of the columns in the table.
> >
> > <xs:element name="CUSTNO" type="xs:int" minOccurs="0" />
> > <xs:element name="CUSTNAME" type="xs:string" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="SLSMN" type="xs:int" minOccurs="0" />
> > <xs:element name="LYTMSALES" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="LYTMPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue][color=green]
> > />
> > <xs:element name="LYTMGM" type="xs:double" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="LYTMORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="LYTMARBAL" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="LYTDSALES" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="LYTDPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue][color=green]
> > />
> > <xs:element name="LYTDGM" type="xs:double" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="LYTDORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="LYTDARBAL" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="JANSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="JANGM" type="xs:double" minOccurs="0" />
> > <xs:element name="JANPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="JANORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="JANARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="FEBSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="FEBPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="FEBGM" type="xs:double" minOccurs="0" />
> > <xs:element name="FEBORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="FEBARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="MARSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="MARPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="MARGM" type="xs:double" minOccurs="0" />
> > <xs:element name="MARORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="MARARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="APRSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="APRPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="APRGM" type="xs:double" minOccurs="0" />
> > <xs:element name="APRORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="APRARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="MAYSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="MAYPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="MAYGM" type="xs:double" minOccurs="0" />
> > <xs:element name="MAYORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="MAYARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="JUNESALES" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="JUNEPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue][color=green]
> > />
> > <xs:element name="JUNEGM" type="xs:double" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="JUNEORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="JUNEARBAL" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="JULYSALES" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="JULYPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue][color=green]
> > />
> > <xs:element name="JULYGM" type="xs:double" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="JULYORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="JULYARBAL" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="AUGSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="AUGPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="AUGGM" type="xs:double" minOccurs="0" />
> > <xs:element name="AUGORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="AUGARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="SEPTSALES" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="SEPTPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue][color=green]
> > />
> > <xs:element name="SEPTGM" type="xs:double" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="SEPTORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="SEPTARBAL" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="OCTSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="OCTPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="OCTGM" type="xs:double" minOccurs="0" />
> > <xs:element name="OCTORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="OCTARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="NOVSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="NOVPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="NOVGM" type="xs:double" minOccurs="0" />
> > <xs:element name="NOVORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="NOVARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="DECSALES" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> > <xs:element name="DECPROFIT" type="xs:double"[/color][/color]
minOccurs="0"[color=blue]
> />[color=green]
> > <xs:element name="DECGM" type="xs:double" minOccurs="0" />
> > <xs:element name="DECORDERS" type="xs:int" minOccurs="0"[/color][/color]
/>[color=blue][color=green]
> > <xs:element name="DECARBAL" type="xs:double" minOccurs="0"[/color]
> />[color=green]
> >
> >[/color]
>
>[/color]
Fergus Cooney
Guest
  
Posts: n/a
#6: Nov 19 '05

re: Columns and Values (Urgent)

Dear Scorpion,

Your data table is wide. Very wide. Loadsacolumns. But that's no
problem. :-)

Your current solution is to dump every column out in an html table
that's wide. Very, very wide. Now that IS a problem, lol. Your Boss doesn't
want to have to play scrolly-scrolly. <:-( And why should he?

We're going to solve your problem in a series of steps. I'm not going to
give you the final solution immediately - I'm doing this off the top of my
head. You must do it step by step to make sure that you understand it and
that it works. Ok?

Here's what I want you to do:

Take all the code out of your outer loop and put it in a subroutine
which takes a Table as its parameter. Take all the column heading code out
and put that in its own routine. And the same for the values.

Replace dshistory1.Tables(i) with dtTable, the parameter. This is both
for efficiency and for clarity of code. Think about how often you've
repeated dshistory1.Tables(i). Cringe!! Get into the habit of spotting such
repetition and using extra variables instead.

This will give you:


Sub DoReport()
For i = 0 To dshistory1.Tables.Count - 1
Output_Table (dshistory1.Tables(i))
Next
End Sub

Sub Output_Table (DataTable dtTable)
sw.WriteLine("<center>")
: : :
Output_ColumnHeadings (dtTable)
Output_Values (dtTable)
sw.WriteLine("</table>")
: : :
End Sub

Sub Output_ColumnHeadings (DataTable dtTable)
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
Next
sw.WriteLine("</b>")
: : :
End Sub

Sub Output_Values (DataTable dtTable)
For r = 0 To dtTable.Rows.Count - 1
sw.WriteLine("<tr>")
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
Next
sw.WriteLine("</tr>")
Next
End Sub


This will give you exactly the same as you already have. But it's
clearer and more efficient. More importantly, it gives us the foundation for
the next step. You can fix any errors that I've made.
Now, go to it!!

Regards,
Fergus.

================================
To the tune of Rawhide:

Scrollin' scrollin' scrollin,
Get that web page scrollin'
Scrollin' scrollin' scrollin,
Re-PORT.
scorpion53061
Guest
  
Posts: n/a
#7: Nov 19 '05

re: Columns and Values (Urgent)

Like this?

Ready for step 2...

'sw publicly declared as streamwriter
Sub DoReport()
For i = 0 To Dshistory1.Tables.Count - 1 'i,r,c publicy declared as
integer
dtTable = Dshistory1.Tables(i) 'publicly delcared dtTable
Output_Table(Dshistory1.Tables(i))
Next
End Sub

Sub Output_Table(ByVal dtTable)
sw.WriteLine("<center>")

Output_ColumnHeadings(dtTable)
Output_Values(dtTable)
sw.WriteLine("</table>")

End Sub

Sub Output_ColumnHeadings(ByVal dtTable)
Dim c As Integer
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
Next
sw.WriteLine("</b>")

End Sub

Sub Output_Values(ByVal dtTable)
Dim r As Integer
For r = 0 To dtTable.Rows.Count - 1
sw.WriteLine("<tr>")
For c = 0 To dtTable.Columns.Count - 1
sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
Next
sw.WriteLine("</tr>")
Next
End Sub

"Fergus Cooney" <wollus@tesco.net> wrote in message
news:ukkwTWnaDHA.1280@tk2msftngp13.phx.gbl...[color=blue]
> Dear Scorpion,
>
> Your data table is wide. Very wide. Loadsacolumns. But that's no
> problem. :-)
>
> Your current solution is to dump every column out in an html table
> that's wide. Very, very wide. Now that IS a problem, lol. Your Boss[/color]
doesn't[color=blue]
> want to have to play scrolly-scrolly. <:-( And why should he?
>
> We're going to solve your problem in a series of steps. I'm not going[/color]
to[color=blue]
> give you the final solution immediately - I'm doing this off the top of my
> head. You must do it step by step to make sure that you understand it and
> that it works. Ok?
>
> Here's what I want you to do:
>
> Take all the code out of your outer loop and put it in a subroutine
> which takes a Table as its parameter. Take all the column heading code out
> and put that in its own routine. And the same for the values.
>
> Replace dshistory1.Tables(i) with dtTable, the parameter. This is both
> for efficiency and for clarity of code. Think about how often you've
> repeated dshistory1.Tables(i). Cringe!! Get into the habit of spotting[/color]
such[color=blue]
> repetition and using extra variables instead.
>
> This will give you:
>
>
> Sub DoReport()
> For i = 0 To dshistory1.Tables.Count - 1
> Output_Table (dshistory1.Tables(i))
> Next
> End Sub
>
> Sub Output_Table (DataTable dtTable)
> sw.WriteLine("<center>")
> : : :
> Output_ColumnHeadings (dtTable)
> Output_Values (dtTable)
> sw.WriteLine("</table>")
> : : :
> End Sub
>
> Sub Output_ColumnHeadings (DataTable dtTable)
> For c = 0 To dtTable.Columns.Count - 1
> sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
> Next
> sw.WriteLine("</b>")
> : : :
> End Sub
>
> Sub Output_Values (DataTable dtTable)
> For r = 0 To dtTable.Rows.Count - 1
> sw.WriteLine("<tr>")
> For c = 0 To dtTable.Columns.Count - 1
> sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
> Next
> sw.WriteLine("</tr>")
> Next
> End Sub
>
>
> This will give you exactly the same as you already have. But it's
> clearer and more efficient. More importantly, it gives us the foundation[/color]
for[color=blue]
> the next step. You can fix any errors that I've made.
> Now, go to it!!
>
> Regards,
> Fergus.
>
> ================================
> To the tune of Rawhide:
>
> Scrollin' scrollin' scrollin,
> Get that web page scrollin'
> Scrollin' scrollin' scrollin,
> Re-PORT.
>
>
>
>
>[/color]
Jay B. Harlow [MVP - Outlook]
Guest
  
Posts: n/a
#8: Nov 19 '05

re: Columns and Values (Urgent)

Scorpion,
Then you will need to use multiple loops, or put a 'control break' in the
middle of the loop. Or even better don't use a loop.

Something like (pseudo code)

write begin table row
write LYTDSALES
write JANSALES
write FEBSALES
write end table row

write begin table row
write LYTDPROFIT
write JANPROFIT
write FEBPROFIT
write end table row

If LYTDSALES, JANSALES, FEBSALES, LYTDPROFIT, JANPROFIT, & FEBPROFIT are in
the same row in the table, that sounds like poor design. I would have TYPE,
LYTD, JAN, FEB in a row, where TYPE would an enumeration with values of
SALES, PROFIT, ORDERS.

Then the Data Set would look similar to :
TYPE - LYTD - JAN - FEB
'SALES' - 100 - 10 - 90
' PROFIT' - 200 - 20 - 180

If you cannot change the layout of the Dataset, then I would suggest not
using a loop at all, instead explicitly refer to each field building the
output (the pseudo code above).

Hope this helps
Jay


"scorpion53061" <scorpion_53061@yahoo.com> wrote in message
news:OVkeZJnaDHA.2136@TK2MSFTNGP10.phx.gbl...[color=blue]
> The way I have it currently written all the values appear straight across[/color]
as[color=blue]
> if in an excel spreadsheet. What I am tring to do is group all the values
> that go together (LYTDSALES, JANSALES, FEBSALES etc)
>
> in one line and then the values in the next line then in the next line:
> (LYTDPROFIT, JANPROFIT, FEBPROFIT etc)
> then the values from those columns.
>
> I hope I am making sense.
>
> "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow@email.msn.com> wrote in[/color]
message[color=blue]
> news:ultNx2maDHA.3768@tk2msftngp13.phx.gbl...[color=green]
> > Scorpion,
> > I don't see any real problems. I can run your code 'as is' and the[/color][/color]
output[color=blue][color=green]
> > appears mostly as you describe it. When the HTML is viewed in design[/color][/color]
mode[color=blue]
> or[color=green]
> > the browser, in source view there is only one line, but 'who cares' is[/color]
> html[color=green]
> > source. (if you care, then change the Write to WriteLine...)
> >
> > Maybe I am reading your description wrong.
> >
> > Do you want:
> >
> > c1 c2 c3 c4
> > v1 v2 v3 v4
> >
> > Or do you want:
> >
> > c1 v1
> > c2 v2
> > c3 v4
> > c4 v4
> >
> > Where c1, c2, c3, c4 are column names and v1, v2, v3, v4 are row values.
> >
> > I did notice a couple of potential problems:
> >
> > Is sw a StreamWriter? Why aren't you using a[/color][/color]
System.Web.UI.HtmlTextWriter[color=blue][color=green]
> > instead? Although this is a windows app, you can reference the[/color][/color]
System.Web[color=blue][color=green]
> > assembly and still use the HtmlTextWriter. This way you are better[/color][/color]
assured[color=blue][color=green]
> > of having well formed HTML. Granted a StreamWriter is easier...
> >
> > When you write the column names, you have an extra /td on the end of the
> > row.
> >[color=darkred]
> > > sw.WriteLine("</b>")
> > > sw.WriteLine("</td>") <- ???
> > > sw.WriteLine("</tr>")[/color]
> >
> > Rather than then use <b> around the headers, consider using <th> for[/color][/color]
each[color=blue][color=green]
> > header cell.
> >[color=darkred]
> > > sw.WriteLine("<tr>")
> > > For c = 0 To dshistory1.Tables(i).Columns.Count - 1
> > > sw.Write("<th>{0}</th>",
> > > dshistory1.Tables(i).Columns(c).ColumnName)
> > > Next
> > > sw.WriteLine("</tr>")[/color]
> >
> > This also allows you to control how it is displayed via a CSS file.
> >
> > Rather than use For To, consider a For Each loop, IMO it cleans up the[/color]
> code,[color=green]
> > even more so in VS.NET 2003
> >
> > ' VS.NET 2003 syntax
> > ' (some stuff omitted for clarity)
> > For Each table As DataTable In dshistory.Tables
> > For Each column As DataColumn In table.Columns
> > sw.Write("<th>{0}</th>", column.ColumnName)
> > Next
> > For Each row As DataRow In table.Rows
> > For Each column As DataColumn In table.Columns
> > sw.Write("<td>{0}</td>", row.Item(column))
> > Next
> > Next
> > Next
> >
> > For VS.NET 2002 you will need to use some dims:
> > Dim table As DataTable
> > Dim column As DataColumn
> > Dim row As DataRow
> >
> > Which has the added benefit of causing the program to run quicker, as[/color][/color]
you[color=blue][color=green]
> > are not dereferencing 3 or 4 objects to get at a value, you are[/color][/color]
accessing[color=blue][color=green]
> > the object directly. And I've seen reports that indexing a DataRow by a
> > DataColumn is the faster way to index.
> >
> > Hope this helps
> > Jay
> >[/color][/color]
<<snip>>
Fergus Cooney
Guest
  
Posts: n/a
#9: Nov 19 '05

re: Columns and Values (Urgent)

Hi again Scorpion,

Ok, you've now got the same output from code with a better structure. At
least I hope you do, you'll learn it better that way.

The next step is to deal with the sub-tables.

First off, loose ends - Add code in DoReport() for the report heading,
and in Output_Table() for the CUSTNO CUSTNAME SLSMNO row. That will use
columns 0, 1 and 2 of your Table. The actual data starts in column 3.

Now the rest of the columns are in groups of 5:
LYTM: SALES, PROFIT, GM, ORDERS, ARBAL,
LYTD: SALES, PROFIT, GM, ORDERS, ARBAL,
JAN: SALES, PROFIT, GM, ORDERS, ARBAL,
Etc..

So the index for SALES will be 3, 8, 13, etc. For PROFIT it'll be 4, 9,
14, etc. And the pattern continues in an obvious way.

This means that to access all SALES items, you'd have a loop such as
For c = 3 To dtTable.Columns.Count - 1 Step 5
: : :
Next

Now having a 3 and 5 in a loop is next to meaningless compared to having
variable names. The 3 is the starting index of the values. The 5 is the gap
between values. Choose some names - decent length. I'm going to use SIOV and
GBV in my examples. Don't use these in your code - they're terrible names.
I'm using them to force you to think. :-)

So your column headings loop becomes
For c = SIOV To dtTable.Columns.Count - 1 Step GBV
: : :
Next
And likewise for the values.

The next step is to make SIOV and GBV parameters of the subroutines. The
subroutines will just do what they're told. The caller, Output_Table(), is
the one that knows what they should do.

So you'll have
Sub Output_ColumnHeadings (dtTable As DataTable, SIOV As Integer,
GBV As Integer)
And likewise for the values.

Now Output_Table() is going to have to do its thing with each of the
sub-tables. This means that SIOV is going to be different for each Table. So
Output_Table() will need SIOV and GBV to be parameters. And DoReport() is
going to have to tell Output_Table() what values to use.

Perhaps the next step will be obvious to you now? In DoReport() you want
to call OutputTable() once for each sub-table. Which means a loop covering
all 5.

[Note that the number 5 has appeared again. This time it's the number of
tables. You could therefore use the same variable name, GBV. Notice how,
with my name, the idea of a gap-between-values makes no sense compared with
number-of-tables. This is something that occurs often, whereby the same
value "changes" it's use. It warrants a different and appropriate name in
each context. This point can get as subtle as you want. Think about it
later. :-)]

Ok, so OutputReport() now gets a secondary loop around Output_Table().
It also needs to pass correct values for SIOV and GBV.

NumT = 5 'Suitable comment required.
GBV = 5 'Suitable comment required.
SIOV = 3 'Suitable comment required.
For i = 0 To dshistory1.Tables.Count - 1
For ST = 1 To NumT
Output_Table (dshistory1.Tables(i), SIOV + ST - 1, GBV)
Next
Next

Again, I'll leave it to you to make proper code out of all this. Come
back to me with your progress. And tell me what the deadline is, too - I
anticipate that we haven't quite finished the job yet. I expect issues to do
with CUSTNO and CUSTNAME!

Regards,
Fergus.
scorpion53061
Guest
  
Posts: n/a
#10: Nov 19 '05

re: Columns and Values (Urgent)

The deadline is Monday at 10am when I am suppose to demonstrate it for my
boss.

He assigned it last Wednesday.... :(

WIll take some time to work through this.


"Fergus Cooney" <wollus@tesco.net> wrote in message
news:%23m7cg0naDHA.2032@TK2MSFTNGP10.phx.gbl...[color=blue]
> Hi again Scorpion,
>
> Ok, you've now got the same output from code with a better structure.[/color]
At[color=blue]
> least I hope you do, you'll learn it better that way.
>
> The next step is to deal with the sub-tables.
>
> First off, loose ends - Add code in DoReport() for the report heading,
> and in Output_Table() for the CUSTNO CUSTNAME SLSMNO row. That will use
> columns 0, 1 and 2 of your Table. The actual data starts in column 3.
>
> Now the rest of the columns are in groups of 5:
> LYTM: SALES, PROFIT, GM, ORDERS, ARBAL,
> LYTD: SALES, PROFIT, GM, ORDERS, ARBAL,
> JAN: SALES, PROFIT, GM, ORDERS, ARBAL,
> Etc..
>
> So the index for SALES will be 3, 8, 13, etc. For PROFIT it'll be 4,[/color]
9,[color=blue]
> 14, etc. And the pattern continues in an obvious way.
>
> This means that to access all SALES items, you'd have a loop such as
> For c = 3 To dtTable.Columns.Count - 1 Step 5
> : : :
> Next
>
> Now having a 3 and 5 in a loop is next to meaningless compared to[/color]
having[color=blue]
> variable names. The 3 is the starting index of the values. The 5 is the[/color]
gap[color=blue]
> between values. Choose some names - decent length. I'm going to use SIOV[/color]
and[color=blue]
> GBV in my examples. Don't use these in your code - they're terrible names.
> I'm using them to force you to think. :-)
>
> So your column headings loop becomes
> For c = SIOV To dtTable.Columns.Count - 1 Step GBV
> : : :
> Next
> And likewise for the values.
>
> The next step is to make SIOV and GBV parameters of the subroutines.[/color]
The[color=blue]
> subroutines will just do what they're told. The caller, Output_Table(), is
> the one that knows what they should do.
>
> So you'll have
> Sub Output_ColumnHeadings (dtTable As DataTable, SIOV As Integer,
> GBV As Integer)
> And likewise for the values.
>
> Now Output_Table() is going to have to do its thing with each of the
> sub-tables. This means that SIOV is going to be different for each Table.[/color]
So[color=blue]
> Output_Table() will need SIOV and GBV to be parameters. And DoReport() is
> going to have to tell Output_Table() what values to use.
>
> Perhaps the next step will be obvious to you now? In DoReport() you[/color]
want[color=blue]
> to call OutputTable() once for each sub-table. Which means a loop covering
> all 5.
>
> [Note that the number 5 has appeared again. This time it's the number[/color]
of[color=blue]
> tables. You could therefore use the same variable name, GBV. Notice how,
> with my name, the idea of a gap-between-values makes no sense compared[/color]
with[color=blue]
> number-of-tables. This is something that occurs often, whereby the same
> value "changes" it's use. It warrants a different and appropriate name in
> each context. This point can get as subtle as you want. Think about it
> later. :-)]
>
> Ok, so OutputReport() now gets a secondary loop around Output_Table().
> It also needs to pass correct values for SIOV and GBV.
>
> NumT = 5 'Suitable comment required.
> GBV = 5 'Suitable comment required.
> SIOV = 3 'Suitable comment required.
> For i = 0 To dshistory1.Tables.Count - 1
> For ST = 1 To NumT
> Output_Table (dshistory1.Tables(i), SIOV + ST - 1, GBV)
> Next
> Next
>
> Again, I'll leave it to you to make proper code out of all this. Come
> back to me with your progress. And tell me what the deadline is, too - I
> anticipate that we haven't quite finished the job yet. I expect issues to[/color]
do[color=blue]
> with CUSTNO and CUSTNAME!
>
> Regards,
> Fergus.
>
>
>[/color]
scorpion53061
Guest
  
Posts: n/a
#11: Nov 19 '05

re: Columns and Values (Urgent)

I am not familar with XSLT. But can you help me learn enough to do what I
need to do here...

By the way I did not design the SQL table this came from. It is from another
application that is fed from a unix server.

"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow@email.msn.com> wrote in message
news:OglwZznaDHA.2632@TK2MSFTNGP12.phx.gbl...[color=blue]
> Scorpion,
> Oh! you do know that if you dshistory is coming from an XML file, it can[/color]
be[color=blue]
> easier to use an XmlTranform to take it from a Xml document an Html
> document. Of course this assumes you know XSLT.
>
> Hope this helps
> Jay
>
> "scorpion53061" <scorpion_53061@yahoo.com> wrote in message
> news:OdsdwmnaDHA.1600@TK2MSFTNGP09.phx.gbl...[color=green]
> > Like this?
> >
> > Ready for step 2...
> >
> > 'sw publicly declared as streamwriter
> > Sub DoReport()
> > For i = 0 To Dshistory1.Tables.Count - 1 'i,r,c publicy declared[/color]
> as[color=green]
> > integer
> > dtTable = Dshistory1.Tables(i) 'publicly delcared dtTable
> > Output_Table(Dshistory1.Tables(i))
> > Next
> > End Sub
> >
> > Sub Output_Table(ByVal dtTable)
> > sw.WriteLine("<center>")
> >
> > Output_ColumnHeadings(dtTable)
> > Output_Values(dtTable)
> > sw.WriteLine("</table>")
> >
> > End Sub
> >
> > Sub Output_ColumnHeadings(ByVal dtTable)
> > Dim c As Integer
> > For c = 0 To dtTable.Columns.Count - 1
> > sw.Write("<td>{0}</td>", dtTable.Columns(c).ColumnName)
> > Next
> > sw.WriteLine("</b>")
> >
> > End Sub
> >
> > Sub Output_Values(ByVal dtTable)
> > Dim r As Integer
> > For r = 0 To dtTable.Rows.Count - 1
> > sw.WriteLine("<tr>")
> > For c = 0 To dtTable.Columns.Count - 1
> > sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
> > Next
> > sw.WriteLine("</tr>")
> > Next
> > End Sub
> >
> > "Fergus Cooney" <wollus@tesco.net> wrote in message
> > news:ukkwTWnaDHA.1280@tk2msftngp13.phx.gbl...[color=darkred]
> > > Dear Scorpion,
> > >
> > > Your data table is wide. Very wide. Loadsacolumns. But that's no
> > > problem. :-)
> > >
> > > Your current solution is to dump every column out in an html table
> > > that's wide. Very, very wide. Now that IS a problem, lol. Your Boss[/color]
> > doesn't[color=darkred]
> > > want to have to play scrolly-scrolly. <:-( And why should he?
> > >
> > > We're going to solve your problem in a series of steps. I'm not[/color][/color]
> going[color=green]
> > to[color=darkred]
> > > give you the final solution immediately - I'm doing this off the top[/color][/color][/color]
of[color=blue]
> my[color=green][color=darkred]
> > > head. You must do it step by step to make sure that you understand it[/color][/color]
> and[color=green][color=darkred]
> > > that it works. Ok?
> > >
> > > Here's what I want you to do:
> > >
> > > Take all the code out of your outer loop and put it in a[/color][/color][/color]
subroutine[color=blue][color=green][color=darkred]
> > > which takes a Table as its parameter. Take all the column heading code[/color][/color]
> out[color=green][color=darkred]
> > > and put that in its own routine. And the same for the values.
> > >
> > > Replace dshistory1.Tables(i) with dtTable, the parameter. This is[/color][/color]
> both[color=green][color=darkred]
> > > for efficiency and for clarity of code. Think about how often you've
> > > repeated dshistory1.Tables(i). Cringe!! Get into the habit of spotting[/color]
> > such[color=darkred]
> > > repetition and using extra variables instead.
> > >
> > > This will give you:
> > >
> > >
> > > Sub DoReport()
> > > For i = 0 To dshistory1.Tables.Count - 1
> > > Output_Table (dshistory1.Tables(i))
> > > Next
> > > End Sub
> > >
> > > Sub Output_Table (DataTable dtTable)
> > > sw.WriteLine("<center>")
> > > : : :
> > > Output_ColumnHeadings (dtTable)
> > > Output_Values (dtTable)
> > > sw.WriteLine("</table>")
> > > : : :
> > > End Sub
> > >
> > > Sub Output_ColumnHeadings (DataTable dtTable)
> > > For c = 0 To dtTable.Columns.Count - 1
> > > sw.Write("<td>{0}</td>",[/color][/color][/color]
dtTable.Columns(c).ColumnName)[color=blue][color=green][color=darkred]
> > > Next
> > > sw.WriteLine("</b>")
> > > : : :
> > > End Sub
> > >
> > > Sub Output_Values (DataTable dtTable)
> > > For r = 0 To dtTable.Rows.Count - 1
> > > sw.WriteLine("<tr>")
> > > For c = 0 To dtTable.Columns.Count - 1
> > > sw.Write("<td>{0}</td>", dtTable.Rows(r).Item(c))
> > > Next
> > > sw.WriteLine("</tr>")
> > > Next
> > > End Sub
> > >
> > >
> > > This will give you exactly the same as you already have. But it's
> > > clearer and more efficient. More importantly, it gives us the[/color][/color][/color]
foundation[color=blue][color=green]
> > for[color=darkred]
> > > the next step. You can fix any errors that I've made.
> > > Now, go to it!!
> > >
> > > Regards,
> > > Fergus.
> > >
> > > ================================
> > > To the tune of Rawhide:
> > >
> > > Scrollin' scrollin' scrollin,
> > > Get that web page scrollin'
> > > Scrollin' scrollin' scrollin,
> > > Re-PORT.
> > >
> > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
Fergus Cooney
Guest
  
Posts: n/a
#12: Nov 19 '05

re: Columns and Values (Urgent)

Hi Jay,

Lol, I thought of going down that route with Scorpion, but it's a whole
new language!

I wish I were able to give Scorpion "best practice" with what I'm
doing - certainly the solution that we're developing isn't how I'd do it
myself - but I suspect that Scorpion doesn't have our years.

And the keyword in his request is (Urgent) !! :-)

Best Wishes,
Fergus
Jay B. Harlow [MVP - Outlook]
Guest
  
Posts: n/a
#13: Nov 19 '05

re: Columns and Values (Urgent)

Fergus,
You know, sometimes you need to say "What the F**k!" and suggest a totally
different approach. ;-)

(if you've seen the non-primetime version of Risky Business).

Later
Jay

"Fergus Cooney" <wollus@tesco.net> wrote in message
news:uy7Xx4naDHA.2256@TK2MSFTNGP10.phx.gbl...[color=blue]
> Hi Jay,
>
> Lol, I thought of going down that route with Scorpion, but it's a[/color]
whole[color=blue]
> new language!
>
> I wish I were able to give Scorpion "best practice" with what I'm
> doing - certainly the solution that we're developing isn't how I'd do it
> myself - but I suspect that Scorpion doesn't have our years.
>
> And the keyword in his request is (Urgent) !! :-)
>
> Best Wishes,
> Fergus
>
>
>[/color]
Cor
Guest
  
Posts: n/a
#14: Nov 19 '05

re: Columns and Values (Urgent)

Scorpion,
Why don't you send a peace of the HTML output in this group,
Only the lines between the <table><tr>and the second </tr>
Then I know what is the problem and Jay knows what is the problem.
(You can change all the numeric data before)
Cor
Closed Thread

« previous question | next question »

Similar Visual Basic .NET bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,501 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved