Random Function from SQL Server database in ASP .NET

Question

I am creating a webpage which is using asp .net as its backend. I need to grab categories from my database which is SQL Server. I am using this code right now

For Each row00 As DataRow In f_oDatatable(7).Rows
  sCategories += row00.Item("Category").ToString & " / "
Next

If sCategories.Length > 0 Then 
   sCategories = sCategories.Substring(0, sCategories.LastIndexOf(" / "))

CType(Me.FindControl("dbcategories"), HtmlGenericControl).InnerHtml = "Categories: " & sCategories

This code works great and outputs my categories correctly. But what I need to do is have the categories output randomly. (i.e: "one/ two/ three/ four/ five" - and then another time - "two/ five/ one/ three/ four")

I can not have it create a random string in the database b/c of other dependencies for the categories to be the same every time.

Any help would be greatly appreciated. Thank you

Answer 1

in your first for loop, instead of concatenating to sCategories, add each category name a collection of your choosing. shuffle the entries with the Fisher-Yates algorithm, then loop through your collection to build the final string.

edit: a previous revision of the wiki page for the shuffle has a vb.net implementation. here you go.

http://en.wikipedia.org/w/index.php?title=Fisher%E2%80%93Yates_shuffle&diff=347101177&oldid=347084753

more edits: for a small number of options (5 qualifies), make a new table and store every permutation of those 5 names in it. when the time comes, pick a random row.

Answer 2

You probably load the datatable with a command that's something like this:

SELECT * FROM CATEGORY

If so, this will return them in random order:

SELECT * FROM CATEGORY ORDER BY NEWID()