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
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
