Before you can understand why more megapixels does not translate to awesomeness, let's understand just what the heck the megapixel is in the first place. The technical definition from DigiCamHelp is:
Digital cameras capture images as pixel elements, known as pixels. Simply put, a megapixel is equal to one million pixels. Digital images are made up of thousands of these tiny, tile-like picture elements. The more pixels, the higher the image resolution. Resolution relates primarily to print size and the amount of detail an image has when viewed on a computer monitor at 100%.
Here, however, is where we hit the proverbial "rub". Why? Not all digital cameras have "sensors" of the same size. This is why the DigiCamHelp site goes on to say:
The number of megapixels is only one aspect relating to the quality of a camera, or the actual quality of a photo it is capable of producing. Factors such as camera sensor and processor, and the optical quality of a lens, play equally important roles.

What Sensors Have to Do With It
A sensor is what replaces film inside of the camera. When you used to use a 35mm camera, the film actually measured 35mm on the diagonal. Today, it is rare to find sensors that large, and the more pixels crammed on to a smaller sensor the lower the quality of the shot. This means that you have to do a bit of research when buying a camera in order to determine if the sensor and the number of megapixels will result in decent results.
Yes, this means that a camera with lower megapixel counts can still be relied on to take killer shots. The other thing to understand is that the camera settings also have a lot to do with the results.
What Enlarging Has to Do With It

Let's use an example to make this easier to understand: you want to give your mother a canvas photo of your family as a Mother's Day gift. That's great and makes for a real treasure, but only if the quality of the shot is good.
You put your digital "point and shoot" on a tripod, set the self-timer, and pose for a series of shots. You order the picture to canvas print and are deeply disappointed at the blurriness and low quality of the results.
What happened? Well, you may have a camera with a high megapixel count, but too small of a sensor. This allows information coming in through the lens to be lost or misread by the flawed pixels. Just think of it this way...try cramming yourself into a pair of jeans that are too small...what's the result? The same thing happens to images jammed into a sensor that just cannot handle all of their "beauty".
When you enlarge such shots, such as when you make custom canvas prints, it exacerbates the problems by really emphasizing them. You can often overcome this by setting the resolution as high as possible and by dialing the ISO setting down too (or as author Ken Rockwell says: Sharpness depends more on your photographic skill than the number of megapixels). This removes as much of the pixilation as possible and can often produce much better results. For instance, try an ISO of 100 and keep the setting as brightly lit as you can, as this will make even an enlargement pretty darn good! So don't get wrapped up in the game of megapixels. You can take a great photo with any camera.