Cameron has been a good PM overall but made an error calling for the referendum in the first place. He went purely by the polls which predicted that people would vote to remain.
However, he should have known it was a massive gamble for a number of reasons -
1) Almost everyone over a certain age would vote to exit the EU. And older folk will produce a significantly higher turnout than younger voters.
2) Seniors within his own party will support Brexit.
3) Labour are currently run by a leader who himself is a Euro skeptic. Corbyn only supported to remain because he had to go with his party's choice. He was unenthusiastic and uninspiring during the campaign. Cameron should have been aware that he wouldn't get the strong support of the shadow leader.
4) Cameron should have put a rule in place which could override a Brexit victory. For instance, he could have said that in order to avoid a split of nations within the UK - if 2 of the 4 nations that make up the UK vote to remain, a Brexit victory would be declared as being void.
Cameron always liked playing a risky game. This was one risk too far.