random selection of database entries

[ariez88]

hello,
can anyone tell me how to randomly pick entries from a database using ASP.NET as front end and SQL SERVER as backend ?

i have written
select * from table order by rand();

but instead of picking entries randomly, it simply displays the entries in the same order as they are stored in the database..

thanks alot.

[Ramesh S]

Try this

SELECT TOP 100  *
FROM Your_Table_Name
ORDER BY NEWID()

This will pick 100 records randomly from a SQL Server 2005 table.

[ariez88]

thankyou very much.. there seems to be a problem with SQL at my home so i was unable to try it yet..I'll try it in the uni the next time i go there.
another problem tht i m having is in this code..

protected void loginButton_Click1(object sender, EventArgs e)
   {
        DataSet temp = new DataSet();
        temp = conn.retrieve("select * from candidate_info");
        int i=0;
        int a = temp.Tables[0].Rows.Count;
        //object obj = new object();

        object[] obj;

        while( i < a)
        {
            obj = temp.Tables[0].Rows[i].ItemArray;
           
                if (this.idTextBox.Text != obj[0].ToString() && this.passTextBox.Text != obj[1].ToString())
                    i++;

        }   
        if (i == a)
            Response.Write("<script>alert('ERROR')</script>");  
        else
            
       Response.Write("<script>alert('Logged In')</script>");
       
}

this is the piece of code tht I have written for the sign in button. The problem here is tht whenever I input wrong entry it gives error in logging in.. Which is correct but when ever I enter an entry tht is present in the database it is stuck there and never goes to the next page and never shows the “logged in ” message. and sometimes it even shows the logged in message for wrong entries... i dont know how to handle it..i guess there is some mistake in the brackets..
can anyone help me??
thanks alot

[dnanetwork]

grab all the value from the database by using the select statment & next store it in the session array or arraylist and next to acces them use random class provided by C#...

[ariez88]

I have tried this. please have a look at it and kindly let me know....
Errors are written within the code. and will you please explain how to access them using random class?

protected void loginButton_Click1(object sender, EventArgs e)
   {
       ArrayList arr = new ArrayList();
      arr =conn.retrieve("select * from candidate_info");

       /*

           Error:Cannot implicitly convert type 

          'System.Data.DataSet' to 'System.Collections.ArrayList' 

       */

       int i=0;
       int a = arr.Count;
       Random rand = new Random();

       // where to use this random class. what will it do ???

       while (i < a)

       {

             // rand = arr[0]; confused here ................

            if (this.idTextBox.Text != obj[0].ToString() && this.passTextBox.Text != obj[1].ToString())

               i++;

        }  

        if(i == a)

            Response.Write("<script>alert('ERROR')</script>"); 

       else Response.Write("<script>alert('Logged In')</script>");                        
}

many thanks..

[mail2saion]

Try to modify your code segment in the following way. If matched then exit other wise after completion of all iteration its sure that no matched data found means error.

while (i < a)

{
   // rand = arr[0]; confused here ................
  // Use trim for fixed lenth db datattype if varchar then leave it            
  if (this.idTextBox.Text == obj[0].ToString().Trim() && this.passTextBox.Text == obj[1].ToString().Trim())
  {
  Response.Write("<script>alert('Logged In')</script>");
  return;
  }
  i++;
}  

Response.Write("<script>alert('ERROR')</script>");