Four random dataset records!

Hey there guys,

I have a Access databases. I have a asp.net page that has a datalist that lists four reocrds from the database. Currently it is displaying the first four obviousley.

I need to randomize the select statement of dataset to display random records....

this is the dataset currently:
------------------------------------------------------------------------------------------------
<MM:DataSet
id="TDGSpecials"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_TDGDBConn") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_TDGDBConn") %>'
CommandText='<%# "SELECT *  FROM TDGSpecials   WHERE RND(ID)" %>'
Debug="true" PageSize="4" CurrentPage='<%# IIf((Request.QueryString("TDGSpecials_CurrentPage") <> Nothing), Request.QueryString("TDGSpecials_CurrentPage"), 0)  %>'
></MM:DataSet>
------------------------------------------------------------------------------------------------

Any help or sample code would be appreciated....

Thanks,

Nugs
LVL 2
NugsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brandonh6kCommented:
Check this link out.  

http://authors.aspalliance.com/stevesmith/articles/RandomizeDataset.aspx

Since you're using Access for the back end, you can't use the ORDER BY Newid() trick that SQL Server has.

HTH,

Brandon
0
NugsAuthor Commented:
Ahhh, that explains allot with the newid() thing....

Not quite sure if the code from ASPAlliance makes much sence to me... is there some example code i can see in context?

Nugs
0
brandonh6kCommented:
Pseudocode would look something like this... I think... :)  I think in C# so you might need to convert this to VB if that's what you're using.

In the script section (or codebehind):

Open SQL connection
Populate TDGSpecials dataset.
Get the dataTable from TDGSpecials. - something like DataTable dt = TDGSpecials.Tables["TDGSpecials"];
Pass the dataTable into the shuffle function - dt = shuffleTable(dt);
Then get the first 4 rows from the table and do whatever with them.

If you need something more than that, I can throw something together, but it'll be later tonight or this weekend.

Brandon
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

NugsAuthor Commented:
Well i did find this on the internet:

----------------------------------------------------------------------
'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Function Randomizer () As Integer    
Static AlreadyDone As Integer
          If AlreadyDone = False Then Randomize : AlreadyDone = True    
      Randomizer = 0
End Function
'************ Code End *************
Now to get 100 questions picked at random:

select top 100 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)
-------------------------------------------------------------------------------------------------

I'm not sure how to implement this into the code of my page... Where does the function go? Between script tags where the other scripts lie?

I get erros when doing that. The SQL can't find the randomizer function....  I think i am implementing it wrong!

Nugs
0
brandonh6kCommented:
That's a VB function.  It should go in your script tags.  I think you'll need to build the SQL string something like this:

Dim sql as String

sql = "select top 100 mytable.*from mytable"
sql = sql & " where " & randomizer() & "= 0"
sql = sql & "order by rnd(isnull(mytable.question) * 0 + 1)"

And then pass it to whatever is connecting to your dataset.  I'm not quite sure what exactly it accomplishes though...  Does the rnd function exist in Access SQL?

Or now that I look at it some more, I wonder if this is all supposed to be VBA stuff and pushed down into the DB somewhere...?

Brandon
0
NugsAuthor Commented:
Brandon,

This is what i have currently, If you can do me a favor and take a look at it and tell me how to get it working.... I'm not sure what to do here....

---------------------------------------------------------------------------------------------------------------------------------------------------------
<MM:DataSet
id="TDGSpecials"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_TDGDBConn") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_TDGDBConn") %>'
CommandText='<%# "SELECT *  FROM TDGSpecials   WHERE RND(ID)" %>'
Debug="true" PageSize="4" CurrentPage='<%# IIf((Request.QueryString("TDGSpecials_CurrentPage") <> Nothing), Request.QueryString("TDGSpecials_CurrentPage"), 0)  %>'
></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />


<script language="vb">
Dim sql as String

sql = "select top 4 TDGSpecials.*from TDGSpecials"
sql = sql & " where " & randomizer() & "= 0"
sql = sql & "order by rnd(isnull(TDGSpecials.question) * 0 + 1)"
</script>
---------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks in advance!

Nugs
0
neu-rahCommented:
"SELECT *  FROM TDGSpecials   WHERE ID=RND(ID) OR ID=RND(ID) OR ID=RND(ID) OR ID=RND(ID)"

i would put that RND() part into my code so i can guarantee that no 2 euqual choices are made, never used that RND(x) on SQL, as i can read on the web its not a RANDOM but a ROUND function

"SELECT *  FROM TDGSpecials   WHERE ID="&randomizer()&" OR ID="&randomizer()&" OR ID= OR ID="&randomizer()&" OR ID=""&randomizer()&";"

0
brandonh6kCommented:
Ack... I'm thinking we huck the whole thing out the window and start from scratch.  Try this (all off the top of my head, probably won't compile right off):

[[aircode]]
<script runat="server">
        public static DataTable shuffleTable(DataTable inputTable)
        {
            return shuffleTable(inputTable, inputTable.Rows.Count*3);
        }

        public static DataTable shuffleTable(DataTable inputTable, int shuffleIterations)
        {
            int index;
            System.Random rnd = new Random();
            // Remove and throw to the end random rows until we have done so n*3 times (shuffles the dataset)
            for(int i = 0; i < shuffleIterations; i++)
            {
                index = rnd.Next(0,inputTable.Rows.Count-1);
                inputTable.Rows.Add(inputTable.Rows[index].ItemArray);
                inputTable.Rows.RemoveAt(index);
            }
            return inputTable;
        }

        public void Page_Load()
        {
              SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_TDGDBConn"));
              objConn.Open();
              string sql = "SELECT *  FROM TDGSpecials"
              SqlCommand objComm = new SqlCommand(sql, objConn);
              SqlDataAdapter da = new SqlDataAdapter(objComm);
              DataSet ds = new DataSet();
              da.Fill(ds, "TDGSpecials");

              // At this point we have a dataset filled with the records from TDGSpecials
              shuffleTable(ds.Tables["TDGSpecials"]);

              // Filter down to 4 records
              DataRowCollection drc = ds.Tables["TDGSpecials"].Rows;
              for (int i = (drc.Count -1), i > 3, i--)
              {
                   // Get rid of the last row in the table.  Repeat until there are 4 left.
                   drc.RemoveAt(i);
              }

              // Now bind the dataset to whatever you want to display it.


        }
</script>
[[/aircode]]

You'll probably have to poke at this a bit to get it to work correctly.  I didn't write any of it in VS or DW.

Brandon
0
NugsAuthor Commented:
Compiler Error Message: BC30235: 'Static' is not valid on a member variable declaration.

Source Error:

Line 8:  
Line 9:  <script runat="server">
----> Line 10:         public static DataTable shuffleTable(DataTable inputTable)
Line 11:         {
Line 12:             return shuffleTable(inputTable, inputTable.Rows.Count*3);
 
-------------------------------------------------------------------------

I'm connfused as to how to pass this to my dataset or do i just scrap the dataset alltogether?

Nugs
0
brandonh6kCommented:
Just rip out the static keyword.  You would normally use that in a utility library.

FYI, this is C#, so if you're using VB.NET, you'll need to convert either the page to C# or change the code to VB.

You don't need your dataset.  The code above generates a normal (non-MM) dataset that you can then bind to a datagrid or repeater control or whatever.

Brandon

0
NugsAuthor Commented:
Oh man..... I didn't realize it was C#, i can convert the page.... Don't suppose you have any VB.NET versions in that head of yours? :)
0
NugsAuthor Commented:
... i CAN'T convert the page .... Typo...
0
brandonh6kCommented:
I'll convert it this weekend.  Don't have time right now... :)

Brandon
0
NugsAuthor Commented:
That would be awesome... I would be very happy about that... I would give your a 1000000 points if i could...

Nugs
0
NugsAuthor Commented:
Any progress brandon?
0
brandonh6kCommented:
I'm sorry, Nugs.  I totally spaced this out this weekend.  I'll do it tonight.
0
NugsAuthor Commented:
np, thanks for your help!
0
NugsAuthor Commented:
Brandon,

Man i hate to bug you... Should i post a new topic regarding the conversion?

Nugs
0
brandonh6kCommented:
Sorry about that.  Try this on for size... :)

<code>

  Public Function ShuffleTable(ByVal inputTable As DataTable) As DataTable
    Return ShuffleTable(inputTable, inputTable.Rows.Count * 3)
  End Function

  Public Function ShuffleTable(ByVal inputTable As DataTable, ByVal shuffleIterations As Integer) As DataTable
    Dim index As Integer
    Dim rnd As System.Random
    rnd = New Random
    For i As Integer = 0 To shuffleIterations
      index = rnd.Next(0, inputTable.Rows.Count - 1)
      inputTable.Rows.Add(inputTable.Rows(index))
      inputTable.Rows.RemoveAt(index)
    Next
    Return inputTable
  End Function


  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Dim objConn As New SqlConnection
    objConn.Open()
    Dim sql As String
    sql = "SELECT *  FROM TDGSpecials"
    Dim objComm As New SqlCommand(sql, objConn)
    Dim da As New SqlDataAdapter(objComm)
    Dim ds As New DataSet
    da.Fill(ds, "TDGSpecials")

    ShuffleTable(ds.Tables("TDGSpecials"))

    Dim drc As DataRowCollection
    drc = ds.Tables("TDGSpecials").Rows

    For i As Integer = drc.Count - 1 To 3 Step -1
      drc.RemoveAt(i)
    Next

    ' Now bind the dataset to whatever control.  something like this...
    ' datagrid.DataSource = ds
    ' datagrid.DataBind()



  End Sub

</code>

Brandon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NugsAuthor Commented:
:) Thanks i got a few error, maybe it is my fault though:

<!-- --------------------------------------------------------------Randomizing Script------------------------------------------------------------- -->
<script language="vb" runat="server">

  Public Function ShuffleTable(ByVal inputTable As DataTable) As DataTable
    Return ShuffleTable(inputTable, inputTable.Rows.Count * 3)
  End Function

  Public Function ShuffleTable(ByVal inputTable As DataTable, ByVal shuffleIterations As Integer) As DataTable
    Dim index As Integer
    Dim rnd As System.Random
    rnd = New Random
    For i As Integer = 0 To shuffleIterations
      index = rnd.Next(0, inputTable.Rows.Count - 1)
      inputTable.Rows.Add(inputTable.Rows(index))
      inputTable.Rows.RemoveAt(index)
    Next
    Return inputTable
  End Function


  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Dim objConn As New SqlConnection
    objConn.Open()
    Dim sql As String
    sql = "SELECT *  FROM TBL_Specials"
    Dim objComm As New SqlCommand(sql, objConn)
    Dim da As New SqlDataAdapter(objComm)
    Dim ds As New DataSet
    da.Fill(ds, "TBL_Specials")

    ShuffleTable(ds.Tables("TBL_Specials"))

    Dim drc As DataRowCollection
    drc = ds.Tables("TBL_Specials").Rows

    For i As Integer = drc.Count - 1 To 3 Step -1
      drc.RemoveAt(i)
    Next

    ' Now bind the dataset to whatever control.  something like this...
    ' datagrid.DataSource = ds
    ' datagrid.DataBind()



  End Sub

</script>
<!-- --------------------------------------------------------------------------------------------------------------------------------------------- -->

And i tried calling the DS like you said but no go...

------------------------------------------------------------------------
<asp:DataList
                      backcolor="White"
                      borderwidth="0px"
                      cellpadding="0"
                      cellspacing="0" DataSource="<%# TBL_Specials.DefaultView %>"
                      gridlines="Vertical" id="RandSpecials"
                      RepeatColumns="4" RepeatLayout="Table"
            RepeatDirection="Horizontal"
                      runat="server"
                      showfooter="False"
                      showheader="False"
                      width="600">
 <itemTemplate>
....
-----------------------------------------------------------------------------


I get this error:


---------------------------------------------------------------------------
Compiler Error Message: BC30002: Type 'DataTable' is not defined.

Source Error:

 

Line 9:  <script language="vb" runat="server">
Line 10:
--->Line 11:   Public Function ShuffleTable(ByVal inputTable As DataTable) As DataTable
Line 12:     Return ShuffleTable(inputTable, inputTable.Rows.Count * 3)
Line 13:   End Function
---------------------------------------------------------------------------
0
brandonh6kCommented:
Sounds like you need to import System.Data. Put something like this at the top of the page.

<%@ Import Namespace = "System.Data" %>

You may also need the SqlClient namespace...

<%@ Import Namespace = "System.Data.SQLClient" %>

Brandon
0
NugsAuthor Commented:
Keep in mind this is a Access DB!
0
NugsAuthor Commented:
Are those namespaces not exclusivly for MS SQL Server?
0
NugsAuthor Commented:
Compiler Error Message: BC30561: 'DataSet' is ambiguous, imported from the namespaces or types 'System.Data, DreamweaverCtrls'.

Source Error:

 

Line 38:     Dim objComm As New SqlCommand(sql, objConn)
Line 39:     Dim da As New SqlDataAdapter(objComm)
-->Line 40:     Dim ds As New DataSet
Line 41:     da.Fill(ds, "TBL_Specials")
Line 42:
 
0
brandonh6kCommented:
Make line 40 into:

Dim ds as New System.Data.DataSet
0
NugsAuthor Commented:
Ok that seemed to do it but i get a error in my datalist... How do i call the Dataset?
0
NugsAuthor Commented:
Here is the error:

Parser Error Message: The 'DataSource' property can only be set programmatically. It cannot be declared.

Source Error:


Line 220:                      <td colspan="4">
Line 221:                               
-->Line 222:                                <asp:DataList
Line 223:                      backcolor="White"
Line 224:                      borderwidth="0px"
 
0
brandonh6kCommented:
Add an ID property to the datalist, like so:

<asp:DataList
backcolor="White"
borderwidth="0px"
ID="dlFourRandom"
...


And in the bottom of the Page_Load Sub, add in these two lines:

dlFourRandom.DataSource = ds
dlFourRandom.DataBind()

Brandon
0
NugsAuthor Commented:
Ok....

------------------------------------------------------------------------------
  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Dim objConn As New SqlConnection
    objConn.Open()
    Dim sql As String
    sql = "SELECT *  FROM TBL_Specials"
    Dim objComm As New SqlCommand(sql, objConn)
    Dim da As New SqlDataAdapter(objComm)
      Dim ds as New System.Data.DataSet
    da.Fill(ds, "TBL_Specials")

    ShuffleTable(ds.Tables("TBL_Specials"))

    Dim drc As DataRowCollection
    drc = ds.Tables("TBL_Specials").Rows

    For i As Integer = drc.Count - 1 To 3 Step -1
      drc.RemoveAt(i)
    Next
      
      dlFourRandom.DataSource = ds
      dlFourRandom.DataBind()

    ' Now bind the dataset to whatever control.  something like this...
    ' datagrid.DataSource = ds
    ' datagrid.DataBind()



  End Sub
------------------------------------------------------------------------------



------------------------------------------------------------------------------
             <asp:DataList
            ID="dlFourRandom"
                      backcolor="White"
                      borderwidth="0px"
                      cellpadding="0"
                      cellspacing="0" DataSource=ds
                      gridlines="Vertical" id="RandSpecials"
                      RepeatColumns="4" RepeatLayout="Table"
            RepeatDirection="Horizontal"
                      runat="server"
                      showfooter="False"
                      showheader="False"
                      width="600">
                   <itemTemplate>
------------------------------------------------------------------------------


Here is the error... The same....
------------------------------------------------------------------------------
Parser Error Message: The 'DataSource' property can only be set programmatically. It cannot be declared.

Source Error:


Line 223:                      <td colspan="4">
Line 224:                               
-->Line 225:                                <asp:DataList
Line 226:                                ID="dlFourRandom"
Line 227:                      backcolor="White"
------------------------------------------------------------------------------
0
NugsAuthor Commented:
Oh... My bad... i forgot to take out my old ID tage...

SO!!!!

That all seems to be working.... we will see now how do i call dataset field values:

I was doing this:

<%# TDGSpecials.FieldValue("Fld_Img", Container) %>

I get a erro with that now!
0
brandonh6kCommented:
Get rid of the DataSource attribute in the DataList.  You're setting it in Page_Load.

Brandon
0
NugsAuthor Commented:
yup i got rid of both the datasource and the old id attribute...
0
brandonh6kCommented:
Use this:

<%# DataBinder.Eval(Container.DataItem, "Fld_Img") %>
0
NugsAuthor Commented:
Ok that worked... but i get a error back up in the script:

Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.

Source Error:


Line 33:     'Put user code to initialize the page here
Line 34:     Dim objConn As New SqlConnection
-->Line 35:     objConn.Open()
Line 36:     Dim sql As String
Line 37:     sql = "SELECT *  FROM TBL_Specials"
 
0
NugsAuthor Commented:
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_TDGDBConn") %>'
0
NugsAuthor Commented:
Something like this right?

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\TDG\db\TDGDB.mdb;Persist Security Info=False

?

0
brandonh6kCommented:
Yeah.  That looks about right.  Just add that string in as a parameter to the SqlConnection

Dim objConn As New SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\TDG\db\TDGDB.mdb;Persist Security Info=False")
0
NugsAuthor Commented:
Gees....

-----------------------------

Exception Details: System.ArgumentException: Keyword not supported: 'provider'.

Source Error:


Line 31:   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 32:
-->Line 33:     Dim objConn As New SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\TDG\db\TDGDB.mdb;Persist Security Info=False")
Line 34:     objConn.Open()
Line 35:     Dim sql As String
 
------------------------
0
brandonh6kCommented:
Ok, duh.  I'm so used to working with SQL Server, I totally put the SQL stuff in without thinking... :P  And skipped your question about it being SQL specific... so I wasted another day worth of questions.

Anyhow, easy enough to fix...  The connection string is right, we just have the wrong objects...  Change anything that says Sql to OleDb (i.e. SqlConnection -> OleDbConnection) and I think that will fix it.  I'm pretty sure that all of the objects in System.Data.SqlClient have a counterpart in System.Data.OleDbClient.  Make sure you change the namespace import.

Brandon

0
NugsAuthor Commented:
<!-- --------------------------------------------------------------Randomizing Script------------------------------------------------------------- -->
<script language="vb" runat="server">

  Public Function ShuffleTable(ByVal inputTable As DataTable) As DataTable
    Return ShuffleTable(inputTable, inputTable.Rows.Count * 3)
  End Function

  Public Function ShuffleTable(ByVal inputTable As DataTable, ByVal shuffleIterations As Integer) As DataTable
    Dim index As Integer
    Dim rnd As System.Random
    rnd = New Random
    For i As Integer = 0 To shuffleIterations
      index = rnd.Next(0, inputTable.Rows.Count - 1)
      inputTable.Rows.Add(inputTable.Rows(index))
      inputTable.Rows.RemoveAt(index)
    Next
    Return inputTable
  End Function


  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\TDG\db\TDGDB.mdb;Persist Security Info=False")
    objConn.Open()
    Dim sql As String
    sql = "SELECT *  FROM TBL_Specials"
    Dim objComm As New OleDbCommand(sql, objConn)
    Dim da As New OleDbDataAdapter(objComm)
      Dim ds as New System.Data.DataSet
    da.Fill(ds, "TBL_Specials")

    ShuffleTable(ds.Tables("TBL_Specials"))

    Dim drc As DataRowCollection
    drc = ds.Tables("TBL_Specials").Rows

    For i As Integer = drc.Count - 1 To 3 Step -1
      drc.RemoveAt(i)
    Next
      
      dlFourRandom.DataSource = ds
      dlFourRandom.DataBind()
  End Sub

</script>
<!-- --------------------------------------------------------------------------------------------------------------------------------------------- -->


/////////////////////////////////////////////////////////////////////////////////////////////////////
Compiler Error Message: BC30002: Type 'OleDbConnection' is not defined.

Source Error:

 

Line 31:   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 32:
-->Line 33:     Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\TDG\db\TDGDB.mdb;Persist Security Info=False")
Line 34:     objConn.Open()
Line 35:     Dim sql As String
/////////////////////////////////////////////////////////////////////////////////////////////////////

<%@ Import Namespace="System.Data.OleDbClient" %>

Did i do something wrong?
0
NugsAuthor Commented:
Oh

<%@ Import Namespace="System.Data.OleDbClient" %>

change to:

<%@ Import Namespace="System.Data.OleDb" %>


I now get this error:

-----------------------------------------------------------------------------------------------------------------------
Exception Details: System.ArgumentException: This row already belongs to this table.

Source Error:


Line 22:     For i As Integer = 0 To shuffleIterations
Line 23:       index = rnd.Next(0, inputTable.Rows.Count - 1)
-->Line 24:       inputTable.Rows.Add(inputTable.Rows(index))
Line 25:       inputTable.Rows.RemoveAt(index)
Line 26:     Next
-----------------------------------------------------------------------------------------------------------------------
0
brandonh6kCommented:
Try changing line 24 to this:

inputTable.Rows.Add(inputTable.Rows(index).ItemArray)

I just added .ItemArray

Brandon
0
NugsAuthor Commented:
Brandon, you are the best... Sorry this was such a long involved project but i appreciate all your help... It is working a working beautifully...

Here is the final working code for all you others out there:

-----------------------------------------------------------------------------------------------------------
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

...

<script language="vb" runat="server">

  Public Function ShuffleTable(ByVal inputTable As DataTable) As DataTable
    Return ShuffleTable(inputTable, inputTable.Rows.Count * 3)
  End Function

  Public Function ShuffleTable(ByVal inputTable As DataTable, ByVal shuffleIterations As Integer) As DataTable
    Dim index As Integer
    Dim rnd As System.Random
    rnd = New Random
    For i As Integer = 0 To shuffleIterations
      index = rnd.Next(0, inputTable.Rows.Count - 1)
      inputTable.Rows.Add(inputTable.Rows(index).ItemArray)
      inputTable.Rows.RemoveAt(index)
    Next
    Return inputTable
  End Function


  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\TDG\db\TDGDB.mdb;Persist Security Info=False")
    objConn.Open()
    Dim sql As String
    sql = "SELECT *  FROM TBL_Specials"
    Dim objComm As New OleDbCommand(sql, objConn)
    Dim da As New OleDbDataAdapter(objComm)
      Dim ds as New System.Data.DataSet
    da.Fill(ds, "TBL_Specials")

    ShuffleTable(ds.Tables("TBL_Specials"))

    Dim drc As DataRowCollection
    drc = ds.Tables("TBL_Specials").Rows

    For i As Integer = drc.Count - 1 To 3 Step -1
      drc.RemoveAt(i)
    Next
      
      dlFourRandom.DataSource = ds
      dlFourRandom.DataBind()
  End Sub

</script>
-----------------------------------------------------------------------------------------------------------

Thanks again!

Nugs

PS: if you feel like spending any more time on this, run through a quick explanation for me... But no big deal i will look at the code closley and try and figure out what it is doing...
0
brandonh6kCommented:
Here's the rundown.  All the guts of this is in Page_Load.  The ShuffleTables are just utility functions.  Normally, you'd want to put those into an external class library (DLL).  ShuffleTable generates a number between 0 and number of rows - 1 (the row indexer starts at 0).  Using that number, it copies the row to the end of the table and then removes the original version.  The default number of shuffles is Number of Rows * 3.

Inside Page_Load, we open a connection to the DB, generate a Command object and pass it the SQL and Connection.  The DataAdapter just allows you to take the results from the Command object and put it in the dataset (the da.Fill line).  Once we've stuffed the data into the dataset, we pass it through the ShuffleTable function.  Since datasets can have multiple tables of data, we have to specify which table is being shuffled.  The DataRowCollection is a way to be able to touch individual rows of data in the DB.  I didn't see a way to truncate the table down to 4 rows, so we just use the time honored method of brute force... delete rows until we get to 4.  We delete off the end to avoid index out of bounds errors and the like.

Once we're down to 4 rows, we just assign the dataset (note that all of the data manipulations we've been doing are going on inside the dataset (ds).  I just find it easier to pull things out to their own variables rather than always using the fully qualified ds.Tables... notation).  Any data-aware .NET control can take a dataset as it's datasource, so we simply assign the dataset to the DataList's datasource and call DataBind.  DataBind tells the control to get the data out of whatever container and do what it's supposed to with it.

Check out aspnet.4guysfromrolla.com.  There's a lot of good information on databinding and the like on there.  Plus most of the code is in VB.Net, so that should be helpful as well.

Hope this helps.

Brandon
0
NugsAuthor Commented:
Cool, make sense... you have made me smarter! Thanks, hope i will see you around in other questions... I post loads of them :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.