Cost depends on the length of your cruise. For an adult, $44 for a week-long cruise. Estimate spending around $6 for each day your cruise lasts. Individual glasses of soda [which is what you get] cost around $1.50.
I went on my first cruise last November and bought a fountain card. It was one of the disappointments of the trip, mostly the only one, because it's expensive, and I got dirty looks from the bartenders because I didn't tip (I thought that the tip was already included).
You can get lemonade, coffee, chocolate, etc., at the buffet area for free, as well as nice, cold water.
I'm not a big soda drinker so I don't think its worth it. Its cheaper for me to buy a few glasses rather than buy the card. Also, depending on how many ports you have, you may not be on the ship during the day for several days.
If you drink more than 2 or 3 sodas per day, it's a great deal. My dh and son are both big soda drinkers, so the cards MORE than paid for themselves. I don't ever buy one since I'm a one soda a day (diet coke) girl!
My dh and son said they never received anything but good service with their cards.
Carnival Elation March 11
Carnival Imagination Sept 07
Carniival Sensation Dec 06
RCI Sovereign of the Seas Sept 06
Carnival Miracle Sept 05
Carnival Glory Sept 04
Carnival Fantasy Jan 04