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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-29 : 10:06:06
|
| 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_GetHTMLListAS SELECT '<OPTION VALUE=''' + CONVERT(VARCHAR(5), FieldID) + '''>' + FieldLabel + '</OPTION>' FROM TableList ORDER BY FieldLabelWith 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
15732 Posts |
Posted - 2001-11-29 : 11:03:39
|
| 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. |
 |
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2001-11-29 : 11:44:59
|
| 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=nothingResponse.Write "</select></td></tr>"cursors are like hammers - sometimes you have to use them, but watch your thumb! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-29 : 12:04:20
|
| Check out this site:www.learnasp.comAnd 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.asphttp://www.learnasp.com/learn/rsfast-table.aspIn 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! |
|
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2001-11-29 : 12:28:04
|
Thanks for the tip. I'm definately going to try it.quote:
Check out this site:www.learnasp.comAnd 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.asphttp://www.learnasp.com/learn/rsfast-table.aspIn 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! |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-11-29 : 17:19:38
|
Hi allHere is another take on it....run this in northwindDeclare @ProductID intSELECT @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 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-11-29 : 18:34:41
|
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 intSELECT @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... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-11-29 : 18:46:35
|
 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 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-11-29 : 18:56:09
|
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... |
|
|
|
|
|
|
|
|
SQL & HTML