I definitely don't think this incident should affect his career. He really is one of the greatest athletes of all time, and doing marijuana one time should not affect his ability and if he did do it a hundred times before, it obviously didn't affect him.
However, as a role model, I don't know if this is the worst thing that could happen. It was already mentioned that he's had a DUI before, and he's not the only one. Everybody is going to mess up some time in their life, and Phelps is just another one of those people. He's not some idol who just swims all day, every day. He has a life too, and if he publicly stated that he wouldn't make another mistake like this again, he should be given the benefit of the doubt.