recordset in a certain format - 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

SQL Server 2000 Forums

SQL Server Development (2000)

recordset in a certain format

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-03-17 : 12:51:28

Using the stored proc below, I get a recordset like this:

Account Month Total
1234567 Jan 1000
1234567 Feb 1234.45
2222222 Jan 4500.25
3333333 Feb 250.00
3333333 Mar 12.57
3333333 Dec 75.67

SELECT C.Name as 'Account Name',
C.AccountNumber as 'Account Number',
C.UserCreditLimit as 'Current Account Limit',
Month = CASE MONTH(T.TransDate)
When 1 Then 'Jan'
When 2 Then 'Feb'
When 3 Then 'Mar'
When 4 Then 'Apr'
When 5 Then 'May'
When 6 Then 'Jun'
When 7 Then 'Jul'
When 8 Then 'Aug'
When 9 Then 'Sep'
When 10 Then 'Oct'
When 11 Then 'Nov'
When 12 Then 'Dec'
End,
CONVERT(varchar(30),SUM(T.TransAmt)) As Total
FROM CardHolder C INNER JOIN TransDetail T ON
T.AccountNumber = C.AccountNumber
WHERE T.VISATransCode NOT IN (31,65,50)
AND (DATEDIFF(mm, T.TransDate, '12/31/2001') < = 12)
GROUP BY C.Name, MONTH(T.TransDate), C.AccountNumber, C.UserCreditLimit
ORDER BY C.Name

My reqt is to display the recordset like this:

Account 1234567
Jan Feb
1000 1234.45
Account 2222222
Jan
4500.25
Account 3333333
Feb Feb Dec
250.00 12.57 75.67

I need to display the data on a web page, but I find it difficult to do the formatting on the web so I think that maybe I can get SQL Server to return the data the way I want them to and just display them on the web as they are returned.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-03-17 : 13:19:58

It's doable with the recordset you have.

What you have to do is this. When looping through the recordset to output it on your ASP page, keep a variable that holds the "previous account." At the top of the loop, compare the "current account" to teh "previous account." If they are different, you need to output the Account header and the first month. Each time through the loop check to see if the account has changed. If it has, output the account header and first month. If it has not, just output the Month and amount.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

PeterG
Posting Yak Master

156 Posts

Posted - 2003-03-17 : 13:33:29

Thanks Michael. Actually, I tried using that logic in my asp page, but it's the displaying of the data (the month and amount)in the format required that's giving me a headache.

You see, I want to display the recordset in a table. When I loop thru the recordset, I compare the previous account with the current account. If they are different, then I write the first month in a row and the first total in the next row in the same column as the month name in the previous row. Granting the account stays the same, I would like to write the next month in the same row as the first month and the next total in the same row as the first total and so on.

With my asp code, I get it like this:

Account 1111111
Jan
124.34
Feb
1256.00
Account 2222222
Mar
100.00
Account 3333333
Apr
123.45
Dec
800.89

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-03-17 : 13:45:28

This might help. It's not perfect, but closer to what you want.

If you could count the number of months for each account, and put that in each row, that could tell you what your COLSPAN needs to be for the account cell.

 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
	<head>
		<title></title>
	</head>
	<body>
	
		<!-- First record Start-->
		<table border="1" bordercolor="#000000" cellpadding="0" cellspacing="0">
			<tr>
				<td>Account 1111111</td>
			</tr>
			<tr>
				<td>
					<table>
						<tr>
							<td>Jan</td>
						</tr>
						<tr>
							<td>124.34</td>
						</tr>
					</table>
				</td>
				<!-- First Record End -->
				<!-- Second Record Start -->
				<td>
					<table ID="Table1">
						<tr>
							<td>Feb</td>
						</tr>
						<tr>
							<td>1256.00</td>
						</tr>
					</table>
				</td>
				<!-- Second Record End -->
				<!-- Third Record Start  
			Account Changed, so write a close row
			and the a new account header 
			-->
			</tr>
			<tr>
				<td>Account 2222222</td>
			</tr>
			<tr>
				<td>
					<table ID="Table2">
						<tr>
							<td>Mar</td>
						</tr>
						<tr>
							<td>100.00</td>
						</tr>
					</table>
				</td>
				<!-- Third Record End -->
				<!-- When you hit the last record, 
		close the last row and close the table
		-->
			</tr>
		</table>
	</body>
</html>

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-17 : 13:46:05

Do this in ASP:

Set up a table with enough columns to accomidate what you will need.

Create two string variables: Months and Values, intialized both to ""

for each record in your recordset,

When you get a new account do the following:
* if Months<> "", response.write "<TR>" & Months & "</TR>"
* if Values <> "", response.write "<TR>" & Values & "</TR>"
* response.write out the account header, spanning all columns
* Months = ""
* Days = ""

Then,
Months = "<TD>" & recordset("Month") & "</TD>"
Value = "<TD>" & recordset("Value") & "</TD>"

recordset.movenext

loop until eof

response.write "<TR>" & months & "</TR>"
response.write "<TR>" & Values & "</TR>"

respone.write table footer info

close the recordset.

- Jeff

PeterG
Posting Yak Master

156 Posts

Posted - 2003-03-17 : 19:31:53

forgot to mention that this must be done in asp.net. i've tried using datagrid, repeater, datalist, datatable to format but to no avail. so maybe i could get sql server to format the data in the way i want

#11111 Jan Dec
#11111 123 234.25
#22222 Feb Mar Apr
#22222 200 200.35 1000
#33333 Jan Feb Mar Apr May Jun Jul
#33333 12 123 234 122 999 228 556

then just display what is returned?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-03-17 : 19:36:41

You could return a dataset or DataReader that had the data in the format that you had in your orignal post. You could then use the loop and output methods as outlined above.

Another solution is to get a reporting package that will do this sort of thing for you. I use Active Reports.net at http://www.datadynamics.com. I've been pretty happy with it. it does have lots of little issues, btu I've not run into any of them yet. For $400, you can get a single developer license.
They have a fully functional demo as well.

I do not represent or get kickbacks from Data Dynamics. I just like their stuff :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-17 : 20:13:31

Peter -- did you see my post? It works and is short and hopefully easy to understand. I don't know about ASP.net specifically, but it should certainly work with no problem.

Instead of adding new rows to the table for each month, you just keep concatenating together two strings -- one for the months, one for the values, surrounded by table detail tags (TD).

Then, when you read a new account#, just print the Months string on one row, then the Values string on another row in the table, and then repeat until done.

- Jeff

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-17 : 20:25:11

quote:

forgot to mention that this must be done in asp.net. i've tried using datagrid, repeater, datalist, datatable to format but to no avail.

Actually it wasn't that you "forgot to mention", you specifically said "Recordset" which is not in .NET but in ADO.

Damian

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-18 : 02:43:59

I dont think there is an easy way out of this problem, you wont be able to simply take a nicely formatted Dataset or SqlDataReader and bind it to a datagrid or something. You are better off writing some *real* code

. Create a server-side table <asp:table> and then populate it from your VB.Net or C# code in the code-behind file, probably in the page load event.

Use the algorithm provided by Michael or Jeff, and use the TableRow and TableCell collections to add rows and columns:

Dim r As New TableRow()
Dim c As New TableCell()
c.Controls.Add(New LiteralControl("Some Text Here"))
r.Cells.Add(c)
Table1.Rows.Add(r)

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources