Wow... to have so many drives gone bad at the same time is almost impossible.
Have you tried to move the boot disc onto the same IDE channel as the CD ROM?
You mentioned that the CD ROM seems to be spooling, right? Have you tried to boot with a Windows XP Setup Disc?
Have you had a lightning storm or a electrical surge of some sort?
Sorry about the series of questions. Here is what I am getting at:
If the Primary IDE channel is producing failure codes on both drives... you might not want rule out the motherboard going bad. It is VERY unlikely that you would have 2 disc drives fail at the same time unless there has been a major surge that damaged both of them, but... in that scenario, the motherboard will get fried before the drives would.
My conclusion with the available information thus far is motherboard.....
Good luck.
**** ADDITIONAL INFO ****
For future information, avoid swap harddrives like you are doing (direct IDE installation. Get an external USB/Firewire enclosure and change out harddrive that way.
I am pretty sure you are running NTFS, and depending on cluster size (part of system settings), you can easily corrupt your drives when you swap it from one computer into another one (even if both are XP). Imagine, you have a harddrive that was preformatted by one PC with cluster size x, and it's partially full. Now you plug it into another computer that's trying to write with cluster size y....
External enclosure to make sure both systems recognize the device via USB or Firewire, and the device would make sure it's using the same cluster size, etc...
So... you now have 2 corrupt harddrives. Repair them by putting them in external enclosures and running a data recovery software (www.download.com).
Back to your computer, the fact that it won't even boot into CD ROM tells me the motherboard is very likely to be the culprit.
To be completely certain, use the CD Rom from your 2nd computer to see if it will boot.
If it boots, your problem is a bad CD Rom and 2 corrput harddrives.
If it still doesn't boot, you have a bad motherboard with 2 corrupt harddrives.