Loop for Record Display - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Loop for Record Display

Author

Topic

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 17:00:23

My sql Query is
<%
Dim connection, sql, rs
Set connection = Server.CreateObject ("ADODB.Connection")
connection.open "DSN=5s"
sql= "TRANSFORM Sum([5s_Audit].Average) AS SumOfAverage "
sql = sql & "SELECT Department FROM 5s_Audit GROUP BY Department PIVOT DatePart('ww',[Due_Date]);"
'Response.write sql
'Response.end
set rs = server.createObject("ADODB.RecordSet")
rs.Open sql, connection
%>

The way I create the table is:
<TABLE cellSpacing=1 cellPadding=1 border=1 align=Center class canResize="true" bgcolor="White" id="mainTable">

<%Do while not rs.EOF
%>
<tr align=center onmouseover='highlightRow(this, "#FFFBCB")' onmouseout='highlightRow(this)'>

<td bgcolor="steelblue" class='normal' nowrap align="center"> <%=rs(i) %></td>

<%rs.MoveNext
Loop%>
The i needs to lop through as many times as there is columns. I tried putting a counter in the sql but it just did the first column.

It needs to look somethign like:
<td bgcolor="steelblue" class='normal' nowrap align="center"> <%=rs(0) %></td>
<td bgcolor="steelblue" class='normal' nowrap align="center"> <%=rs(1) %></td>
<td bgcolor="steelblue" class='normal' nowrap align="center"> <%=rs(ETC. Until End) %></td>

Any Ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:04:07

Dim iLoop AS Byte

for iloop = 1 to rs.fields.count

response.write "<td bgcolor="steelblue" class='normal' nowrap align="center"> <%=rs(" & iloop & ") %></td>"

next

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 17:27:59

When I try to enter that in sql it makes the whole page sql code, what am I doing wrong?
Thanks,
Sry, ive never used asp, or sql lol so this is tough

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:44:39

My mistake.

FOR iLoop = 0 to rs.Fields.Count - 1
NEXT

Since Fields collection is zero based.

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 17:46:50

response.write "<td bgcolor="steelblue" class='normal' nowrap align="center"> <%=rs(" & iloop & ") %></td>" Its this line thats turning everything to server code for soem reason. This goes in the sql code correct?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:53:42

[code]<TABLE cellSpacing=1 cellPadding=1 border=1 align=Center class canResize="true" bgcolor="White" id="mainTable">

<%
Dim iLoop AS Byte

do until rs.bof or rs.eof
reponse.write "<tr align=center onmouseover='highlightRow(this, "#FFFBCB")' onmouseout='highlightRow(this)'>"

for iloop = 0 to rs.fields.count - 1
response.write "<td bgcolor="steelblue" class='normal' nowrap align="center"> " & rs(iloop).Value & "</td>"
next

reponse.write "</tr>"

rs.movenext
loop
%>

</TABLE>[/code]

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 17:56:51

eh somethign is still makign everythign below that code servercode, almost like if the closing %> isnt there

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:05:15

Try again!

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 18:12:50

I removed option explicit now i get
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:22:16

[code]<TABLE cellSpacing=1 cellPadding=1 border=1 align=Center class canResize="true" bgcolor="White" id="mainTable">

<%
Dim iLoop AS Byte

do until rs.bof or rs.eof
reponse.write "<tr align=center onmouseover='highlightRow(this, ""#FFFBCB"")' onmouseout='highlightRow(this)'>"

for iloop = 0 to rs.fields.count - 1
response.write "<td bgcolor='steelblue' class='normal' nowrap align='center'> " & rs(iloop).Value & "</td>"
next

reponse.write "</tr>"

rs.movenext
loop
%>

</TABLE>[/code]

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 18:42:04

Thanks i got it working by removing as Byte, now it displays the departments on the left with all their averages for each week on the right, is their any way to get the column headings to print above them, ie: departments, 49, 50, 51 (the weeks)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:50:32

[code]<TABLE cellSpacing=1 cellPadding=1 border=1 align=Center class canResize="true" bgcolor="White" id="mainTable">

<%
Dim iLoop As Integer, iFields As Integer

'Show all headers
reponse.write "<tr>"

Set iFields = rs.fields.count - 1

for iloop = 0 to iFields
response.write "<td>" & rs.Fields.(iLoop).Name & "</td>"
next

reponse.write "</tr>"

'Iterate all records
do until rs.bof or rs.eof
reponse.write "<tr align=center onmouseover='highlightRow(this, ""#FFFBCB"")' onmouseout='highlightRow(this)'>"

for iloop = 0 to iFields
response.write "<td bgcolor='steelblue' class='normal' nowrap align='center'> " & rs(iLoop).Value & "</td>"
next

reponse.write "</tr>"

rs.movenext
loop
%>

</TABLE>[/code]

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 19:05:06

<%
Dim iloop, iFields

'Show all headers
Response.Write("</tr>")

Set iFields = rs.fields.count -1
for iloop = 0 to iFields
Response.Write("<td>"& rs.Fields.(iloop).Name &"</td>")
next

'Iterate all records
do until rs.bof or rs.eof
Response.Write("<tr align=center onmouseover='highlightRow(this, ""#FFFBCB"")' onmouseout='highlightRow(this)'>")
Response.Write "<td bgcolor='SteelBlue' class='normal' nowrap align='center'> " & rs(0).Value & "</td>"

for iloop = 1 to iFields
response.write "<td bgcolor='White' width='30' class='normal' nowrap align='center'> " & rs(iloop).Value & "</td>"
next

Response.Write("</tr>")

rs.movenext
loop
%>

at thsi line:
Response.Write("<td>"& rs.Fields.(iloop).Name &"</td>")
it says Expected identifier
if I comment it out then it has this error:
Object required: '[number: 5]'
at this line:
Set iFields = rs.fields.count -1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 19:11:02

Can you please show some effort yourself?

<TABLE cellSpacing=1 cellPadding=1 border=1 align=Center class canResize="true" bgcolor="White" id="mainTable">

<%
Dim iLoop, iFields

reponse.write "<tr>"

iFields = rs.Fields.Count - 1

for iloop = 0 to iFields
	response.write "<td>" & rs.Fields.(iLoop).Name & "</td>"
next

reponse.write "</tr>"

do until rs.bof or rs.eof
	reponse.write "<tr align=center onmouseover='highlightRow(this, ""#FFFBCB"")' onmouseout='highlightRow(this)'>"

	for iloop = 0 to iFields
		response.write "<td bgcolor='steelblue' class='normal' nowrap align='center'><b><FONT Face='arial' COLOR='white'>" & rs(iLoop).Value & "</Font></b></td>"
	next

	reponse.write "</tr>"

	rs.movenext
loop
%>

</TABLE>

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 19:14:23

Sorry I really appreciate all of your help! Ive just been messing with this for so long im about to pull my hair out. It sux being told to do somethign you knwo nothing about.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 19:16:29

Just say no! It is a sign of integrity.

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 19:19:22

ive gotten it all doen but this last part :)

gcool12
Starting Member

22 Posts

Posted - 2007-12-11 : 19:22:20

UGH
response.write "<td>" & rs.Fields.(iLoop).Name & "</td>"
expected identifier
Whats that mean!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 19:23:14

rs.Fields(iLoop).Name

Drop the point between Fields and paranthesis.

<TABLE cellSpacing=1 cellPadding=1 border=1 align=Center class canResize="true" bgcolor="White" id="mainTable">

<%
Dim iLoop, iFields

reponse.write "<tr>"

iFields = rs.Fields.Count - 1

for iloop = 0 to iFields
	response.write "<td>" & rs.Fields(iLoop).Name & "</td>"
next

reponse.write "</tr>"

do until rs.bof or rs.eof
	reponse.write "<tr align=center onmouseover='highlightRow(this, ""#FFFBCB"")' onmouseout='highlightRow(this)'>"

	for iloop = 0 to iFields
		response.write "<td bgcolor='steelblue' class='normal' nowrap align='center'><b><FONT Face='arial' COLOR='white'>" & rs(iLoop).Value & "</Font></b></td>"
	next

	reponse.write "</tr>"

	rs.movenext
loop
%>

</TABLE>

E 12°55'05.25"
N 56°04'39.16"

gcool12
Starting Member

22 Posts

Posted - 2007-12-12 : 09:30:16

Right now the page shows the date 48, 49, 51, 52, but when it gets to jan. 08 it puts week 1 08 before week 48 of 07 instead of after week 52, is there any way to change this in the query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 09:41:49

sql = sql & "SELECT Department FROM 5s_Audit GROUP BY Department PIVOT FORMAT([Due_Date], ""yyyyww"", 2, 2);"

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources