Yeah, pretty much any weapon will be better than the Drake Sword when you upgrade it. So I would recommend just using a weapon that you like and scales well with the stats you are choosing and upgrade it. For example, one of my favorite weapons is the plain old Longsword, which is pretty awesome when upgraded.
As for farming souls, have you bought the Covenant of Artorias from Andre? If not, it might be worth saving up 20,000 souls to buy that. I like using the forest for farming -- the humans inside give you a lot of souls when you kill them. (Something like 4,000 to 6,000 each, IIRC. Or maybe that's just on New Game+? Can't remember the exact amount, but whatever it is, it's pretty high.) There's also a trick for killing them without even really having to fight them, if you want to be cheap. =D