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?
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
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...
Thanks so much - really helped :)
My pleasure,Happy coding ahead...
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.
Write the code that i have given a stored procedure.
The call that Stored procedure from your aspx page.