The Xsensual company had applied to the council for a sex shop licence, which it needed if the erotic fair was to go ahead.
Xsensual held a similar event at Newcastle last month, selling goods including adult videos, toys and books.
Christian groups unite against plan
The Evangelical Alliance Wales, the Christian public policy body CARE and the Christian Institute had all joined forces to oppose the event, which had been planned for 20, 21 and 22 October.
Cardiff council's licensing and public protection committee rejected Xsensual's application.
One of the reasons councillors gave for turning it down was that the arena is near schools, several churches, hotels and a Toys R Us store.
Protesters celebrate
The religious groups said they were "delighted" over the decision to refuse the licence.
More than 450 people had objected, they said.
Simon Calvert, who attended the meeting on behalf of the Christian Institute, said: "We are pleased that the council listened to the submissions to them not to turn a cherished symbol of national identity in Wales into a massive sex shop.
"It may still go ahead but most of the stalls at this show will now be severely restricted."
Councillor Len Ackerman, deputy chair of Cardiff's Licensing and Public Protection Committee, said: "Several hundred objections were received by the committee in writing and many of those objectors have been heard in person today."
A spokeswoman for Cardiff International Arena said that the booking for the fair, which was due to take place later this month, had not been confirmed so there was a chance it may now not go ahead at all.