What's the connection between the end of the previous parsha -- where Moshe charges Yehoshua not to fear war against Canaan because G-d will help with that conquest just like he helped with the conquest of the lands of Sichon and Og -- and the tefilah of Moshe which opens our parsha?
Ramban (see also Ibn Ezra) explains that there is a gap in the narrative that Moshe is filling in. Moshe told us at the end of last week's parsha that Yehoshua would be in charge, but he never explained why he would not be filling that role himself. Our parsha fills in the rest of the story.
I am not sure how Ramban would explain why the Torah needs to record the entire tefilah of Moshe in all its detail. The only point that we need in order to close the narrative gap is the fact that G-d rejected Moshe's tefilah for whatever reason. In fact, all we really need to know is that Moshe sinned and would therefore not be allowed to enter Eretz Yisrael.
Alshich suggests that the Torah here is delivering a powerful psychological boost to the people. Moshe records his tefilah as a nechama (Shabbos Nachamu!) to Klal Yisrael. Imagine the people's reaction to hearing that Yehoshua would be the one in charge: You took us this far and are now abandoning us? You took us this far and now are giving up the fight? So Moshe tells them that's not the case. To the contrary, he davened because he wanted to join them and continue the journey. He was even willing to give up his role as leader and enter Eretz Yisrael as just a simple Jew, a regular member of the klal, if only to be with them at that moment when they were zocheh to come into the land. Moshe was in effect telling the people look at the great effort I made through tefilah so as not to forsake you.
Hashem turned Moshe down, "Va'yisaber Hashem l'ma'anchem," for the sake of the people. Moshe spent his life working miracles; the dor de'ah of the midbar needed that type of leadership. The generation which entered Eretz Yisrael had to find G-d in the mundane world -- no miracles. Their leader is Yehoshua. Moshe here told the people that G-d had to turn his prayers down because his neshoma was needed in chutz la'aretz, in the midbar, because he was the eternal leader of the dor de'ah; at the time of ultimate redemption he would need to be there to lead them into Eretz Yisrael.
Alshich writes that we see from here that no Jewish soul is forsaken. The dor de'ah were stuck in the midbar because they rejected Eretz Yisrael and trusted in the report of the meraglim. They took the word of the spies over that of Moshe, Aharon, Yehoshua, and Kaleiv. Nonetheless, G-d did not let Moshe enter Eretz Yisrael so as to allow for the future redemption of even these souls.
We've discussed What of the souls of the dor ha'midbar, the generation Moshe served as the leader of for some many years? Who would lead them to Eretz Yisrael in the future if there was no Moshe Rabeinu? Therefore, Moshe had to remain outside the land. Amazing: to bring the generation who had sinned with the meraglim and rejected the land eventually into the land, Moshe is denied the privilege he so wanted. To prevent Jewish souls from being lost, Hashem denied the dying request of the great Moshe.