Plans for a new supermarket, care homes and nursery in a Herefordshire town have been dropped following widespread local opposition.