how to combine first name and last name in asp.net c# and read it from database

Try:

cmd.CommandText = "SELECT FirstName + ' ' + LastName, CompanyName, Department, Location, MobileNo from ResidentTable where EmployeeNo='" + TxtSearch.Text + "'";

But please, don't do that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

cmd.CommandText = "SELECT FirstName + ' ' + LastName, CompanyName, Department, Location, MobileNo from ResidentTable where EmployeeNo=@EN";
cmd.Parameters.AddWithValue("@EN", TxtSearch.Text);

This is quite easy to do -

cmd.CommandText = "SELECT FirstName + LastName FullName, FirstName,LastName,CompanyName,Department,Location,MobileNo from ResidentTable where EmployeeNo='" + TxtSearch.Text + "'";

//Do this at query level
//You should use ISNULL() function too becasue in the case when FirstName or LastName will be null then it will replace that null with ''
//Best of luck


SqlConnection con = new SqlConnection(_connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT ISNULL(FirstName,'') +' '+ISNULL(LastName,'') AS FullName, FirstName,LastName,CompanyName,Department,Location,MobileNo from ResidentTable where EmployeeNo='" + TxtSearch.Text + "'";

using (con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
//lblFullName.Text = reader["FullName"].ToString();
lblFirstName.Text = reader["FirstName"].ToString();
lblLastName.Text = reader["LastName"].ToString();
lblCompanyName.Text = reader["CompanyName"].ToString();
lblDepartment.Text = reader["Department"].ToString();
lblLocation.Text = reader["Location"].ToString();
lblMobileNo.Text = reader["MobileNo"].ToString();
}

else
{
lblFirstName.Text = "";
lblLastName.Text = "";
lblCompanyName.Text="";
lblDepartment.Text="";
lblLocation.Text="";
lblMobileNo.Text="";

ScriptManager.RegisterStartupScript(this, this.GetType(), "RunCode", "javascript:alert('Employee No. not exists, please try again.');", true);

}