data in datagridiew with different name search in winforms

Question

0.00/5 (No votes)

See more:

hi i need to search data in datagrid view with different fields
i am geting error for that syntax error at keyword WHERE
but it is executing for single field but i need two more field to search with one textbox
please help me...

my code is here

C#

private void button1_Click(object sender, EventArgs e)
{

  SqlConnection con = new SqlConnection("Data Source=SUBHAN-PC;Initial Catalog=subhan;Integrated Security=True");

  DataTable dt = new DataTable();

  string query = "SELECT * FROM ghtdetails"; // Query for all rows
  // If there is any text in textBox1 then add WHERE clause to your query
  if (!string.IsNullOrWhiteSpace(textBox1.Text))
  {
      query = query + " WHERE EmployeeName LIKE '%" + textBox1.Text + "%'";
    query = query + " WHERE EmployeeEmailId LIKE '%" + textBox1.Text + "%'";
      
      


  }
  SqlDataAdapter da = new SqlDataAdapter(query, con);

  da.Fill(dt);
  da.ToString();
  dataGridView1.DataSource = dt;

Posted 26-Nov-14 2:25am

123456789shaik

Updated 26-Nov-14 2:35am

Tomas Takac

v3

Add a Solution

Comments

Richard Deeming 26-Nov-14 9:13am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

123456789shaik 26-Nov-14 11:14am

hi
how can i add a button to this datagridview

4 solutions

Solution 3

This is not a good practice. You should always use Parameterized Query to avoid SQL Injection Attack.

Explore more from the following links.
1. Using Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]
2. [MSDN] How to: Execute a Parameterized Query[^]
3. C# SqlParameter[^]

Posted 26-Nov-14 2:58am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Solution 2

You have WHERE twice there. Should be like this:

C#

query = query + " WHERE EmployeeName LIKE '%" + textBox1.Text + "%'";
query = query + " AND EmployeeEmailId LIKE '%" + textBox1.Text + "%'";

Posted 26-Nov-14 2:39am

Tomas Takac

Comments

Richard Deeming 26-Nov-14 9:14am

You have copied the SQL Injection[^] vulnerability from the question.

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Tomas Takac 26-Nov-14 9:29am

Well, the question was about syntax error in SQL, this answers it. Although looking at it now the other answer with OR makes more sense. Sometimes I point out the other problems with the code, sometimes I don't. I will try to be more thorough next time. But is it really a reason for yelling and downvoting?

Richard Deeming 26-Nov-14 9:38am

SQLi is one of the most common vulnerabilities, one of the easiest to exploit[^], and one of the easiest to fix.

Whenever I see an answer promoting vulnerable code, even if the vulnerability is just copied from the original question, I'll down-vote it and add an explanatory comment. Otherwise, novice developers might copy the code from the answer without realising there's a problem.

If you update your answer to remove the vulnerability, or at least point it out, then I'll update my vote.

Tomas Takac 26-Nov-14 10:10am

Fair enough. But you already posted your solution which is exactly how I would have updated mine so there is no point anymore, not for the OP at least. Cheers.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2014-11-26T03:19:00

You need to fix both your query syntax and the SQL Injection[^] vulnerability:

C#

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection("Data Source=SUBHAN-PC;Initial Catalog=subhan;Integrated Security=True"))
using (SqlCommand cmd = con.CreateCommand())
{
    string query = "SELECT * FROM ghtdetails";
    if (!string.IsNullOrWhiteSpace(textBox1.Text))
    {
        query += " WHERE EmployeeName Like '%' + @Filter + '%'";
        query += " OR EmployeeEmailId Like '%' + @Filter + '%'";
        cmd.Parameters.AddWithValue("@Filter", textBox1.Text);
    }
    
    cmd.CommandText = query;
    
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);
}

dataGridView1.DataSource = dt;

Er. Dinesh Sharma · Accepted Answer · 2014-11-26T02:37:00

Solution 1

Try this

C#

string query = "SELECT * FROM ghtdetails"; // Query for all rows
// If there is any text in textBox1 then add WHERE clause to your query
if (!string.IsNullOrWhiteSpace(textBox1.Text))
{
query = query + "  WHERE EmployeeName LIKE '%" + textBox1.Text + "%'";
query = query + "  OR EmployeeEmailId LIKE '%" + textBox1.Text + "%'";
}

Posted 26-Nov-14 2:37am

Er. Dinesh Sharma

Comments

Richard Deeming 26-Nov-14 9:14am

You have copied the SQL Injection[^] vulnerability from the question.

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

data in datagridiew with different name search in winforms

4 solutions

Solution 3

Solution 4

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us