0

I want to search the empid of a name that with the characters entered in my textbox. using wildcard characters. I wrote the statement 

da = new SqlDataAdapter(
 "Select empID from emp where FirstName like ' "+textbox1.text+" ' % "
   , connstring); 
da.Fill(ds);

Is this statement correct?

Aristos
  • 66,005
  • 16
  • 114
  • 150
Seema
  • 107
  • 2
  • 5
  • 13

3 Answers3

1

You are open for sql-injection, use sql-parameters instead:

string sql = "SELECT empID " +
              "FROM emp " + 
              "WHERE FirstName like @FirstName";
using(var con = new SqlConnection(connstring))
using (SqlCommand command = new SqlCommand(sql, con))
{
    command.Parameters.AddWithValue("@FirstName",  textbox1.text + "%");
    using(var da = new SqlDataAdapter(command))
    {
        da.Fill(ds);
    }
}

The % signs need to be part of the parameter value, and you don't need the single quotes at all when using binding parameters.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Im actually trying to return the empId of all those employees whose name starts with the textbox entered value. If i use Parameters and a stored procedure, how will i store the multiple rows returned to me from the database? That is why i was using the dataset. Is there another way out using parameters?. i thought that we could return only one row's values. – Seema Apr 01 '13 at 18:17
  • @Seema: I cannot follow. I don't use a stored-procedure, i use parameters, i return multiple empIDs for a given part of a `FirstName`, i'm using a `DataSet`, so does it work, or not? – Tim Schmelter Apr 01 '13 at 20:18
  • Yes i figured that out, Thanx a lot :) – Seema Apr 01 '13 at 22:02
1

The statement you have entered would allow for spaces in front of the first name and after the first name before the wildcard search. If you want to search for any part of a first name, you should change your SQL to something like this:

SELECT empID FROM emp WHERE FirstName LIKE '@FirstName%'

Also, it's much safer to use parameretized queries like this versus just concatenating your arguments:

StringBuilder sb = new StringBuilder();
sb.Append("SELECT empID FROM emp WHERE FirstName LIKE '@FirstName%'");

SqlConnection conn = new SqlConnection(connStr);
SqlCommand command = new SqlCommand(sb.ToString());
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("FirstName", textbox1.Text);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);

If you want to use a stored procedure, you'll need to setup your SqlCommand object like so:

SqlCommand command = new SqlCommand("Procedure", conn);
command.CommandType = Command.StoredProcedure;
Cameron Tinker
  • 9,634
  • 10
  • 46
  • 85
  • Im actually trying to return the empId of all those employees whose name starts with the textbox entered value. If i use Parameters and a stored procedure, how will i store the multiple rows returned to me from the database? That is why i was using the dataset. Is there another way out using parameters?. i thought that we could return only one row's values. – Seema Apr 01 '13 at 18:38
  • I've updated my answer to fill a DataTable. It's a few more lines than scartag's answer, but similar. I've explicitly created each object instead of using the shorthand methods that scartag used. – Cameron Tinker Apr 01 '13 at 19:10
  • so that means that i can use parameters with da and ds together if i have a stored procedure? – Seema Apr 01 '13 at 20:15
  • Yes, you can use them together. Stored procedures are designed to be parameterized. Be sure to define your parameters in your stored procedure appropriately. – Cameron Tinker Apr 01 '13 at 20:23
  • Please mark the answer that worked for you as the correct answer. This will help both you and the people who answered this question. – Cameron Tinker Apr 02 '13 at 13:28
0

There are numerous things wrong with that statement.

The simple one is that you have spaces between your single quotes and the textbox value and the percent sign is outside of where it needs to be. Also, textbox1.text is misspelled. It should be closer to: 

da = new SqlDataAdapter(
 "Select empID from emp where FirstName like '"+textbox1.Text+"%' ", connstring);

But that's just the first problem. The bigger issue is that this is a prime candidate for SQL Injection. See How do parameterized queries help against SQL injection?

Community
  • 1
  • 1
NotMe
  • 87,343
  • 27
  • 171
  • 245
  • can i use parameters with da and ds? – Seema Apr 01 '13 at 19:12
  • Yes.. Using parameters has nothing at all to do with whether you are using a `sqldataadapter` or `dataset`. Several answers to your question provide examples. – NotMe Apr 01 '13 at 21:34