Member Avatar for bang2711

hi guy,

i need this ugently.. hope someone can help me out..

i need to get data from database,
but want it to take randomly and also dont want to get data duplicate.

Ex; i get data A from DB, then there will be a next button. i wan to go next data after i click it,
and get data c, later i dont wan to get back A & C again.

i`m doing Question and Answer thing.

thanks alot..

  • 3 Contributors
  • 4 Replies
  • 986 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by Ganesh_m

Dani AI

Generated

The array-based idea that proposed is a solid, practical way to guarantee no repeats: load just the primary keys, track which ones were used, and serve a shuffled sequence. It is simple, predictable, and cheap on the database for small-to-medium pools. Do not mix DAO and ADODB in the same VB project; choose one explicitly, close recordsets and release objects to avoid leaks.

A few alternative approaches and their tradeoffs: database-side randomness (for example ORDER BY RAND() with LIMIT 1) is the easiest to implement but becomes very slow as the table grows because the DB computes a random value for every row and sorts. Picking a random offset from COUNT(*) then using LIMIT 1 OFFSET n avoids per-row randoms but large offsets are still costly. Choosing a random id in the min/max range and selecting the next existing row can be fast, but requires retries and may bias results if there are gaps. For resumeable per-user sequences, pre-generate a randomized order for that user (store user_id, question_id, position) so queries are simple and the session can be resumed.

Practical recommendation: for most Q&A use cases preload only IDs, perform an in-memory Fisher‑Yates shuffle, then fetch rows by id as you need them — that guarantees no duplicates and keeps database work low. For very large pools or heavy concurrency, generate and persist per-user orders or maintain a per-user "used" table and exclude those ids in queries. Always test chosen method with realistic data sizes and measure response times to pick the best balance of memory, latency and complexity.

Recommended Answers

All 4 Replies

Member Avatar for bang2711

this is my program..
please help me

Member Avatar for Member #53442

Hi Bang2711,

First of all you are missing two .dlls in your .zip file:
XVoice.dll
ExButton.dll

Next, I see you have references to both DAO and ADODB in your project, unless you actually use both (and from what I see you are only using DAO) remove the one you are not using to avoid conflicts, OR specify which type you are using,
i.e. Dim rstdb1 As DAO.Recordset OR
Dim rstdb1 As ADODB.Recordset
Otherwise VB will pick which ever one it wants to... :(

Now to a possible soution.

An idea come to my mind as soon as I read your post and here it is.

Create a user defined type and a public variable of that type:

Public Type DBWords
   DataID As Long
   Used As Boolean
End Type

Public DBWordsArr() As DBWords

You do NOT want to set a limit here since you want to be able to resize it dynamically depending on the data in your DB.

Next you load your array with the IDs from your table PicSentenses and FALSE in the boolean part.

Set rstdb1 = db.OpenRecordset("SELECT ID FROM PicSentenses")
rstdb1.MoveLast 'Will get an accurate RecordCount
rstdb1.MoveFirst
ReDim DBWordsArr(rstdb1.RecordCount -1) 
i = 0
'Unless you want to use a 1-based array
'ReDim DBWordsArr(1 to rstdb1.RecordCount)
'i = 1
While Not rstdb1.EOF
   DBWordsArr(i).DataID = rstdb1!ID
   DBWordsArr(i).Used = False
   rstdb1.MoveNext
   i = i + 1
Wend
'ALWAYS close your recordsets in VB otherwise you are prone to memory leaks, yes EVEN in VB...  :)
rstdb1.Close  
Set rstdb1 = Nothing

Then you use your random code to get the next array element to use verifying for Used, I would create a function that returns a new random number within the range of subscripts of DBWordsArr

i = GetNextElement()
While DBWordsArr(i).Used 
   i = GetNextElement()
Wend
Set rstdb2=db.OpenRecordset("SELECT * FROM PicSentenses WHERE ID = " & DBWordsArr(i).DataID)

To guard against a infinite loop I would also keep counters of how many items I have in the array and of how many I have used so far.

Whenever you visit on node of your array you set the Used flag to TRUE and then you cannot get that item back until you have gone through the entire array and reset all the Used flags to FALSE.

Hope this makes sense and that it helps

Have fun

Yomet

P.S. I have not tested this code as I just typed it here in the answer window... ;)

Member Avatar for bang2711

hi Yomet,

thanks alot for ur help
i have solve the problem..

:D have a great day..

Member Avatar for Ganesh_m

I need the exact code to connect to oracle8i from vb 6.0.
Please send the code if you have

Thank you

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.