ASP SQL Query string syntax issue.

Ahelbling
Ahelbling used Ask the Experts™
Can someone please help me to understand why I am getting this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Lab@xyz.org'.

/trainingSessions/SQLRuns2.asp, line 58


 strSQL3 = "UPDATE _Employees SET Pager = e.LName + ""Lab@xyz.org"" FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = " & Session("strPIID")                                        
 set rsEMP = oConn.Execute(strSQL3)


This is the result in SQL Server Profiler:

UPDATE _Employees SET Pager = e.LName +  "Lab@xyz.org" FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = 7165
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Strings require single quotes around them.  Double quotes makes it an object.

If you want to append the string 'Lab@xyz.org' to the end of last name:
SET Pager = e.LName +  'Lab@xyz.org'
Ok I changed it to this and am getting this new error:

strSQL3 = "UPDATE _Employees SET Pager = e.LName + 'Lab@xyz.org' FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = " & Session("strPIID")                                        

Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated.

/trainingSessions/SQLRuns2.asp, line 58

Capture.PNG
Most Valuable Expert 2012
Distinguished Expert 2018
What is the data type lengths for LName and Pager?  Make sure you have enough room in the Pager column to store the maximum length of the LName columns and the extra 'Lab@xyz.org'
Pager column was varchar(30)
Most Valuable Expert 2012
Distinguished Expert 2018
What is LName?  If it is 30, then you might have a problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial