Hi OP,
I've had periods of erectile dysfunction twice in the past, once for over a year.
For me, it was during periods of high stress in other areas of my life, and had absolutely nothing to do with how attractive I found my partner.
You may not have noticed it happening while having sex because he's not going fully soft. Sometimes I'd speed up to get to the finish line because I knew it was happening, or I'd switch it up and do something not involving my penis for a bit. Or it may be happening while he's having a wank rather than with you.
The problem with ED, especially when it's not being caused by anything physical, is that it's insidious. It happens a couple of times, and then every time you have sex, you start to worry it's going to happen, or think "Phew, I'm not going soft this time", and then it does. It's a self fulfilling prophecy, and it's a really difficult cycle to get out of.
And it's a horrible feeling. It makes you worry that this part of your body is failing you, that you can't do this thing that you're meant to be able to do as a man. You stress that your partner is going to think it's about them, that your erection or lack of it is going to make them feel upset or inadequate.
Like your husband, I too got hold of some viagra. I wasn't even intending to use it when I got it. It was just there as a backup, I think I was hoping that it's mere presence would provide a little mental fortitude. It didn't work. And then it didn't work when I actually used it. Turns out viagra is great for the physical causes of ED, not so much the mental ones.
The first relationship it happened in, it ended the relationship. I was dealing with hugely shit family drama, along with a crappy job that I hated. I was in my early 20s, didn't know how to deal with it like an adult. And my partner didn't help, she made it all about her. Six months in we split up, and the ED persisted through a few one night stands until one girl just shrugged and said "Go down on me instead then". (To be honest, I think she thought I'd drunk to much). She didn't care, and that removed all the pressure. We had a lot of fun that night without involving my penis, and the next morning we had a lot of fun with my penis, safe in the knowledge what we could go back to last nights plan should I fail, which I didn't.
The second time it happened was in my current relationship. This time I was ready for it. Again, it was during a period of huge stress. My mum was dying, and I was being made redundant. First couple of times I styled it out, but once it became clear this wasn't going away I sat DP down and explained what was going on, that it wasn't because of her, explained the details of what had happened the first time round. DP was great, we did other stuff for a couple of months. After about 2 months it became more intermittent, and then went away completely after another couple of months.
What I've basically written an essay to say is, this is really not about you. I get that it'll be knocking your confidence, and that you're upset about it, but showing that is probably the least helpful thing you can do right now.
If your husband wants to talk about it, then talk about it, but if not, don't go on about it too much. He'll already be making a huge thing about it in his head.
From a practical point of view, it's time to get more creative in bed. Don't be afraid to let it get a bit one sided for a while. Just because his penis isn't working, it doesn't mean he doesn't want to pleasure you. Men are all about their penis, so have no idea what they like when that's taken out of the equation. So find out together. And don't feel his penis has to be a touch free zone. Stroke it, play with it, just don't go furiously tugging on or sucking on a flaccid penis, it's not going to have the desired effect.