There are a number of things wrong with your code:
Firstly, you don't check your inputs - if the user does not enter two words, separated by a space, you will get an "Out of range" exception. Always check - users make mistakes, and they would really rather your program didn't crash and take their data with it...
Secondly, please don't do it like that - as EF Nijboer has said, that leaves you wide open for SQL Injection attacks which can accidentaly or deliberately destroy your database. Use parametrized queries instead.
SqlCommand cmd = new SqlCommand("Select PROFILE_ID,FIRST_NAME,PROFILE_REQUEST_STATUS from DSProfile.HDR_PROFILE where FIRST_NAME=@FN and LAST_NAME=@LN and PROFILE_REQUEST_STATUS=@PRS, con);
cmd.Parameters.AddWithValue("@FN", firstname);
cmd.Parameters.AddwithValue("@LN", ...
The way to check the number of words added is pretty simple: Check the Length of the
Spilted
array...