?
Solved

Four random dataset records!

Posted on 2005-03-04
45
Medium Priority
?
584 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:Nugs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 27
  • 17
45 Comments
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13462352
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
 
LVL 2

Author Comment

by:Nugs
ID: 13462486
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13462592
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
WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

 
LVL 2

Author Comment

by:Nugs
ID: 13462710
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13466658
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
 
LVL 2

Author Comment

by:Nugs
ID: 13560291
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
 
LVL 1

Expert Comment

by:neu-rah
ID: 13573664
"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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13574704
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
 
LVL 2

Author Comment

by:Nugs
ID: 13577493
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13579042
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
 
LVL 2

Author Comment

by:Nugs
ID: 13579089
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
 
LVL 2

Author Comment

by:Nugs
ID: 13579095
... i CAN'T convert the page .... Typo...
0
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13579105
I'll convert it this weekend.  Don't have time right now... :)

Brandon
0
 
LVL 2

Author Comment

by:Nugs
ID: 13579152
That would be awesome... I would be very happy about that... I would give your a 1000000 points if i could...

Nugs
0
 
LVL 2

Author Comment

by:Nugs
ID: 13592756
Any progress brandon?
0
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13593873
I'm sorry, Nugs.  I totally spaced this out this weekend.  I'll do it tonight.
0
 
LVL 2

Author Comment

by:Nugs
ID: 13594241
np, thanks for your help!
0
 
LVL 2

Author Comment

by:Nugs
ID: 13604290
Brandon,

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

Nugs
0
 
LVL 3

Accepted Solution

by:
brandonh6k earned 2000 total points
ID: 13606203
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
 
LVL 2

Author Comment

by:Nugs
ID: 13606276
:) 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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13606555
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
 
LVL 2

Author Comment

by:Nugs
ID: 13606573
Keep in mind this is a Access DB!
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606585
Are those namespaces not exclusivly for MS SQL Server?
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606593
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13606628
Make line 40 into:

Dim ds as New System.Data.DataSet
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606659
Ok that seemed to do it but i get a error in my datalist... How do i call the Dataset?
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606665
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13606709
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
 
LVL 2

Author Comment

by:Nugs
ID: 13606750
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
 
LVL 2

Author Comment

by:Nugs
ID: 13606767
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13606768
Get rid of the DataSource attribute in the DataList.  You're setting it in Page_Load.

Brandon
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606792
yup i got rid of both the datasource and the old id attribute...
0
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13606800
Use this:

<%# DataBinder.Eval(Container.DataItem, "Fld_Img") %>
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606866
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
 
LVL 2

Author Comment

by:Nugs
ID: 13606885
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_TDGDBConn") %>'
0
 
LVL 2

Author Comment

by:Nugs
ID: 13606963
Something like this right?

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

?

0
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13607020
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
 
LVL 2

Author Comment

by:Nugs
ID: 13607055
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13608198
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
 
LVL 2

Author Comment

by:Nugs
ID: 13613535
<!-- --------------------------------------------------------------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
 
LVL 2

Author Comment

by:Nugs
ID: 13613606
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13614377
Try changing line 24 to this:

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

I just added .ItemArray

Brandon
0
 
LVL 2

Author Comment

by:Nugs
ID: 13614488
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
 
LVL 3

Expert Comment

by:brandonh6k
ID: 13614920
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
 
LVL 2

Author Comment

by:Nugs
ID: 13615005
Cool, make sense... you have made me smarter! Thanks, hope i will see you around in other questions... I post loads of them :)
0

Featured Post

Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question