You have two problems here: the first is that as PIEBALDconsult has said, you should never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
The second is that you can't parameterize the column name: the SQL preprocessor does optimisations before the parameter replacement so a query like this will return no records:
DECLARE @C NVARCHAR(MAX)
Set @C = 'Title'
SELECT * FROM VIDEOS WHERE @C LIKE '%My Movie%'
That means that to be safe you have to use CASE WHEN to select only valid column names and build the SQL query that way. Concatenating the column name and "making absolutely sure that the user can't type anything into it" is risky as some future change could replace the combo box with a textbox and put your whole DB at risk.
But very, very definitely parameterize the search string! That'll probably get rid of your problem at the same time.
Choose to search for the product using combobox
The way I;'d do it to vbe safe is to have two columns in you combobox: a visible column that describes the table to search, and a hidden column which contained a search string specific to that table:
"
Computer Monitors" and "
Keyboards" would be the visible rows, and the hidden column would contain "
SELECT * FROM PRODUCTS WHERE Monitors LIKE '%@SEARCH%'" and "
SELECT * FROM PRODUCTS WHERE Keyboards LIKE '%@SEARCH%'"
That way, the user can't enter a value for the columns name which could allow SQL Injection.
You set the BoundColumn property of the ComboBox to the hidden column, and the other one will be displayed. You can access the visible data with the Text property, and the hidden data with the Value property.
With me so far? Give it a try ...
Submit an article or tip