Your DSL modem should simply replace your cable modem as it is connected now. The WRT54G usually comes with a CD that guides you through the software setup.

Here's the web how-to:
Answer

You don't say what part of your cable internet service is horrible. If it's the price, speed or excessive downtime, then the change is understandable.

If the problem is the wireless connection, then it's your wireless router, and has nothing to do with the cable service. The WRT54G is not a bad router, but either the antennas need some improvement to get through your walls, there could be wireless phone interference, or you need to move the router to a better location.

Here's the web how-to on everything but antenna improvements:
Answer