Ppolymorphe is bang on about the SQL Injection risk: 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. Use Parametrized 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?
So the first thing you need to do is go through your entire app and fix that - leave one concatenation, and you had better backup often!
When you have done that, look at your query, and when you don't get the data you want, start with the WHERE clause, since that is what decided what data is returned:
... WHERE FireArm.Serial_Number = '" + textBox1.Text + "%'"
Equals is an exact match; it doesn't acknowledge wildcards. Did you mean:
... WHERE FireArm.Serial_Number LIKE @SerNo + '%'"