Decades ago I had the same type of what are people talking about?!, it's certainly not happening to me! surprise, regarding MDMA and the supposed wonders of dancing the night away. I felt the effects of the substance, but to me, nightclub dancing on MDMA still felt about as awkwardly-conscious, performative and unnecessary as it did without!
I suspect it's similar with the spiritual stuff, in principle. That is, if you're typically not a personality who tends towards that stuff - spiritual connections and revelations and such - then perhaps no substance will necessary make you so.
Might be you're just different than me, or might be that it's the type of nightclub. For me it needs to be something more ravey, like psytrance, DNB or hard techno. If I went to some lame commercial house nightclub, I'd have a bad time as well. But either way, no reason not to retry, life is an adventure. It beats sitting around and <doing the regular evening passtime stuff>.
[dead]
Set and setting. None of these drugs are like alcohol that just dulls the brain and lowers your IQ by 30 points. In a way you get what you're prepared to get.
It sounds like the nightclubs you were going to weren't a good setting either. A club should never feel self-conscious or performative. You should be able to get lost in there with the music. The dancing shouldn't feel unnecessary but more of an obligation because the music is so good. If you feel its unnecessary and awkward sober then no drug will fix that except maybe alcohol. I to to clubs and dance 100% sober these days.