Let's focus on the case where $C = Set$, since this will give the intuition for other cases.
An object $p: E \to X$ in the category $Set/X$ can be thought of as an $X$-indexed set, where over every $x \in X$ there is a fiber $p^{-1}(x)$. Similarly, a morphism in $Set/X$ from $p: E \to X$ to $q: F \to X$ is a global function $h: E \to F$ which takes fibers to fibers, i.e., is an $X$-indexed family of functions $h_x: p^{-1}(x) \to q^{-1}(x)$.
Now, for simplicity, take $Y = 1$ to be a 1-element set, where $Set/1 \simeq Set$. The pullback functor $X^\ast: Set \to Set/X$ takes a set $A$ to the $X$-indexed set where $A_x = A$ for all $x$. A morphism from $X^\ast A \to (p: E \to X)$ is thus a family of functions $h_x: A \to p^{-1}(x)$. Such families are in natural bijection with functions
$$A \to \prod_{x \in X} p^{-1}(x)$$
where on the right we take the product of all fibers together. That basically gives you the right adjoint, and suggests the usual notation for this functor $\prod_X$. More formally, the set $\prod_x p^{-1}(x)$ is constructed as the set of sections $s: X \to E$ of $p: E \to X$; categorically it is the equalizer of a pair of functions
$$Sect(p) \to E^X \stackrel{\to}{\to} X^X$$
which you can work out yourself; basically it's the solution set to the equation $p \circ s = 1_X$. The effect on morphisms is similarly described: $\prod_X h = \prod_{x \in X} h_x$; formally, it can be constructed by taking advantage of the universal property of equalizers.
The situation for the right adjoint to a pullback $f^\ast: Set/Y \to Set/X$ is only slightly more complicated. Intuitively, the right adjoint $\prod_f$ sends an $X$-indexed set $p: E \to X$ to a $Y$-indexed set where for each $y \in Y$, we have
$$(\prod_f p)_y := \prod_{x \in f^{-1}(y)} p^{-1}(x)$$
i.e., don't take the product of all fibers $p^{-1}(x)$, but only over those where $x$ sits over $y$ via the map $f$. Again, this can be constructed more formally by considering $Y$-indexed sets of sections, where we take families of equalizers which implement section equations; here we consider a $Y$-indexed family of diagrams of the form
$$(f \circ p)^{-1}(y)^{f^{-1}(y)} \stackrel{\to}{\to} f^{-1}(y)^{f^{-1}(y)}$$
More compactly, compute the object of sections of $p$ seen as a morphism from $f \circ p$ to $f$ in the category $Set/Y$.
Once the formal categorical details of that have been squared away, it works the same way for any locally cartesian closed category.
I would disagree that the hypotheses of the adjoint functor theorem are much stronger than exactness. Left exactness is equivalent to preserving all finite limits, and the hypotheses of the adjoint functor theorem are existence of all limits, preserving all limits, and a smallness condition that usually is easy to verify. Furthermore, to know that a left exact functor preserves all limits, it suffices to know that it preserves arbitrary products (since any limit can be expressed as a kernel of an appropriate map between products). So in most typical applications, the only difference between being left exact and having a left adjoint is whether a functor preserves infinite products.
This also shows how to find a counterexample: find a left exact functor that does not preserve infinite products. For instance, if $M$ is any flat module over a commutative ring $R$, tensoring with $M$ is left exact, but will not preserve infinite products unless $M$ has nice finiteness properties (if $R$ is Noetherian, the condition is that $M$ is finitely generated). In particular, if $R=\mathbb{Z}$ you could take $M=\mathbb{Q}$, or if $R$ is a field you could take $M$ to be any infinite-dimensional vector space.
As Todd notes in his comment, you can similarly get an example for right exactness instead of left exactness by Homming out of a projective module that is not finitely generated.
You can get a more artificial sort of counterexample by taking abelian categories with a size restriction on their objects that prevents the adjoint from existing (because you don't have all (co)limits). For instance, take the category of countable-dimensional vector spaces over some field, and consider the endofunctor given by tensoring with a countably infinite dimensional space $V$. This is right exact, and it ought to have a right adjoint given by $\mathrm{Hom}(V,-)$ (and it is easy to show that if the right adjoint exists, it must be given by $\mathrm{Hom}(V,-)$). But this right adjoint is undefined because (for instance) $\mathrm{Hom}(V,V)$ is uncountable-dimensional and so it doesn't exist in our category.
Best Answer
For the first question: Yes.
Let $C$ be a category with finite products and let $\Delta=(\Delta_1,\Delta_2):C\to C\times C$ s.t. $$\times\dashv \Delta.$$ More specifically we find $$[X\times Y, Z]\simeq[(X,Y),\Delta Z]=[X,\Delta_1 Z]\times[Y,\Delta_2 Z].$$ Note that this isomorphism is functorial in all arguments and given $f:X'\to X$ and $g:Y'\to Y$ we find the isomorphism to be compatible with precomposition with $(f,g)$ (I did not check this compatibility with much diligence so please take care here). Now, take $Y=*$ to be the final object (also the neutral object with respect to $\times$). We find $$[X,Z]\simeq[X,\Delta_1 Z]\times [*,\Delta_2 Z].$$ Taking $X=Z$ and we find $\varphi\in [Z,\Delta_1 Z]$ and $\omega\in [*,\Delta_2 Z]$ corresponding to the identity on $Z$. Conversely, taking $X=\Delta_1 Z$ we find $\phi\in[\Delta_1 Z, Z]$ corresponding to $(\mathrm{id}_{\Delta_1 Z},\omega)$. Now the compatibility with precomposition can be used to obtain $$\varphi\circ\phi=\mathrm{id}\textrm{ and } \phi\circ\varphi=\mathrm{id}.$$
I guess you'd still have to confirm that this actually constitutes an isotransformation $\mathrm{id}_C\simeq\Delta_1$.
Edit: After this, I guess we also find $*$ to not only be final but also initial as we arrive at $$(-,\omega):[X,Z]\simeq[X,Z]\times[*,Z].$$ So $[*,Z]=\{\omega\}$.