To explain why I didn't like it, I need to go into a bit more detail: this involves spoilers for the "cinema experience" more than the film itself. So, if you're planning to go, and you'd prefer to be surprised, you should probably skip the rest of this post. Also, if you enjoy going to the public performances then I don't want to take the fun out of it by being negative, so you may want to skip this post too.
I'm not sure whether there's supposed to be a code of silence (like there is for The Mousetrap), but if there is then I don't subscribe to it. What I would advise is that you watch the film on TV before you go to watch it in public; that's partly to give you a rough idea of what to expect, and partly because you won't be able to hear all of the dialogue in the cinema. If you'd like more detail (forewarned is forearmed) then read on...
You can get a more detailed plot synopsis elsewhere, but the basic idea is that a clean-cut young American couple (Brad and Janet) are out for a drive when their car breaks down. They go to the nearest house looking for a phone, which turns out to be the castle of Dr Frank-N-Furter, where there are various dubious things going on ("orgies" might be slightly too strong a word). As the couple stay there, they gradually lose their own sexual inhibitions.
When I went off to the cinema, lots of people were there in costume; given the subject matter, this typically means corsets, fishnet stockings, and high heels (for men and women). The cinema was showing "Singalong Sound of Music" in another screen, which had also attracted a costumed audience, so while we were waiting to go in there were various nuns and Nazis wandering around too. One unusual thing about this film is that it has a live cast (also in costume), and they act out the film as it goes along, mirroring their on-screen counterparts.
The theory was that audience participation was welcomed, but I think that is something of a euphemism; I'd use the term "demanded". It's quite common to say that certain films have a "cult audience" (e.g. Blade Runner), but the term isn't usually meant this literally: at Rocky Horror events, people are initiated into the group and then expected to dress and act the same as everyone else.
Before the actual film started, they played one of the songs ("Time Warp"), and the actors told everyone to stand up and do the appropriate motions. Most people (including me) did so, but there were a few people near me who stayed sitting down. I felt uncomfortable doing the pelvic thrusts, so I sat back down. As soon as I did, the person next to me sat down too. I think this was a definite case of peer pressure, which was able to be cancelled out by other people resisting it. I found it easier to go against the flow when there were other people already sitting down, and I'm sure that my neighbour's resolve was bolstered by my actions. At this point, there were some other actors roaming the aisles (as well as the ones on stage at the front), who were ordering us to stand up and join in, but I ignored them.
Once the song was over, the cast on the stage said it was time for a game, so they told everyone to stand up "including you lot at the back who were sitting down during the song". There was then a process of elimination. Initially, they told people to stick up their hands if they had had sex in public places, e.g. on a bus, and those people could then sit down. Then it was people who'd been to the show before. Some of the other "first timer" people I was with sat down at this point, having seen which way the wind was blowing, but honesty compelled me to remain standing. The next condition was "Anyone who's had sex today". Then sex this week. At this point, I was having severe doubts about the direction the questions were taking. I don't recall how long the time frame got; I think it was a month. Anyway, that was the last phase, so the actors then said "What do we call them?" (i.e. those of us who were still standing), at which point most of the audience (presumably those who had attended before) yelled out "Virgins!". The actors then said "Right, the rest of you sad losers, all come out to the aisles." Once we were gathered at the front, they selected three men and three women (luckily for me, I wasn't included in that), and lined them up on the stage. A bunch of the actors formed a circle round them, and danced round singing a modified version of "Ring around the roses" ("Ring around the virgins"), which ended with a simulation of oral sex. We then returned to our seats. A few other people from the audience were then picked on for not joining in, and the ground rules for the evening were explained; essentially, anyone who didn't toe the line would be isolated and ridiculed.
This was all plenty to convince me that there were other things I would rather be doing with my time, so I left after the lights went down. (For the sake of the people I was with, I didn't want to cause a scene, or draw attention to them.) I value my independence, and I am nobody's sock puppet, so I choose my own words and actions. If people want to join in with the songs, then that's fine by me; I'd be happy to sing along with something like High School Musical. Equally, I can understand why it is more fun to do things in a group (e.g. going on a rollercoaster), and I can see the appeal of going to a Star Trek film marathon in a Starfleet uniform. However, if people feel the need for everyone else to act in the same way, regardless of personal preference, then I think that we are into the realms of bullying.
If you do some digging on the web, you can find descriptions and videos of the initiations at other performances; the precise details seem to vary, depending on the cast members involved. A common theme seems to be that all the first timers get a red V drawn on their foreheads, and the people who are taken on stage are told to strip down to their underwear (as described here). I haven't been to the stage show, but I've been reliably informed that it's "more debauched".
It's possible that I'm taking all of this too seriously. I remember that someone described me a while back by saying "the stick up your arse must have a stick up its arse", but I like to think that I have a sense of fun. I think that the majority of the audience are decent people, and I know someone who was in the cast for one of the Rocky Horror cinemas; the philosophy of the virgin sacrifice seems to be that "This is for your own good, and you'll enjoy it once you've got through this". It's just a bit unfortunate if anyone who really doesn't like it gets caught in the middle. In my case, I think that part of the problem is that I wasn't expecting anything like this, and I wasn't really in the right mood for it.
Defining the target audience is a bit tricky; I don't think it's as simple as saying "only prudes won't like it". Personally, I enjoyed The Guru and "Avenue Q", both of which involve sex fairly prominently. Actually, there's a quote from Spaced which has stuck in my mind, where Tim describes Rocky Horror as "boil in the bag perversion for sexually repressed accountants and first year drama students".
One feature of the film is "call outs". The idea is that the audience will shout things out at various points during the film; this will drown out some of the real dialogue, which is why I recommend watching the film on TV first (so that you actually know what's going on). When I watched the video, I printed out this Audience Participation Script. For instance, when Brad shows up everyone shouts "Arsehole!", and when Janet turns up people shout "Slut!". I gather that the precise calls will vary between cinemas, so it's probably best to follow other people's lead on your first visit. However, I hardly did any of them, mainly because they weren't things that I would normally choose to say. I do swear from time to time, but I don't really like shouting "Do you fuck your sister?" If you're in this situation at a performance, you might want to mime some of it, so that you don't draw too much attention to yourself.
Anyway, despite my Dire Warnings of Doom, I know that several of my friends do enjoy going to these performances. Ultimately, I'd just advise you to go in with your eyes open.