Ok guys, Andrei and I are cool again. Help Me understand the weapons upgrade system.

Haven't sunk much time into weapons or armor reinforcement because up until now I have been fine with my Drake sword and whatever armor suits the situation. However, at level 40 and just starting Sen's Fortress and Undead Asylum 2, I seem to have hit a wall. I was told yesterday that my Drake sword is now basically useless, and to ditch it for upgraded weapons. Here's what I don't get:
My drake sword is at 200 pwr and my closest thing to that is at like 145, weighs me down, and is swung at like 3 secs per swing. I'll have to upgrade the **** out of ONLY ONE (because I can't afford to spend souls on the whole arsenal) weapon for it even to approach the Drake's 200 pwr. So what gives? I gave Andrei a couple of Embers but he hasn't mentioned them again, and I don't know what to sink souls into. I really am not getting this system. HELP PLEASE...

What are your stats like, and how do you like to fight (bulky and powerful, fast and light, or medium)? How you answer those will determine what weapons you should look towards upgrading and how you want to upgrade them.

As to how upgrading works, it's actually pretty simple. Each non-unique weapon (a weapon that upgrades with regular titanite shards at first) can be upgraded a total of 15 times. Every 5th upgrade (when upgrading a weapon from +5 to +6 or from +10 to +11) you have the option to branch your weapon into a different upgrade path depending on what Embers you've found and turned in.

For example, let's say you have a Longsword. It will always upgrade to +5 with regular titanite shards. At this point, you've also given Andrei the Large Ember and the Divine Ember. By speaking with Andrei, you can now continue upgrading your Longsword to +6 or change it to a Raw or Divine Longsword +0.

As to which way to upgrade your weapon is entirely dependant on your stats, and there is no truly "best" upgrade path. There are alot of options as you get more and more embers and more and more blacksmiths, but here is a nicely done chart that may help convey some of these concepts.

Spoiler:

Use the drake sword in sense until you meet the mimic in the elevator room. It will be one tough fight and then you'll get the lightning spear. It's good enough for the next few stages of the game.