ASP.NET Database Entry Randomizer

[suhaildawood]

I am creating a part of a game in ASP.NET (VB). There is a database with four tables (topics), and in those tables there are three items. I need the application to be able to show a random item (for example the three items are 1, 2, 3 - I just want one random entry from 2) within the table and show it on the app. There also needs to be a option to check so that that entry does not show up again when the user clicks for another random entry. How would I go about doing that?

[Akash Saikia]

Hi,

For getting random numbers you need to have a bit of code like this

Random Number Generator

-- Generate Random Numbers (Int) between Rang
-- Create the variables for the random number generation
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
-- You should change you upper to be your minimum value of your primary key of the table  like
--SET @Lower =select min(pkField) from tableName
SET @Upper = 999 ---- The highest random number
--Same thing to be done here like in @lowe but use Max funtion there as max(pkField)
--SET @Upper =select max(pkField) from tableName

--the below written code will generate a random value between upper and lower limit
-- so you will get a value that is withing the range of your table

--But still it can give you the value that is already shown
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
--SELECT @Random

To overcome with the problem that a value shown once is not shown again,you can do one thing is that add one more field to the tables,like lastAccessed
If your select query specified above selects a particular value,then update its lastAccessed's value as '1'

so a change will be needed in your select query to

DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
declare @flag=0

while (@flag=0)
begin

SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
set @flag=
(
select count(*) from tableName where 
pkFieldName=@Random and LastAccessed not like '1'
)



end



select * from tablename
where LastAccessed not like '1'
and pkFieldName=@Random
--the above written query will give you a row that has not been shown yet
--After getting it,update it as accessed
update tablename
set lastAccessed='1'
where pkFieldName=Random

I have used a flag variable,to check that i am not getting a value repeated again and again

For example
first time I can get a value for Random = 1 and it is already accessed
second time Random=5 already accessed
third time Random=15 already accessed

So what i want,is to remain in the loop until i get a value Random=[Value] that is not already accessed
So for checking this,i fire a count(*) query,whether a value is returned by the query or not,if it returns something other than 0,then the loop will be terminated.

And outside the loop,we will fire the select query for getting the actual unshown value
and then update it's lastAccessed value as '1'.

Hope it helps... :)

Mark it as solved if it helps...

[suhaildawood]

Thanks so much - really helped :)

[Akash Saikia]

My pleasure,Happy coding ahead...

[suhaildawood]

how would I go about adding this into a .asp page? I want the user to click a button, and when the button is clicked, it will show the random record from the table in the database. How would I add this code in? I'm sorry, really not used to using ASP.NET with this database stuff.

[Akash Saikia]

Write the code that i have given a stored procedure.
The call that Stored procedure from your aspx page.