There are two aspects to this.
Firstly, it is unreasonable to expect the opposition to vote with the government. Their job is to oppose. The SNP is part of the opposition and I imagine (though have not checked) that SNP MPs have voted against the government on most issues in the last nine months or so and this was no different. The government has an overall majority and the reason this measure was defeated was because some of their own MPs rebelled against them.
However, more important than that, Mr Cameron pledged (among many other things) to prevent Scottish MPs voting in Westminster on matters that did not affect their constituents. In was his promise to address the “West Lothian Question”. The SNP, for their part, made a loose undertaking not to block measures which do not affect the Scots. Whatever contortion the SNP may use to dress up this matter, it would have little or no effect on people in Scotland.
Mr Cameron needs to urgently address the democratic imbalance that exists between the Scots and the English and if it encourages the Scots to demand a second referendum (where they will hopefully vote to leave the UK) then that will be a bonus.