Why was set theory inadequate as a foundation to the emerging new fields and why category theory isn't?

It is not that set theory is inadequate for some aspects of homological algebra, but rather that the language provided by category theory is very powerful and very expressive for the purposes of some areas in mathematics, in particular homological algebra. Logically speaking set theory is perfectly adequate since all of homological algebra can be stated in $ZFC$. But, just like some programming languages are better suited for certain tasks then others (but ultimately they are all equivalent to machine code) so is it in mathematics that the choice of base language may be suited to certain things more than to others.

Traditional set theory, since Cantor, serves as a very rigorous foundations and provides a strong and consistent common language to discuss mathematics. But, it is centered on sets and so everything in set theory is (warning: getting into philosophy now) static. Even the notion of a function as a relation satisfying a condition is a very static view of what a function is.

In category theory in (philosophical) contrast the emphasis is on the morphisms. These are the undefined terms and the resulting language is very powerful in expressing the interrelations between structures and between constructions in mathematics.

This does not say that one is good and the other is bad or that one is adequate and the other is not. It is to say that for certain things one formalism may be better suited than the other. Without a doubt the category theory formalism is superior when considering homological algebra (or algebraic topology more generally). In other areas of mathematics (such as, to the best of my knowledge, combinatorics) there seems to be little to gain from the categorical formalism.

Now, it was discovered that category theory can be used also as a foundation for logic and there are many differences between categorical logic and classical logic. Here again one formalism may be better suited than another, depending on the purpose. For instance, it would seem that for constructive and intuitionistic logic topos theory provides a very natural setting, more so than classical set theory would. For classical logic it is debatable how much merit there is in adopting the topo theoretic approach. Classical techniques such as Cohen forcing can be understood quite nicely via topos theory but one may argue that the insight provided is not significant enough to justify abandoning standard techniques for new ones. It's a matter of taste.

Particularly in homology theory a language that allows one to easily speak of processes, processes between processes, and comparisons between such is crucial. Everything can be stated in $ZFC$ but than one would not see the forest for the trees (or rather one would not see the structure for the objects). When the language talks about morphisms and not objects one can ignore the objects and concentrate on the processes and thus starting seeing the structure.