Only God is fully aware of all the reasons why Moses' actions at Meribah warranted this judgment - I've heard many explanations from pastors with more biblical knowledge than I have, but here are a few of them:
1. He exalted himself as being equal with God by saying, "Must WE give you water from this rock?" Make no mistake, it was not Moses who gave them water from the rock, it was God alone.
2. He struck the rock, rather than speaking to it. This might seem inconsequential aside from two things - first, it was disobedient, and second, it destroyed the symbolic picture of Christ that God was painting for the Israelites. Water from the life-giving Rock was intended to represent to US coming to Jesus by a word of faith - but because of Moses' disobedience, the picture was of the Rock being struck. And even more, being struck twice, in contrast to Christ who was 'struck' - crucified - once for all.
3. As God tells Moses, it is "Because you did not believe in me, to uphold me as holy in the eyes of the people of Israel."
The lessons for us from Moses' error are to trust and believe God will do what He says He will do! To follow Him in full obedience, even in the little things! And to remember, when He accomplishes what He has promised, that it was HIM and not us who did it.
But there is also a lesson in the denial of Moses' prayer to enter the Promised Land. Sometimes God's answer to our prayers is no. Maybe it is because of a failure on our part, or maybe it is because His ways are higher than our ways. Maybe there is some cosmic eternal purpose in our "no" that we won't understand until we are in His presence. Maybe we won't even fully understand then. But we trust that He is good. He proved His love for us on the cross, so we can be sure that when the answer is "no," it is flowing from His love as surely as the water that flowed from the rock in the desert.