For the long term future, renovation and revitalization of the area the stadium should be built in this vicinity, just closer to the water.
Buffalo is the waterfront. It is what built the area and made it initially great. It is what makes Buffalo unique.
Many examples of how to manage traffic with a W/F stadium- Chicago, San Francisco, etc. You use out lots and shuttles. Soldier Field is a crown jewel of Chicago's lakefront skyline. It helps to show a vital, prosperous image.
Nothing advertises a city and region better than an NFL game on national TV. Imagine a lakefront shot, possibly with a western sunset over the water (something few cities in this country have), the shiny new stadium full and illunminated. A Buffalo NY, re-born and vital.
DO IT!