SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL & HTML
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/29/2001 :  10:06:06  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Dave writes "Actually, I don't really have a question, although I can coach it in such a way...

What have you found to be the most efficient OR fastest method for building html lists or tables using SQL and/or ado?
My answer is that I incorporate the html element tags into the sql stored proc:
example:
<PRE style="color:green">
CREATE PROC sp_GetHTMLList
AS
SELECT '<OPTION VALUE=''' + CONVERT(VARCHAR(5), FieldID)
+ '''>' + FieldLabel + '</OPTION>'
FROM TableList
ORDER BY FieldLabel

With this type of statement, I can simple use a recordset.getString call to return long lists or tables.

Do you know of a faster way?"

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/29/2001 :  11:03:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
I use GetString() with the appropriate tags most of the time. Option lists are tricky, and I use EXACTLY the same thing you described for them! I don't see any performance problems including the HTML in the query.

GetString with tags works beautifully on HTML tables, basically it works where you have a lot of columns and rows that need to be delimited the same way. To me it's more flexible; I don't have to write a custom procedure or query just to format it as HTML.

As long as you stay away from the dreaded While Not rs.EOF...rs.MoveNext...Wend to build your HTML you should do pretty well.

Go to Top of Page

lfmn
Posting Yak Master

USA
141 Posts

Posted - 11/29/2001 :  11:44:59  Show Profile  Reply with Quote
I use the following code to build lists and tables, but I would be interested to know why robvolk doesn't like the Do While loop. Maybe I've been lucky all these years, but I've never had a problem with it.

sql1 = "select distinct TypeMusic from fndBand(nolock)"
set oRS1 = oConn.Execute(sql1)

Response.Write "<tr><td align=right><b>Genre: </b></td><td><select name=Genre>"
Do While NOT oRS1.EOF
Response.Write "<OPTION VALUE='" & oRS1("TypeMusic") & "'>"
Response.Write oRS1("TypeMusic") & "</OPTION>"
oRS1.MoveNext
Loop
oRS1.Close
Set oRS1=nothing
Response.Write "</select></td></tr>"

cursors are like hammers - sometimes you have to use them, but watch your thumb!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/29/2001 :  12:04:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
Check out this site:

www.learnasp.com

And look for anything on GetString and GetRows. He has a bunch of examples and experiments that show how much of an improvement they make. Here's a few:

http://www.learnasp.com/learn/getrowsultimate.asp
http://www.learnasp.com/learn/rsfast-table.asp

In your example, if you're only talking about 20-30 maximum rows for your option lists, then you probably won't seem a big improvement. If you are building 100, 200, 1000+ row tables with Do...While...rs.MoveNext, trust me, GetString will BLOW THE DOORS off of your current code.

I have web pages that pull up 1200 row (x 10 columns) tables in 10 seconds or less, using GetString, and most of that is HTML rendering time, not data transfer time. They would probably time out using a loop. And a lot of timeout problems that people post here are due to a While...rs.MoveNext loop. Also, you can perform a single response.Write of the string that GetString generates, instead of hundreds. I also get beautiful formatting when I combine it with some CSS.

Like I said, GetString is tricky to use with OPTION lists, and your method or Dave's is better. But you really can't beat GetString when building HTML tables. Test it out if you have any big tables and see what happens. I'm pretty sure you'll like it!

Go to Top of Page

lfmn
Posting Yak Master

USA
141 Posts

Posted - 11/29/2001 :  12:28:04  Show Profile  Reply with Quote
Thanks for the tip. I'm definately going to try it.

quote:

Check out this site:

www.learnasp.com

And look for anything on GetString and GetRows. He has a bunch of examples and experiments that show how much of an improvement they make. Here's a few:

http://www.learnasp.com/learn/getrowsultimate.asp
http://www.learnasp.com/learn/rsfast-table.asp

In your example, if you're only talking about 20-30 maximum rows for your option lists, then you probably won't seem a big improvement. If you are building 100, 200, 1000+ row tables with Do...While...rs.MoveNext, trust me, GetString will BLOW THE DOORS off of your current code.

I have web pages that pull up 1200 row (x 10 columns) tables in 10 seconds or less, using GetString, and most of that is HTML rendering time, not data transfer time. They would probably time out using a loop. And a lot of timeout problems that people post here are due to a While...rs.MoveNext loop. Also, you can perform a single response.Write of the string that GetString generates, instead of hundreds. I also get beautiful formatting when I combine it with some CSS.

Like I said, GetString is tricky to use with OPTION lists, and your method or Dave's is better. But you really can't beat GetString when building HTML tables. Test it out if you have any big tables and see what happens. I'm pretty sure you'll like it!





cursors are like hammers - sometimes you have to use them, but watch your thumb!
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/29/2001 :  17:19:38  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi all

Here is another take on it....run this in northwind


Declare @ProductID int
SELECT @ProductID = 3 --or any product you want selected

select
1 as tag,
Null as parent,
ProductID as [option!1!value],
Case ProductID
WHEN @ProductID THEN 'SELECTED'
ELSE NULL END as [option!1!selected],
ProductName as [option!1!]

from Products
order by ProductName

FOR XML EXPLICIT






Damian
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 11/29/2001 :  18:34:41  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Damian, I was going to say, "You're my HERO!" because I was looking for a way to do this and get the appropriate SELECTED option. But then I realized that I have SQL 7, not 2000, and SQL 7 apparently doesn't understand FOR XML.

So then, after brooding for a few moments, I decided, "Hey, I'm an Aged Yak Warrior, I bet I can modify his code!" So I did, and here it is...

Declare @ProductID int
SELECT @ProductID = 3 --or any product you want selected
select '<option value="' + CAST(ProductID as varchar(5)) + Case ProductID
WHEN @ProductID THEN '" SELECTED'
ELSE '"'
END + '>' + ProductName + '</option>' as ListText,
ProductName as [option!1!]
from Products
order by ProductName


And so, now I can say, Damian, You're my HERO!

-------------------
It's a SQL thing...
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/29/2001 :  18:46:35  Show Profile  Visit Merkin's Homepage  Reply with Quote


Yeah I should have specified SQL 2000 for the XML bit. But yep, that way works too. The advantage of the XML is it returns it as one string and you don't need to do any looping.
Also you don't need the

ProductName as [option!1!]

bit in yours, you have everything you need in one column.







Damian
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 11/29/2001 :  18:56:09  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
quote:
Also you don't need the

ProductName as [option!1!]

bit in yours, you have everything you need in one column.



Oops! I was so excited that it worked, I forgot to take that out after my last test. Thanks.

Now, with everything in one field, can't you just do an RS.GetString on it and write it out?

-------------------
It's a SQL thing...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000