MS SQL 2005: generate ID using ROW_NUMBER()

SELECT PVID, AuthorName, ROW_NUMBER() OVER(ORDER BY PVID DESC) AS ID WHERE ID BETWEEN 16 AND 20

PVID	AuthorName	ID
34	Mitchell	16
65	Sean	17
73	John	18
90	Mogan	19
96	Chang	20

Select DISTINCT column together with Non DISTINCT Column

I have a post rating table named Rate, with Column RateID (auto ID), PostID, DateRated

I want the select the 20 latest rated post with no repeated PostID

Below query help me to get the data needed

SELECT TOP 20 PostID, MAX(RateID) AS RateID, MAX(DateRated ) AS DateRated FROM Rate
GROUP BY PostID
ORDER BY RateID DESC, DateRated DESC

technorati like - News Scroller with jQuery

I have create a real time viewer for blogarate.com in ASP.NET using jQuery. If you not familiar with jquery, you can read more here jquery.com. It's a very powerful javascript library.

Let me explain how the real time viewer/scroller/ticker/ works:

When web user access to realtime.aspx. The page will bind 5 latest rated blog and show on screen. once the user/visitor rate on the blog, the newly rated blog will show on top, the rest will scroll down and the last one will be deleted. To see real example, please check on http://202.75.40.204/blogarate/Realtime_.aspx

There are few things that we need to take care of in my case:

• HTML/ASPX
• CSS
• Javascript
• AJAX

HTML/ASPX

I'm using in the scroller <ol> because we have more than one (5) message want to display.

<div id="content">
<ol class="wrapper">
<li class="headline">
Message 1
</li>
<li class="headline">
Message 2
</li>
<li class="headline">
Message 3
</li>
<li class="headline">
Message 4
</li>
<li class="headline">
Message 5
</li>
</ol>
</div>




CSS
Create CSS for #content and .wrapper with height: 395px; width: 500px; overflow: hidden; The height: 395px; is the trick here. You will get what i mean when go along.
Each headline will have height: 78px; 5 headline will give total of 390px.

#content {
position: relative;
overflow: hidden;
float:left;
height: 395px;
width: 500px;
clear: both;
}
.wrapper
{
position:relative;
float: left;
margin:0;
overflow:hidden;
width: 500px;
padding-left: 0;
list-style-type: none;
}
.headline {
position: relative;
height: 78px;
width: 500px;
left:5px;
overflow: hidden;
border-bottom: 1px solid #CCC;
float: left;
list-style-type: none;
z-index : 1;
}
.next
{
margin-top: -100px;
/* for IE */
filter: alpha(opacity=40);
/* CSS3 standard */
opacity: 0.4;
/* for Mozilla */
-moz-opacity: 0.4;
}
active
{
background-color:#EEE;
border-bottom: none;
}





Javascript

// JScript File
var BlogarateRealtime = {
timer : 10, // second
display : 5, // how many item displayed
interval: null,
animation: 500, //milisecond


init: function() {
if (!BlogarateRealtime.interval) {

// stop scrolling when mouseover the message
// start scroll again when mouseout
$("#content .wrapper").mouseover(function(){
BlogarateRealtime.stopScroll();
}).mouseout(function(){
BlogarateRealtime.startScroll();
})
BlogarateRealtime.RatingUpdate();
BlogarateRealtime.startScroll();
}
},

RatingUpdate: function(){
var i;

// add class active when mouseover
// remove class active when mouse out
var cpostItem = $(".wrapper .headline");
$(cpostItem).mouseover(function() {
$(this).addClass("active");
}).mouseout(function() {
$(this).removeClass("active");
});


// xxx.shx return realtime message (rewly post)
var html = $.ajax({
url: http://xxx.ashx/ ,
async: false
}).responseText;
// return 3 value:
//1. PostUpdating[0] - any blog rated
//2. PostUpdating[1] - new blog ID
//3. PostUpdating[2] - new blog information
var PostUpdating = html.split("");

// check if any new blog rated (1 = yes, 0 = no)
if (PostUpdating[0] == "1")
{

// assign currentItem with new blog infomation
var currentItem = PostUpdating[2];
// add class next to currentItem
// pre append currentItem to #content .wrapper
$(currentItem).addClass("next").prependTo("#content .wrapper");
// scrolling (animate) the new blog to marginTop -1
$(".wrapper .next").animate( { marginTop: -1 }, BlogarateRealtime.animation, function() {
// remove marginTop -1, class next and fadeIn new blog
$(".wrapper .next").css("marginTop", -1);
$(".wrapper .next").removeClass("next");
$(".wrapper .next").fadeIn(BlogarateRealtime.animation);
});

var postItem = $(".wrapper .headline");
// fade out and remove the last post
$(postItem[BlogarateRealtime.display]).animate( { opacity: .50 }, BlogarateRealtime.animation, function(){
$(postItem[BlogarateRealtime.display]).remove();
} );
// add class active when mouse over
// remove class active when mouseout
$(postItem).mouseover(function() {
$(this).addClass("active");
}).mouseout(function() {
$(this).removeClass("active");
});
}

},
startScroll: function() {
if (!this.interval) {
this.interval = setInterval("BlogarateRealtime.RatingUpdate()", this.timer * 1000);
}
},
stopScroll: function() {
if (this.interval) {
clearInterval(this.interval);
this.interval = false;
}
}
};
// initial once ducument finish loaded
$(BlogarateRealtime.init);

AJAX
The call of http://xxx.ashx/ will return newly added post if there is any.

You can see the effect in http://202.75.40.204/blogarate/Realtime_.aspx . You will only see the scroll effect if only new post rated. Done.
Leave me a message or a comment if you have any. Thanks

Global variable In web.config

Let's say want to add a variable deployment in web.config, add
<appsettings><add value="0" key="deployment"></appsettings>to <configuration></configuration>if <appsettings></appsettings>not exist in web.config.


<configuration>
<appsettings>
<add value="0" key="deployment">
</appsettings>
</configuration>

to call the variable in C# code,

string strDeployment = System.Configuration.ConfigurationSettings.AppSettings["deployment"];

Done

jQuery: Exception... "'Permission denied to call method XMLHttpRequest.open' when calling method: [nsIDOMEventListener::handleEvent]" nsresult: ...

I received below error message in firebug when i try to use jQuery Ajax call

var html = $.ajax({
url: "http://www.xxx.com",
async: false
}).responseText;

Where http://www.xxx.com is different domain from my current site.

Exception... "'Permission denied to call method XMLHttpRequest.open' when calling method: [nsIDOMEventListener::handleEvent]" nsresult: "0x8057001e (NS_ERROR_XPC_JS_THREW_STRING)" location: "" data: no]

As far as my understanding, this is not allowed. In order to have the similar result, use Cross Domain getJSON or you can check on my sample in other post which work on cross domain ajax.



Ajax treat http://www.xxx.com and http://xxx.com as two different entry. There are different between url with www and without www. To solve this problem, use location.host to get the url that use by visitors.

example:
var html = $.ajax({
url: "http://"+location.host+ "/Realtime.ashx" + "?PostID=" + PostID,
async: false
}).responseText;

Dynamic CSS reference in ASP.NET

This sample allowed me to dynamic add CSS link to my ASP.NET page. If css provided via querystring, the page will link to provided css. If not provided then if will refer to the default css.


protected void Page_Load(object sender, EventArgs e)
{
string mycss;

if (Request.QueryString["mycss"] != null)
{
if (Request.QueryString["mycss"].ToString().Trim().Length > 0)
{
mycss= Request.QueryString["mycss"].ToString().Trim();
}
else
{
mycss= "~/default.css";
}
}
else
{
mycss= "~/default.css";
}
AddLinkedStyleSheet(this, mycss);
}

public static void AddLinkedStyleSheet(Page page, string stylesheet)
{
HtmlLink link = new HtmlLink();
link.Href = page.ResolveUrl(stylesheet);
link.Attributes["text"] = "text/css";
link.Attributes["rel"] = "stylesheet";
page.Header.Controls.Add(link);
}

credit give to Rich Sturim

window.location.search

window.location.search is a very useful script that able to return all querystring for further usage like pass to another page/AJAX.

To test on the usage, save below page as locationsearch.htm. To see the result, add querystring to the page called: locationsearch.htm?p=testing&l=javascript

<html>
<head>
<script language="JavaScript">

function onload()
{
alert(window.location.search);
}

</script>
</head>

<body onLoad="onload()">

</body>
</html>

Top 100 living geniuses

1= Albert Hoffman (Swiss) Chemist 27 1= Tim Berners-Lee (British) Computer Scientist 27 3 George Soros (American) Investor & Philanthropist 25 4 Matt Groening (American) Satirist & Animator 24 5= Nelson Mandela (South African) Politician & Diplomat 23 5= Frederick Sanger (British) Chemist 23 7= Dario Fo (Italian) Writer & Dramatist 22 7= Steven Hawking (British) Physicist 22 9= Oscar Niemeyer (Brazilian) Architect 21 9= Philip Glass (American) Composer 21 9= Grigory Perelman (Russian) Mathematician 21 12= Andrew Wiles (British) Mathematician 20 12= Li Hongzhi (Chinese) Spiritual Leader 20 12= Ali Javan (Iranian) Engineer 20 15= Brian Eno (British) Composer 19 15= Damian Hirst (British) Artist 19 15= Daniel Tammet (British) Savant & Linguist 19 18 Nicholson Baker (American Writer 18 19 Daniel Barenboim (N/A) Musician 17 20= Robert Crumb (American) Artist 16 20= Richard Dawkins (British) Biologist and philosopher 16 20= Larry Page & Sergey Brin (American) Publishers 16 20= Rupert Murdoch (American) Publisher 16 20= Geoffrey Hill (British) Poet 16 25 Garry Kasparov (Russian) Chess Player 15 26= The Dalai Lama (Tibetan) Spiritual Leader 14 26= Steven Spielberg (American) Film maker 14 26= Hiroshi Ishiguro (Japanese) Roboticist 14 26= Robert Edwards (British) Pioneer of IVF treatment 14 26= Seamus Heaney (Irish) Poet 14 31 Harold Pinter (British) Writer & Dramatist 13 32= Flossie Wong-Staal (Chinese) Bio-technologist 12 32= Bobby Fischer (American) Chess Player 12 32= Prince (American) Musician 12 32= Henrik Gorecki (Polish) Composer 12 32= Avram Noam Chomski (American) Philosopher & linguist 12 32= Sebastian Thrun (German) Probabilistic roboticist 12 32= Nima Arkani Hamed (Canadian) Physicist 12 32= Margaret Turnbull (American) Astrobiologist 12 40= Elaine Pagels (American) Historian 11 40= Enrique Ostrea (Philippino) Pediatrics & neonatology 11 40= Gary Becker (American) Economist 11 43= Mohammed Ali (American) Boxer 10 43= Osama Bin Laden (Saudi) Islamicist 10 43= Bill Gates (American) Businessman 10 43= Philip Roth (American) Writer 10 43= James West (American) Invented the foil electrical microphone 10 43= Tuan Vo-Dinh (Vietnamese) Bio-Medical Scientist 10 49= Brian Wilson (American) Musician 9 49= Stevie Wonder (American) Singer songwriter 9 49= Vint Cerf (American) Computer scientist 9 49= Henry Kissinger (American) Diplomat and politician 9 49= Richard Branson (British) Publicist 9 49= Pardis Sabeti (Iranian) Biological anthropologist 9 49= Jon de Mol (Dutch) Television producer 9 49= Meryl Streep (American) Actress 9 49= Margaret Attwood (Canadian) Writer 9 58= Placido Domingo (Spanish) Singer 8 58= John Lasseter (American) Digital Animator 8 58= Shunpei Yamazaki (Japanese) Computer scientist & physicist 8 58= Jane Goodall (British) Ethologist & Anthropologist 8 58= Kirti Narayan Chaudhuri (Indian) Historian 8 58= John Goto (British) Photographer 8 58= Paul McCartney (British) Musician 8 58= Stephen King (American) Writer 8 58= Leonard Cohen (American) Poet & musician 8 67= Aretha Franklin (American) Musician 7 67= David Bowie (British) Musician 7 67= Emily Oster (American) Economist 7 67= Steve Wozniak (American) Engineer and co-founder of Apple Computers 7 67= Martin Cooper (American) Inventor of the cell phone 7 72= George Lucas (American) Film maker 6 72= Niles Rogers (American) Musician 6 72= Hans Zimmer (German) Composer 6 72= John Williams (American) Composer 6 72= Annette Baier (New Zealander) Philosopher 6 72= Dorothy Rowe (British) Psychologist 6 72= Ivan Marchuk (Ukrainian) Artist & sculptor 6 72= Robin Escovado (American) Composer 6 72= Mark Dean (American) Inventor & computer scientist 6 72= Rick Rubin (American) Musician & producer 6 72= Stan Lee (American) Publisher 6 83= David Warren (Australian) Engineer 5 83= Jon Fosse (Norwegian) Writer & dramatist 83= Gjertrud Schnackenberg (American) Poet 5 83= Graham Linehan (Irish) Writer & dramatist 5 83= JK Rowling (British) Writer 5 83= Ken Russell (British) Film maker 5 83= Mikhail Timofeyevich Kalashnikov (Russian) Small arms designer 5 83= Erich Jarvis (American) Neurobiologist 5 91=. Chad Varah (British) Founder of Samaritans 4 91= Nicolas Hayek (Swiss) Businessman and founder of Swatch 4 91= Alastair Hannay (British) Philosopher 4 94= Patricia Bath (American) Ophthalmologist 94= Thomas A. Jackson (American) Aerospace engineer 3 94= Dolly Parton (American) Singer 3 94= Morissey (British) Singer 3 94= Michael Eavis (British) Organiser of Glastonbury 3 94= Ranulph Fiennes (British) Adventurer 3 100=. Quentin Tarantino (American) Filmmaker 2

Extract from http://www.telegraph.co.uk/

Hot topic in town - Facebook

Plan starting to develop a facebook application, not sure where to start and i found this wiki really help .... have a look .. Facebook for .Net developers ..
http://wiki.developers.facebook.com/index.php/ASP.NET

System.Net.Mail.SmtpFailedRecipientsException

I'm migrating my existing application to a new application server. When test on the suggestion box which involve SMTP, i get below error message:



System.Net.Mail.SmtpFailedRecipientsException: Unable to send to all recipients. ---> System.Net.Mail.SmtpFailedRecipientException: Mailbox unavailable. The server response was: No such user here --- End of inner exception stack trace --- at System.Net.Mail.SmtpTransport.SendMail(MailAddress sender, MailAddressCollection recipients, String deliveryNotify, SmtpFailedRecipientException& exception) at System.Net.Mail.SmtpClient.Send(MailMessage message)

Few things that i test on new server:

1. Install SMTP - check on document provided
2. Testing with the Pickup directory
   You can also compose a simple e-mail text file based on the SMTP specifications (RFC 822). Here is the content of a sample text file typed in Notepad:
   
   From: myname@mydomain.com
   To: someone@somedomain.com
   Subject: testing
   This is the test message body.
   
   Simply copy or move the text file into the Pickup directory where SMTP was installed. (The default path should be "\Inetpub\mailroot\Pickup" but if you have Exchange installed then the path will be "\Program Files\Exchsrvr\Mailroot\Vsi 1\Pickup".)
3. Set Relay Restrictions in the IIS admin. Put your app server IP Address/Domain Name in the Relay Resctictions.
   
   
   
4. After all, remember to restart your machine. Restart IIS or Virtual SMTP server won't help. Restart the server do the best.

Done