Re: Better random record generator
Thank you Dean for seeing my issue, and supplying a possible resolution. I agree access is not the best system out there, But I have applications with over 500,000 records in a single table that have been working with cold fusion of many years also. I agree I should migrate but that is not in my scope for this project right now. I will try that solution today and let you know. Thank You, Keith ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314392 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Better random record generator
Keith McGee wrote: I am using an access database for the back end and would like to know if anyone has a better solution. *snip* 1. Use MySQL. 2. Order by Rand() 3. ??? 4. Profit ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314215 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Better random record generator
*snip* 1. Use MySQL. 2. Order by Rand() 3. ??? 4. Profit this is not the only program or site running off of access database, we do have oracle and I know I could use where rownum = #randomID#, but I am looking for an access solution. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314216 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Better random record generator
MS SQl server lets you order by newid() ie. select * from My_Table order by newid() I have no clue if this works in Access or not. Wil Genovese One man with courage makes a majority. -Andrew Jackson A fine is a tax for doing wrong. A tax is a fine for doing well. On Oct 22, 2008, at 9:17 AM, Keith McGee wrote: I created a random record generator for pulling current news items. I am using an access database for the back end and would like to know if anyone has a better solution. This code works, but I think I'm missing something. Thank you in advance Keith. Step 1: Pull the current records and get a count. cfquery name=get_news_count datasource=#dsn# select fld_page_ID from tbl_marketing_pages where fld_item_type = n and fld_active_date = #now()# and fld_inactive_date = #now()# /cfquery Step 2: Generate a random number from our count. cfset intRandomNumber = randRange(get_news_count.recordcount, listLen(get_news_count.recordcount)) Step 3: Output the query starting with the row equal to our random number and only output one row. cfoutput query=get_news_count startrow=#intRandomNumber# maxrows=1 Step 4: Set a random record id with the record ID that came back from our query. cfset randomID = #fld_page_ID# /cfoutput Step 5: Pull all the information from using our random record ID in our where statement. cfif get_news_count.fld_page_ID GT cfquery name=get_news datasource=#dsn# select fld_title, fld_body, fld_attach_ID, fld_url, fld_active_date, fld_inactive_date from tbl_marketing_pages where fld_page_ID = #randomID# /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314221 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Better random record generator
Well, Access isn't a database. If you gave several sites, why not just make them in oracle? I mean, why use access at all? Keith McGee wrote: *snip* 1. Use MySQL. 2. Order by Rand() 3. ??? 4. Profit this is not the only program or site running off of access database, we do have oracle and I know I could use where rownum = #randomID#, but I am looking for an access solution. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314222 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Better random record generator
Thanks for the suggestion, but I tried that and I didn't work. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314224 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Better random record generator
Keith, You can pull a random record from Access by using the Rnd() function. So your query would look like this: cfquery name=get_news datasource=#dsn# select fld_page_ID, fld_title, fld_body, fld_attach_ID, fld_url, fld_active_date, fld_inactive_date from tbl_marketing_pages where fld_item_type = n and fld_active_date = #now()# and fld_inactive_date = #now()# ORDER BY Rnd(fld_page_ID) /cfquery Hope this helps, Dean I created a random record generator for pulling current news items. I am using an access database for the back end and would like to know if anyone has a better solution. This code works, but I think I'm missing something. Thank you in advance Keith. Step 1: Pull the current records and get a count. cfquery name=get_news_count datasource=#dsn# select fld_page_ID from tbl_marketing_pages where fld_item_type = n and fld_active_date = #now()# and fld_inactive_date = #now()# /cfquery Step 2: Generate a random number from our count. cfset intRandomNumber = randRange(get_news_count.recordcount, listLen(get_news_count.recordcount)) Step 3: Output the query starting with the row equal to our random number and only output one row. cfoutput query=get_news_count startrow=#intRandomNumber# maxrows=1 Step 4: Set a random record id with the record ID that came back from our query. cfset randomID = #fld_page_ID# /cfoutput Step 5: Pull all the information from using our random record ID in our where statement. cfif get_news_count.fld_page_ID GT cfquery name=get_news datasource=#dsn# select fld_title, fld_body, fld_attach_ID, fld_url, fld_active_date, fld_inactive_date from tbl_marketing_pages where fld_page_ID = #randomID# /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314249 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Better random record generator
ORDER BY Rnd(fld_page_ID) This command keeps pulling the same order, even if I close the browser. If they go back to the main page I would like possibility of having a different news item appear. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314257 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Better random record generator
I understand you want to stay with access for whatever reason. But consider how much trouble this is for you.. Are you sure you aren't able to use a different database (or convince your client that he should move up)? I mean, access can only handle 1 user at a time anyway.. That alone should be a good reason to move up (also, access costs money. MySQL doesn't). I know (believe me.. I know) that you may not have a say in this. But consider informing whoever choose to stick with access (or start with it) that they are making the wrong choice. Keith McGee wrote: ORDER BY Rnd(fld_page_ID) This command keeps pulling the same order, even if I close the browser. If they go back to the main page I would like possibility of having a different news item appear. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314260 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Better random record generator
ORDER BY Rnd(fld_page_ID) This command keeps pulling the same order, even if I close the browser. If they go back to the main page I would like possibility of having a different news item appear. Keith, This is a ColdFusion caching issue. If you run the query directly in Access, you will see that it does properly return random records. Make sure you have the newest CF DB drivers from Adobe. Also, despite what everyone is eluding to, Access is not the devil tool that they are making it out to be. I have a couple of very active legacy apps that have been running for close to 10 years on Access. I don't use it for any new sites, but there is also no need for me to scramble to move them. Yes, it does not scale like a true server based databases (MS SQL, mySQL, etc.), but depending on your circumstances, it should probably perform fine. For web based applications, it only needs to support a single user, which is ColdFusion. And in actually, it will allow for multiple users in a network environment sharing a single DB. It is not the greatest (locking issue primarily), but it does work for some people. Long term, you will probably want to migrate to one of the other DB apps, but just by telling you so doesn't help you with your immediate problem. Just my .02 cents. Dean ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314264 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
