locked
random records problem RRS feed

  • Question

  • User-1904981288 posted
    hi im using an oledbconnection and am trying to generate 5 random records from a database. i am firstly generating 5 random numbers, and can get it to match 1 of the numbers (i) to my database and retrieve the record and display it on the page using the 'message.text method'. can anyone please help me out and tell me how i can get it to match all of the 5 numbers and display them on the screen. i am also having trouble trying to get them to display on the screen. can anyone help me out....... Sub Start_Click (Src As Object, E As EventArgs) Dim i,j,k,l,m as integer Randomize i = CInt(Int((10 * Rnd()) + 1)) j = CInt(Int((10 * Rnd()) + 1)) k = CInt(Int((10 * Rnd()) + 1)) l = CInt(Int((10 * Rnd()) + 1)) m = CInt(Int((10 * Rnd()) + 1)) Dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0;" strConnection += "Data Source=C:***.mdb" Dim objConnection as New OledbConnection(strConnection) Dim strSQL as string objConnection.Open() strSQL = "SELECT * FROM Tab1 WHERE QNo = "& i &"" Dim objCOmmand as New OledbCommand(strSQL, objConnection) objCommand = New OleDbCommand(strSQL, objConnection) objCommand.ExecuteNonQuery() objConnection.Close() Dim objDReader as OledbDataReader objConnection.Open() objDReader = objCommand.ExecuteReader() objDReader.Read() message.text = objDataReader("QuestionNo") message1.text = objDataReader("Question") message2.text = objDataReader("Answer1") message3.text = objDataReader("Answer2") message4.text = objDataReader("Answer3") message5.text = objDataReader("Answer4") objConnection.Close() end sub
    Saturday, March 27, 2004 4:36 PM

All replies

  • User-1430188240 posted
    In my opinion, to make the code maintainable and easy to use, write a dedicated method to retrieve a random record from the database. Just use the same code, but only with the i-variable as the random variable. Then, in your code, change this piece: message.text = objDataReader("QuestionNo") message1.text = objDataReader("Question") message2.text = objDataReader("Answer1") message3.text = objDataReader("Answer2") message4.text = objDataReader("Answer3") message5.text = objDataReader("Answer4") to return an array (or arraylist or some other ordered collection) with the 6 fields you retrieved from the database. Let's give an example (not tested but only pseudo-code): Function GetRandomRecord() As String() 'put Randomize in the Page_Load event Dim i As Integer = CInt(Int((10 * Rnd()) + 1)) Dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0;" strConnection += "Data Source=C:***.mdb" Dim objConnection as New OledbConnection(strConnection) Dim strSQL as string = "SELECT * FROM Tab1 WHERE QNo = "& i &"" Dim result(6) As String Try Dim objCommand as New OledbCommand(strSQL, objConnection) Dim objDReader as OledbDataReader = objCommand.ExecuteReader() objConnection.Open() objDReader.Read() result(0) = objDataReader("QuestionNo") result(1) = objDataReader("Question") result(2) = objDataReader("Answer1") result(3) = objDataReader("Answer2") result(4) = objDataReader("Answer3") result(5) = objDataReader("Answer4") Finally objConnection.Close() End Try Return result End Function To make it easy to display, you could write a separate .ascx ASP.NET User Control to display the 6 fields on, and have 5 such control instances on your .aspx page. Hope this can help you further?
    Saturday, March 27, 2004 5:16 PM
  • User-1904981288 posted
    hi thank you for your help......yea it does make the code more easier to read but from what i can see the code you have written will only display 1 random record rite?? i need it to display 5 in one instance and also is there a way of ensuring that five different numbers get generated???? i have got it to display 5 records but can you please tell me if there is a way to ensure i generate 5 different number, thus meaning 5 records will be displayed on the page????? the code to display more then one record is below but i need to know how to get it to display 5 unique numbers..... Dim i,j,k,l,m as integer Randomize i = CInt(Int((10 * Rnd()) + 1)) j = CInt(Int((10 * Rnd()) + 1)) k = CInt(Int((10 * Rnd()) + 1)) l = CInt(Int((10 * Rnd()) + 1)) m = CInt(Int((10 * Rnd()) + 1)) Dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0;" strConnection += "Data Source=C:***.mdb" Dim objConnection as New OledbConnection(strConnection) Dim strSQL as string objConnection.Open() strSQL = "SELECT * FROM Tab1 WHERE QNo = "& i &"" Dim objCOmmand as New OledbCommand(strSQL, objConnection) objCommand = New OleDbCommand(strSQL, objConnection) objCommand.ExecuteNonQuery() objConnection.Close() Dim objDReader as OledbDataReader objConnection.Open() objDReader = objCommand.ExecuteReader() objDReader.Read() message.text = objDataReader("QuestionNo") message1.text = objDataReader("Question") message2.text = objDataReader("Answer1") message3.text = objDataReader("Answer2") message4.text = objDataReader("Answer3") message5.text = objDataReader("Answer4") objConnection.Close() strSQL = "SELECT * FROM Tab1 WHERE QNo = "& j &"" Dim objCOmmand as New OledbCommand(strSQL, objConnection) objCommand = New OleDbCommand(strSQL, objConnection) objCommand.ExecuteNonQuery() objConnection.Close() Dim objDReader1 as OledbDataReader objConnection.Open() objDReader1 = objCommand.ExecuteReader() objDReader1.Read() message.text = objDataReader1("QuestionNo") message1.text = objDataReader1("Question") message2.text = objDataReader1("Answer1") message3.text = objDataReader1("Answer2") message4.text = objDataReader1("Answer3") message5.text = objDataReader1("Answer4") objConnection.Close()
    Saturday, March 27, 2004 9:49 PM
  • User753101303 posted

    You have a {;} button to insert sample code in a more legible way. The problem is you don't want random numbers but know numbers in a random order.

    Not sure which db you are using but for SQL Server a common approach is to use SELECT ... FROM ... ORDER BY NEWID()

    It creates a GUID for each row and it sorts on that so that rows are retrieved in a random order...

    Wednesday, January 15, 2020 5:39 PM