Saving your tweets to a database and then displaying them from the database rather than from your Twitter xml helps to keep your site within the 350 requests per hour rate limit.


Classic ASP Active Server Pages Examples, code, tutorials, scriptsClassic ASP for Twitter - Cache Tweets In Database, Respect Rate Limits

First of all, I want to acknowledge http://asp.web.id/update-twitter-status-with-classic-asp-vbscript.html for getting me started with Twitter for Classic ASP. I use his Twitter update script and his Twitter TinyURL script a lot. UPDATE: Saputra's script uses basic authentication which Twitter will no longer support after June 2010. For an idea of how to use Oauth with Classic ASP to automatically update Twitter status, see this example.

The scripts on this page came about because I got to thinking about Twitter's Rate Limit of 350 requests per hour. I decided to cache or dump Tweets into a database and display the Tweets table instead of displaying the Twitter xml file. I think it is a good idea to archive all your tweets in a database anyhow.

The tweetstodb.asp file checks the DateTime and Switch fields of the twittercache.mdb file and if 5 or more minutes has passed since DateTime was modifed, and then if Switch equals 1, it updates Switch to 0 and checks to see if there are existing records with TweetID's that match the tweets of the xml file. Any unmatched tweets are inserted into the database. Then the Switch field is set back to 1.

This limits requests to the Twitter xml file to a max of 12 per hour. That's the idea, anyhow and it seems to be working fine for me.

The file twittercache.asp is for displaying the tweets that have been saved to the database. You can see it in use at Quarter Horse Times. I include the tweetstodb.asp file in the footer of the page and include the twittercache.asp file for display in a text scoller. Technically, if I wanted to be sure to show a visitor the most up-to-date Tweets, I should include tweetstodb.asp in the page BEFORE twittercache.asp. However, I decided not to, because receiving and processing the external xml file may slow down the page load.

NOTE: When first trying to sort the table by the TweetsID numbers, I ran into problems. Because of similar issues with stack overflow etc., I had made the TweetID field a text field. When I attempted to convert it to numeric for the sql statement for display, the same problems appeared. Here is the SQL statement that works using CDBL, so that I can use the field TweetID as numeric for sorting the display from newest to oldest Tweets: "select TOP 20 * from [Tweets] ORDER BY cdbl(Trim(TweetID)) DESC" -- I put the trim in there just in case there were any leading/trailing spaces in the string.

NOTE: The Twitter development team says they will discontinue supporting basic authentication in June 2010 and everyone will need to be using Oauth by then. Here is a Classic ASP implementation of Twitter's current Oauth system, by Scott Desapio. Here is an example using Scott's library to for making automated status updates. Here is a tutorial of registering an app with Twitter.


tweetstodb.asp

<%'
'tweetstodb.asp by ClassicASPreference.com
'Open Connection to the database
TwitterConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("..\data\twittercache.mdb") & ";"
set Tconn = Server.CreateObject("ADODB.Connection")
Tconn.Open TwitterConn


sqlUD = "SELECT TOP 1 * FROM SWITCH WHERE Switch = 1 ORDER BY UDID ASC"
Set rsUD = Server.CreateObject("ADODB.Recordset")
rsUD.Open sqlUD, Tconn, 3, 3
If NOT rsUD.eof Then
latestDate = now()
timeInterval = (DateDiff("n",rsUD("DateTime"),latestDate))
'response.write "Time interval is " & timeInterval


if timeInterval > 5 then 'change the number of minutes to how often to check for new tweets (triggered by loading pg in browser or by task mgr/cron service)

'response.write "Checking now"
rsUD("Switch")=0
rsUD("DateTime")=now()
rsUD.Update
SwitchTurn = rsUD("Switch")
rsUD.close
set rsUD=nothing
'response.write " switch: " & SwitchTurn & "<BR>"
if SwitchTurn=0 then
%>

<%
'NOTE--the feed url below will show only tweets that you have personally originated. If you want to include your retweets, then you will need to adapt the script for your Twitter rss feed instead, which uses your Twitter id number instead of your account name.
TheFeed = "http://twitter.com/statuses/user_timeline/YOURTWITTERNAME.xml"
Set objXML = Server.CreateObject("Microsoft.XMLDOM")
objXML.Async = False
objXML.SetProperty "ServerHTTPRequest", True
objXML.ResolveExternals = True
objXML.ValidateOnParse = True
objXML.Load(TheFeed)
CellCount = 0

If (objXML.parseError.errorCode = 0) Then

Set objRoot = objXML.documentElement
If IsObject(objRoot) = False Then

'response.write ""

Else


Set objItems = objRoot.getElementsByTagName("status")

If IsObject(objItems) = True Then



For Each objItem in objItems

'On Error Resume Next
'uncomment this line if not concerned about error checking, optional

x_TweetID = objItem.selectSingleNode("id").Text
x_Tweet = objItem.selectSingleNode("text").Text
x_Created = objItem.selectSingleNode("created_at").Text


x_Time = ""
x_TweetOK = 1
x_Status = "OK"
x_Destroyed = 0
x_DelStatus = ""

sqlCheck = "SELECT * FROM [Tweets] WHERE TweetID='" & x_TweetID &"'"

Set rsCheck = Server.CreateObject("ADODB.Recordset")
rsCheck.Open sqlCheck, Tconn, 1, 2
if rsCheck.eof then
' Open record
strTsql = "SELECT * FROM [Tweets] WHERE 0 = 1"

set Trs = Server.CreateObject("ADODB.Recordset")
Trs.Open strTsql, Tconn, 1, 2

Trs.AddNew

Trs("Created") = Trim(x_Created)
tmpFld = x_ItemCount
If Not IsNumeric(tmpFld) Then tmpFld = 0
Trs("ItemCount") = cLng(tmpFld)
tmpFld = x_SendCount
If Not IsNumeric(tmpFld) Then tmpFld = 0
Trs("SendCount") = cLng(tmpFld)

Trs("Time") = now()

tmpFld = Trim(x_Tweet)
If trim(tmpFld) & "x" = "x" Then tmpFld = Null
Trs("Tweet") = tmpFld
tmpFld = x_TimeSent
If IsDate(tmpFld) Then
Trs("TimeSent") = cDate(tmpFld)
Else
Trs("TimeSent") = Null
End If
tmpFld = x_TweetOK
If Not IsNumeric(tmpFld) Then tmpFld = 0
Trs("TweetOK") = cLng(tmpFld)
tmpFld = Trim(x_Status)
If trim(tmpFld) & "x" = "x" Then tmpFld = Null
Trs("Status") = tmpFld

Trs("Destroyed") = Null

tmpFld = Trim(x_DelStatus)
If trim(tmpFld) & "x" = "x" Then tmpFld = Null
Trs("DelStatus") = tmpFld

tmpFld = Trim(x_TweetID)
If Not IsNumeric(tmpFld) Then tmpFld = 0
Trs("TweetID") = (tmpFld)

Trs.Update


'response.write "<br>Record inserted:" & Trs("IDkey")&"<br>"
Trs.Close
Set Trs = Nothing
end if
rsCheck.close
set rsCheck=nothing

Next




End If



Set objItems = Nothing

end if
End If




Else

'response.write "There was an error retrieving the news feed


End If

Set objXML = Nothing


set cmClick = Server.CreateObject("ADODB.Command")
cmClick.ActiveConnection = Tconn
cmClick.CommandText = "UPDATE SWITCH SET [Switch] = 1"
cmClick.CommandType = 1
cmClick.CommandTimeout = 0
cmClick.Prepared = true
cmClick.Execute()


set cmClick = Server.CreateObject("ADODB.Command")
cmClick.ActiveConnection = Tconn
cmClick.CommandText = "UPDATE SWITCH SET [UDcnt] = UDcnt+ 1"
cmClick.CommandType = 1
cmClick.CommandTimeout = 0
cmClick.Prepared = true
cmClick.Execute()


end if
end if


Tconn.Close
Set Tconn = Nothing

%>



 


Download Access Database


twittercache.asp for displaying the tweets


<%

' Connect to the db with a DSN-less connection
cn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("..\data\twittercache.mdb") & ";"

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Select all data from the table the_bird
sql = "select TOP 20 * from [Tweets] ORDER BY cdbl(Trim(TweetID)) DESC"

' Execute the sql
rs.Open sql, cn
%>

<%
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof

TheText = rs("Tweet")
TheLink = "http://twitter.com/QHtimes/statuses/"& rs("TweetID")
TheDate = rs("Created")

'*********************************************
'* @TITLE: Regexp Autolink Function
'* @PACKAGE: Simple Classic ASP Twitter API
'* @AUTHOR: Ariel G. Saputra <webmaster@asp.web.id>
'* @DESCRIPTION: function to search and replace url in a text into clickable url
'* @DATE: May 15 2009
'*********************************************
'function aspTwitterAutolink(strHtml)
'Dim objRegex,strReturn
set objRegex = new regexp
objRegex.Pattern = "(\b(?:(?:https?|ftp|file)://|www\.|ftp\.)(?:\([-A-Z0-9+&@#/%=~_|$?!:,.]*\)|[-A-Z0-9+&@#/%=~_|$?!:,.])*(?:\([-A-Z0-9+&@#/%=~_|$?!:,.]*\)|[A-Z0-9+&@#/%=~_|$]))"
objRegex.IgnoreCase = true
objRegex.Global = true
strReturn = objRegex.Replace(TheText, "<a href=""$1"" target = ""_blank"">$1</a>")
set objRegex = nothing
TheText = strReturn
'end function

Response.Write "<font size='-1'><a target='_blank' href=" & TheLink & ">Twitter.com/YourTwitterName/</a>: " & _

"<b>" & TheText & "</b>" & _

"" & _

"<br />"

'Response.Write TheDesc & _

'"<br />"

Response.Write TheDate &"</font>"& _

"<hr />"


' Move to the next record
rs.movenext
' Loop back to the do statement
loop %>


<%
' Close and set the recordset to nothing
rs.close
set rs=nothing
%>

<%
'cn.Close
Set cn = Nothing
%>

 

 


ASP Examples Menu of Active Server Pages scripts code Classic ASP

Classic ASP Active Server Pages Examples, code, tutorials, scriptsASP Examples Menu (below*)



Active Server Pages | ASP | Scripts | Tutorials | Code | Web Programming | Examples
Active Server Pages ASP code examples, tutorials, and snippets for use in Programming interactive websites.

If you find omissions or errors let me know. I have moved these code samples to their own domain; if you find pieces missing, please let me know.
My favorite scripts site is CODANGO.

*

If any the information I've posted here has been useful to you, please donate.

Quick 'n  easy  dirty PHP script code wizard-generator

Zenphoto RSS XML feed used as data source for javascript slide show with PHP

Classic ASP to archive emails into database and save attached files to folder

PHP Image GD for thumbnails and image resizing with pathing for Windows servers

ODBC database connections for Microsoft Access MDB files with Windows 7: solution posted on this forum.

HTML5 embedded video player "Projekktor" completely skinnable with hyperlinked watermark

Edit web.config to allow Windows iis7 server to handle mp4 files or other mime types as needed

URL rewrite or make friendly URLs for iis7 Windows Server 2008 by editing your web.config file

FACEBOOK API GEEWHIZ - INTEGRATE FACEBOOK WITH YOUR WEBSITE

Use PHP to zip folders for backing up a website

Use PHP to archive emails in database and save attached files to folder

Use PHP with json to check how many hits left on Twitter API

PHP get single record from Access database without catchable fatal error message

PHP to make a bot to send automatic Twitter status updates with Abraham Williams' PHP Oauth Twitter Library

A way to use the PHP Oauth Twitter Library with your Classic ASP scripts for sending status updates

Get user's IP address location for your form results

Tutorial for registering an application with Twitter

Update your Twitter status with Classic ASP and Oauth seamlessly and invisibly without having to login to Twitter. Send status updates programmatically from a web page. PHP example too.

CLASSIC ASP: Cache your Twitter tweets in a database to keep your Twitter xml requests within the rate limit of 350 per hour / PHP VERSION

PHP: Use json to fetch Twitter followers' profiles

Index files content and insert into database for faster searches

Classic ASP with Ajax: secure URL check

Classic ASP with Ajax: referer check

Classic ASP with Ajax: a solution to the ampersand problem in querystrings

Very Simple Star Rating System for Classic ASP

Star Rating System uses CSS and JS adapated for Classic ASP from Yvo Schaap PHP script

XMLhttp with ASP to fetch and save photos from Photobucket account through RSS XML. Can be adapted for Youtube videos to import data to database.

NET image resize can be used to make thumbnails for Classic ASP

Track site visitors, get their ip address geographical data, count page views with Classic ASP

Javascript slideshow with Classic ASP to load image details dynamically from database photos table

Classic ASP with Ajax for rotating banners without page refresh and XML file converted to recordset as banner database
--Variation of the above but fetching a REMOTE XML file as the database
--Variation checks for secure URL and builds absolute URL and makes offsite banner links open in new window

True inline edit Classic ASP datagrid table database edit updating with Ajax SIMPLE EASY without page refresh!

Use Ajax with Classic ASP to preview data then update form with javascript url write

Yvo Schaap's easy inline edit Ajax: an example of using it to inline edit multiple rows of records in Classic ASP

Use Classic ASP to send text messages SMS from your website (Also shows how to use xmlhttp to post form data)

Ajax with Classic ASP for seamless, no page reload database record insertion

Nested SQL Statements to make temporary column names

Multiple records per row with paging

Display multiple records per row

Get ID of newly inserted record, Classic ASP, Access Database

Javascript timer with ASP to keep track of time expired until login expires / timeout session is up

Multiple Javascript event functions triggered from single form onsubmit

Get Querystring key names along with querystring variables

These are out of date but may still be of interest:

ASP Youtube Downloader  Version FOUR
All ASP code. Allows user client side downloads of Youtube videos. You have the option to allow files to be saved directly to your site also.
ASP/PHP video downloader (Works better than ASP for large files)

Use ASP to dynamically parse a Youtube XML playlist to render to html.

Zip up an archive file of your site for backup if you need to restore website with ASP and free Zip Component.

Zip all files in a folder into an archive or backup file dynamically by specific file extension.

Sanitize or clean price cost field for database insertion or display.

Javascript image dimensions and image file size preview.

Put recordset into array, assign keys to the records then use record associated with specific array key as a variable elsewhere.

Access Database Interface Generator Wizard to read your table and field names and to create a table display.

Combining dropdown selection box menu with dynamic ASP database page.

How to redirect user to original page they tried to view before logging, after they are logged in.

Previous/Current/Next Records, or Buddy Ring Script

Custom ASP 404 page not found error script: can also be used for URL rewriting

ASP Example: Combine ASP with Javascript for popup windows
dynamically sized to image dimensions.

ASP Example: How to use ASPJPEG to create thumbnails
Click for free Highly recommended thumbnailer for which you don't need Server access (doesn't seem to work on Windows Server 2008)

ASPjpeg: How to re-scale aspect ratio of thumbnails by height instead of width.

ASP Example: What if your host has an older version of ASPJPEG that doesn't support gifs?

Select Count Distinct Records for Access Database

Upload Image to folder and insert filename into database

Get date in such a way that it can be made part of a file name

Isolate a file name away from its path or extension or strip a path from a file name or strip characters from database field

User-customized on-the-fly stylesheet css files

Enhanced Page Hits Counter (also counts downloads and menu link hits)

Loop through array and compare to string with select case

Aphabetical Paging for your ASP scripts

Web Wiz Guide's Site Searcher Script modified as a file content indexer

Let Users Select How Many Records to View

Access Database for your PayPal IPN scripts

Miscellaneous collected code

From Classic ASP to PHP

You don't have to be a genius, just persistent, to write original Active Server Pages scripts

BuildApp - great old Classic ASP script generator!

Software Recommendation: bare_bones_no_bells_and_whistles_asp_code_generator_database_interface_maker (For PHP I recommend EasyGen.)

If any of this information is helpful to you, please consider donating with the button below. Thanks! ~~Lil