The US Center for Disease Control (CDC) recommends DEET, Picaridin, IR3535, and lemon eucalyptus oil (active ingredient being p-menthane 3,8-diol). There was also apparently a claim that catnip is 10x more effective than DEET at repelling mosquitoes, but I've never encountered any catnip-based repellents. Outdoor retailer REI has a pretty thorough comparison of the pros and cons of common insect repellents, but they don't really cite research.
DEET seems to be agreed upon as the most effective insect repellent, but there are a lot of arguments about its safety.
The ATSDR (a sub-agency of the CDC) has a page on their site about the effect of DEET in humans. They cite several other studies and summarize reports of (many upsetting) side effects of DEET use, including this one:
A study was done involving 143 National Park Service employees at
Everglades National Park to determine the effects of DEET on varying
use groups. Exposure groups were classified as low (non-users), medium
(0.01-0.52 g/day) and high (0.71-69.38g/day) use of DEET. It was found
that 36 of the workers (25%) reported health effects that they
attributed to DEET. These effects included rashes, skin or mucous
membrane irritation, transient numb or burning lips, dizziness,
disorientation, and difficulty concentrating. Headache and nausea were
also reported. A statistically significant difference was not found
between reported effects from high-exposure and medium-exposure
workers, although the incidences were significantly higher than in the
non-users (McConnell et al. 1987).
This article from Field & Stream I think summarizes the matter best: there is no real consensus about the issue of DEET, and no singular authoritative voice on the subject. On a personal note, I avoid DEET almost entirely because I'd rather deal with insect bites (and take anti-malarials when traveling) than risk exposure to something potentially toxic to my central nervous system.
Finally, I've never come across any literature that says what medium of repellent is most effective (spray v. lotion). I think the concentration of the active ingredient is more likely to affect the product more than its viscosity.
P.S. My uncle has a hiking friend who swears that taking cayenne pepper capsules is the best insect repellent. I'm not really sure if there's anything to back that up.