Sounds like your pc is the same age / spec as mine. I have considered the options, and can tell you all your fears about upgrading are well founded.
New mobo to take a dual core cpu will mean faster memory and a pci-express graphics card, and ideally sata hard drives.
You could use some of your old components, eg the IDE HDDs, but you'd only be slowing down your new system.
I quickly concluded that the most effective upgrade would be a new base unit. You can get a blisteringly fast processor, as much RAM as you like, and hard disks the size of Belgium.
Dell are astonishingly good value at the moment.
Take a look at the most recent Intel duo processors; they have been getting very good reviews.