Yes! Before you needed to use the same brand drive (i.e. Western Digital to Western Digital) now that OWC has this adapter you can choose any drive! HD, SSHD or even a SSD!
The only rub here is the drives SATA speed! You need to get a drive that will work in a SATA II (3.0) system.
Some drives have a fixed SATA speed like SATA III (6.0) which won't work in your system. Other's have the ability to sense the systems SATA port speed and can adjust to it. As an example a very good drive here would be a hybrid SSHD drive like: Seagate Desktop SSHD. If we look at its spec sheet: Seagate SSHD spec sheet we can see the line: SATA Transfer Rates Supported (Gb/s) lists all three SATA speeds, this is what you want to look for, for the drive you do get. You want your systems SATA speed listed.
If you go with a SSD drive you'll need to get an adapter frame if you replace your HD with it. Frankly, I would go with the SSHD as its more cost effective given the age of your system.