Question:
I’m trying to retrieve the “firstname” and “lastname” fields from a SQL Server database, place them into a column called “fullname” using the AS statement. When I simply retrieve “lastname,” it works just fine. As soon as I do the concatenation, I get an error message from the SQL server with regard to the datatypes (mismatch of boolean and char). I’ve checked the datatypes of all fields in the SQL server and they are all char.

Here is my code:

Set oRS = oConn.Execute("SELECT FirstName &
 ' ' & LastName AS FullName, E_Mail, SchoolId from 
Member2 where SchoolId='" & strSchoolId & "'")While Not oRs.EOF%>      <%=oRS("FullName")%>    <%=oRS("E_Mail")%>    <%=oRS("SchoolId")%>  <%oRS.MoveNextWend

Answer:
Unlike VB, SQL Server doesn't recognize the ampersand (&) as a valid concatenation operator. It's actually a bitwise AND for integer or binary datatypes. Use the plus sign (+) instead. For example, this query in the Pubs database fails with the error message you mentioned:

SELECT au_lname & au_fname AS Name FROM authorsBut this statement works:SELECT au_lname + au_fname AS Name FROM authors