A few completely separate issues here.
One, it was a bad call for someone in his position because of the security implications. That's the pragmatic reason, and really the only reason he should be gone.
Two, as Fumes said, if he was gettin' some in theater with this woman, he was violating CENTCOM G.O. #1. Quite a bit hypocritical, admittedly. I know of Marines who've gotten NJP'd for exactly that.
Three, the adultery/morality question. No one knows what was in his heart, in his marriage, or in his soul. As Tolstoy said,"All happy families are alike. All unhappy families are unhappy in their own way." It's really easy to throw stones at some guy, but you have no idea what he was dealing with. Everyone's a fucking sinner. Yours might not be adultery, but everyone's got their cross to bear, uh, so to speak.
Big time players have big time egos and big time problems. All this stuff does not diminish the fact that he was one of the few generals of recent times that's been actually successful and he deserves accolades for it. At the same time, this event shows poor judgment, and there's no way he could continue, especially at CIA, where personal secret affairs like this aren't tolerated.