Searching for infromation out of a database using text input c#

Question

I'm trying to search for a customers "forename", "surname", and "customerid" to show into a combo box through the use of sql.

     string strSql = "SELECT customerID&\" \" & forename &\" \" & surname AS FullName, surname FROM customer WHERE [customerID]='" + txtCustomerID.Text +"'";

Though with this code I get a "data mismatch exception" which I'm assuming is because I'm using a string and searching for an int?

**Never** use string concatenations to build SQL queries. This code is vulnerable to SQL injection. Always use parametrized queries to avoid meeting with [`boby-tables`](http://bobby-tables.com/). — Darin Dimitrov, Jan 09 '13 at 16:51
[How do Parameterized queries help against SQL Injection](http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection) — Alex K., Jan 09 '13 at 16:52

Luis Tellez · Answer 1 · 2013-01-09T19:15:14.270

1

just change this: You dont need to use the '' because its an int

string strSql = @"SELECT customerID +'\' + forename + '\' + surname AS FullName, surname FROM customer WHERE [customerID]=" + txtCustomerID.Text;

But as other have told you this is not a good practice to use string concatenations to build SQL queries, and more if its with parameters or data that you get from the users.

edited Jan 09 '13 at 19:15

answered Jan 09 '13 at 16:50

Luis Tellez

2,785
1
20
28

now im getting this error "syntax error (missing operator) in query expression '[CustomerID]='" – Bunion Jan 09 '13 at 16:58
Are you sure its & and not + for concatenation? – Alex K. Jan 09 '13 at 17:03
both + and & give me the same exception unfortuntally :/ – Bunion Jan 09 '13 at 17:07
@Bunion, try this: SELECT customerID, forename + \" \"+surname AS FullName, surname FROM customer; If it works, you only need to change your query so that it uses the parameter object for the where clause – platon Jan 09 '13 at 17:11
I just edited my awnser please try it out and let us know how it worked. – Luis Tellez Jan 09 '13 at 19:15

Dour High Arch · Answer 2 · 2013-01-09T21:50:12.850

0

As everyone says; do not construct queries from text fields, use a parameterized query:

SqlCommand sqlQuery = new SqlCommand("SELECT customerID, forename + ' ' + surname AS FullName FROM customer WHERE customerID = @customerID", sqlConnection);
sqlQuery.Parameters.AddWithValue("@customerID", Int32.Parse(txtCustomerID.Text));

You might also want to do some error-checking on txtCustomerID.Text.

edited Jan 09 '13 at 21:50

answered Jan 09 '13 at 21:24

Dour High Arch

21,513
29
75
90

Searching for infromation out of a database using text input c#

2 Answers2