I was using a Terratec DMX 6Fire 24/96 for years, but seeing as Terratec never made properly working Windows 7 drivers for it, I had to change when I build my current PC.
I bought an ESI/AudioTrak Prodigy 7.1 HIfi, which according to the manufacturer was compatible with Windows 7. This was based on the VIA Envy24HT (updated version of the VIA Envy24 from the DMX 6Fire), and provides very low ASIO latency. Unfortunately, the drivers for this card was absolutely shockingly bad, to the point of giving bluescreens on a regular basis, so I ended up boxing it and replacing it with a Sound Blaster X-Fi Titanium HD. This card has worked beautifully since day 1, and it has very clean output with proper RCA plugs instead of those stupid mini-jacks.
I want good quality ASIO drivers, a selection of in and outputs, a dedicated amplified headphone output and high quality DACs from a daily runner. Gives me some options for usage. The on-board sound on my P6T-Deluxe does not really provide all these things. Hence why I bothered with a sound card in this system.
As to the question "Do you really need a sound card in a modern PC?" then the answer is: "It depends what you want to do with it".
If you're only after listening to mp3s, watching movies and gaming, then you'll probably be just fine with on-board sound. In fact, with a modern mid-to-highend motherboard, you'll be more than fine.
If you also want things like low latency ASIO drivers for music or recording software, proper microphone inputs, greater feature sets and so on, then you might be in the market for a discreet sound card.
Also, if you're looking at the lower end of sound cards, things like cheap Sound Blaster cards or something like that. You know, all those ~$50 cards, then forget it. You're better off using the on-board card and putting the money you save into better speakers. Generally, if you buy any speaker in the world that is designed as "computer speakers", regardless if it's the biggest, baddest Logitech, Creative, Klipsch or Bose set, it'll still be likely to be the weakest link. Hook your PC up to an expensive hifi setup, and it's a different story. Skip the sound-card altogether and go digital to the hifi, and you will still be fine even when connecting to expensive high end stuff.
If I was building a dedicated gaming rig however, I would absolutely not waste money on discreet sound.
WinXP : PIII 1.4GHz, 512MB RAM, 73GB SCSI HDD, Matrox Parhelia, SB Audigy 2.
Win98se : K6-3+ 500MHz, 256MB RAM, 80GB HDD, Matrox Millennium G400 MAX, Voodoo 2, SW1000XG.
DOS6.22 : Intel DX4, 64MB RAM, 1.6GB HDD, Diamond Stealth64 DRAM, GUS 1MB, SB16.