How to show error messages in current notebook?

In version 9 you can select this in the Preferences>Messages tab.

---

For those using old versions of Mathematica that do not have that tab you can change these settings in the Option Inspector in the category Global Options > MessageOptions.

UPDATE Mar 2018: In newer versions, there is no "Messages" tab in the "Preferences" dialog. Use the Option Inspector instead (in the format menu or command shift O).

Since things have changed a complete answer with the new setting is in order.

Use the Option Inspector (in the Format menu or using the keystroke command shift O).

And change the "KernelMessageAction" field to "PrintToNotebook" as follows