When you tell him "Yes, that's cool, BUT…," you have, in effect, told him his wish is not legitimate. Our wants; yours, mine, and the baby's next door; are legitimate – we don't choose them so much as they choose us. We're stuck with them, until we figure out how to handle them.
So teach your son how to deal with his completely normal unmet desires, rather than discounting them. This is a multi-step process that can be approached from several directions.
First, be sure he's not just hungry, thirsty, tired, or overscheduled. If he has unmet needs in those areas, all his other wants will feel more like needs to him.
Second, empathize with him. Get into the desire he's expressing as if you were a kid with the same wish. "Wow, look at those wonderful (details) on that (item)! I love that." Or "Oh, yes, I wanted one just like that when I was little." Once he feels understood, some of the urgency will disappear from his request.
Then start to shift his attention away from right now. "Wouldn't it be cool to have that toy under the Christmas tree? I'll help you write it on your Wish List." or "You know, you could do some extra chores for the next few weeks and earn 10 stickers for that."
Another tack: Plan ahead. BEFORE you take him anywhere, lay out your expectations. You can be very clear that he will not be getting a treat this trip, because it's just a quick run for milk and bread. (You might wish to bring a small refreshment or toy with you from home.) Or, if you'll need him to be on good behavior for a longer time, tell him he can pick out ONE item only for ($) or less, if he helps you shop. Have him agree to this – a verbal contract. He will agree, because he'll be eager for his treat.
While shopping, ask him to find a few simple items he can hand you or drop in the cart. If he begs for everything, and he will at first, just smile and remind him of his agreement. A hundred times, if necessary. Stay calm and cool, and expect his very normal repeated requests. A phrase that always served well for me was, "Sweetie, the answer was no last time, is no this time, and will be no every single time you ask." If he starts leaning toward a tantrum, which could happen if he's tired, hungry, or otherwise at the end of his emotional rope, be prepared to abandon the trip and quietly take him home. You won't have to do that too often with most children.
Finally, be aware that if you or Dad give in often to your own impulses, at home or in public, he'll be seeing that as model of how the world ought to work for him, too. So live by the principles you want him to learn. Kids learn best from example, and parental double standards are, quite reasonably, infuriating. A good example "takes" more slowly than yelling or correcting, but takes for good.
Good luck, J., and stay calm. Your hurt over this is normal, and it's wonderful that you are looking at your daily patterns to make sure they are as healthy as possible. And your son is just doing what he's emotionally wired to do. The world is full of so many goodies, and he doesn't yet fully comprehend why he can't have them all. He'll learn if you're calm, loving, and consistent.