ASP SQL Query string syntax issue.
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
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
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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'
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial