I see two errors:
1. probably typo -
INSERT INTO ESK_Products(CateogoryID...
2. You don't supply value for CategoryID in VALUES part
After new information supplied by OP I've changed query.
string query =
"DECLARE @categoryID INT; " +
"SET @categoryID = (SELECT CategoryID FROM ESK_Categories where CategoryName='" + DropDownList1.Text + "'); " +
"INSERT INTO ESK_Products " +
"(CategoryID, ProductName, ProductImage, UnitCost, Description) " +
"VALUES " +
"(@categoryID, '" + txtproname.Text + "', '" + FileUpload1.FileName + "', " + txtproprice.Text + ", '" + txtprodesc.Text + "')";