I echo the "join 'em all!" posts. No brainer: grab all three of the major-program credit cards with the best sign-up bonuses you can find, even if you just toss one or two of them in a drawer.
After that, I'd definitely take a closer look at both HHonors and Starwood.
The advantage of Starwood is twofold:
(1) The program isn't so structured around the big awards that you feel terrible about spending your hard-earned points on modest-value weekend stays. Yes, most of the road warriors here shoot for the 5-night stays at higher-end properties, but when you run the numbers (even against what you'd pay via a wholesaler), a Cat 2 weekend stay for 3,000 points per night is a reasonable ROI for your Starpoints.
(2) Even someone who isn't a huge credit card spender can quickly accumulate enough points to yield a few free nights in the lower-level properties. Lower-level doesn't mean crappy - there are some good Cat 2 Westins and Sheratons out there, and the Four Points aren't all bad either. SPG Amex comes with a good enough sign-up bonus, a low enough annual fee, and a "quasi-elite" status (good to help ensure you get your room preferences, mainly).
The upside to HHonors is better rural coverage and simply more properties, particularly in the mid-scale levels in a lot of cities. HH Amex is free, includes sign-up bonuses, and includes low-elite status as long as you hold the card. So at least join and have it as a backup in case Starwood doesn't have what you need.
Marriott is my current preferred chain, but it's definitely more geared towards the business traveler with rewards focus on the major awards. I have 180,000 points in my account right now, and I know I have another 55,000 to go before I get to the first award I'd consider redeeming. And the credit card isn't very good as a primary spend card, regardless of whether you are a big-volume CC user or not. Getting to 235k would be too long of a slow grind if I weren't actually doing revenue stays on a frequent basis - I'd be at HHonors or Starwood otherwise.
And of course, with any stay, I'd always compare back to the going Priceline rates. You're paying extra for the right to earn hotel points and some level of better treatment at the hotels. Sometimes that number is a little, sometimes it's a lot. Even though I hardly ever use Priceline, I always like to know what the number is. Through promotions and certificates (e.g., Marriott Premium Pounds), I can usually get my rate *close* to Priceline level, but in the end I always find that I'm paying *something* to play the game. Only you can decide if it's worth it.