Microsoft OLE DB Provider for SQL Server error '80040e14' Invalid column name . SQL query

Question

Hi I have having this error message

Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'gonzalez'.

When I try to run this query I just made. This website has a search box that I type in that searches all the last names in corporate directory.

    SecurityQuery = "SELECT * FROM dbCorpDir.dbo.vwEmployees WHERE sn like '" & FormatDBTextSearch(Last_Name) & "' ORDER BY sn;"

this works fine in SSMS

    SELECT * FROM dbCorpDir.dbo.vwEmployees WHERE sn like 'gonzalez' ORDER BY sn;

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
Could you have a single quote in your search string? In any case, printing out the value of SecurityQuery after substitution makes the problem obvious in about 95% of the cases. — Gordon Linoff
If you look at SecurityQuery variable to see what is in it, you'll find what is wrong. Query is simple and looks correct unless there is some special character in Last_Name that may cause problem. BTW, this query is not safe because of SQL Injection. — FLICKER
yes forgot to say we are aware of the sql injection but this site is internal only. so we are not that worried. — T Dang
Run Sql Profiler and find the exact string that is being sent to your server. — granadaCoder

T Dang T Dang · Accepted Answer · 2016-08-02T20:26:40

Ok guys just for future reference I will post what I found.I removed the single quotes. But I needed to rename the output fields to the column names of the new database.(DUH)! I kept relying on the debugger telling me what line the error was on and it was wrong. Thanks again for all the help.

   SecurityQuery = "SELECT * FROM dbCorpDir.dbo.vwEmployees WHERE sn like " & FormatDBTextSearch(Last_Name) & " ORDER BY sn;"

Microsoft OLE DB Provider for SQL Server error '80040e14' Invalid column name . SQL query

1 Answers