Problem in search data from database in C# winform

Question

0.00/5 (No votes)

See more:

i Try search textBox Value From DataBase and i use this Code and it's Work fine

C#

private DataTable PopulateDataGridView()
        {
            
                string query = "SELECT Number, CustomerName, MobileNo, SecMobileNo FROM customer";
                query += " WHERE Number LIKE '%' + @SearchTerm + '%'";
                query += " OR CustomerName LIKE '%' + @SearchTerm + '%'";
                query += " OR MobileNo LIKE '%' + @SearchTerm + '%'";
                query += " OR SecMobileNo LIKE '%' + @SearchTerm + '%'";
                query += " OR @SearchTerm = ''";
                string constr = ConfigurationManager.ConnectionStrings["TestData"].ConnectionString;
                using (OleDbConnection con = new OleDbConnection(constr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(query, con))
                    {
                        cmd.Parameters.AddWithValue("@SearchTerm", txtName.Text.Trim());
                        using (OleDbDataAdapter sda = new OleDbDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            return dt;
                        }
                    }
                }
            }

but When i Try in my datatable show and search only the Data Whose Status Columns Value is "OPEN" But my Code not Work and data show like my Above/Old Code without any filter

What I have tried:

C#

private DataTable PopulateDataGridView()
        {
            string query = "SELECT Number, CustomerName, MobileNo, SecMobileNo, Status FROM customer";
                query += " WHERE Number LIKE '%' + @SearchTerm + '%'";
                query += " OR CustomerName LIKE '%' + @SearchTerm + '%'";
                query += " OR MobileNo LIKE '%' + @SearchTerm + '%'";
                query += " OR SecMobileNo LIKE '%' + @SearchTerm + '%'";
                query += " OR @SearchTerm = ''";
                query += "AND Status=@Status";
                string constr = ConfigurationManager.ConnectionStrings["TestData"].ConnectionString;
                using (OleDbConnection con = new OleDbConnection(constr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(query, con))
                    {
                        cmd.Parameters.AddWithValue("@SearchTerm", txtName.Text.Trim());
                        cmd.Parameters.AddWithValue("@Status", "OPEN");
                        using (OleDbDataAdapter sda = new OleDbDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            return dt;
                        }
                    }
                }
        }

Posted 20-May-20 22:52pm

Amar chand123

Updated 21-May-20 6:13am

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 21-May-20 11:37am

You have to debug this. What did you want us to do?

2 solutions

Solution 1

SQL

query += " OR @SearchTerm = ''";
query += "AND Status=@Status";

You are missing a space character between the two quotes ('') in the first of the above lines, and the AND in the second.

Posted 20-May-20 23:04pm

Richard MacCutchan

Comments

Amar chand123 21-May-20 5:10am

I try but still not work

Amar chand123 21-May-20 5:27am

and if i use this code then data show other then "OPEN" but Where Status is "OPEN" Data Not Show

string query = "SELECT Number, CustomerName, MobileNo, SecMobileNo, Status FROM customer WHERE Status=@Status ";
query += " OR Number LIKE '%' + @SearchTerm + '%'";
query += " OR CustomerName LIKE '%' + @SearchTerm + '%'";
query += " OR MobileNo LIKE '%' + @SearchTerm + '%'";
query += " OR SecMobileNo LIKE '%' + @SearchTerm + '%'";
query += " OR @SearchTerm = ' '";

string constr = ConfigurationManager.ConnectionStrings["TestData"].ConnectionString;
using (OleDbConnection con = new OleDbConnection(constr))
{
using (OleDbCommand cmd = new OleDbCommand(query, con))
{
cmd.Parameters.AddWithValue("@SearchTerm", txtName.Text.Trim());
cmd.Parameters.AddWithValue("@Status", "OPEN");
using (OleDbDataAdapter sda = new OleDbDataAdapter(cmd))
{

DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}

Richard MacCutchan 21-May-20 5:28am

Your code is checking if the status equals "OPEN", OR if any of the other conditions is true. Try a simple test with one search term and one status just to get your syntax correct. And please read carefully what you have coded.

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 · 2020-05-21T06:14:00

Your query is:

SQL

WHERE 
    Number LIKE ...
OR 
    CustomerName LIKE ...
OR 
    MobileNo LIKE ...
OR 
    SecMobileNo LIKE ...
OR 
    @SearchTerm = ''
AND 
    Status = @Status

Adding parentheses to match the implicit operator precedence[^] gives:

SQL

WHERE 
    Number LIKE ...
OR 
    CustomerName LIKE ...
OR 
    MobileNo LIKE ...
OR 
    SecMobileNo LIKE ...
OR 
(
    @SearchTerm = ''
AND 
    Status = @Status
)

In other words, if the Number, CustomerName, MobileNo, or SecMobileNo match, the record will be included regardless of the Status.

To override the operator precedence, you will need to explicitly add parentheses to your query:

SQL

WHERE 
(
    Number LIKE ...
OR 
    CustomerName LIKE ...
OR 
    MobileNo LIKE ...
OR 
    SecMobileNo LIKE ...
OR 
    @SearchTerm = ''
)
AND 
    Status = @Status

In code:

C#

string query = "SELECT Number, CustomerName, MobileNo, SecMobileNo, Status FROM customer";
query += " WHERE (Number LIKE '%' + @SearchTerm + '%'";
query += " OR CustomerName LIKE '%' + @SearchTerm + '%'";
query += " OR MobileNo LIKE '%' + @SearchTerm + '%'";
query += " OR SecMobileNo LIKE '%' + @SearchTerm + '%'";
query += " OR @SearchTerm = '')";
query += "AND Status = @Status";

NB: It may be easier to see the query if you use a verbatim string[^]:

C#

const string query = @"SELECT 
    Number, 
    CustomerName, 
    MobileNo, 
    SecMobileNo, 
    Status 
FROM 
    customer
WHERE
(
    Number LIKE '%' + @SearchTerm + '%'
Or
    CustomerName LIKE '%' + @SearchTerm + '%'
Or
    MobileNo LIKE '%' + @SearchTerm + '%'
Or
    SecMobileNo LIKE '%' + @SearchTerm + '%'
Or
    @SearchTerm = ''
)
And
    Status = @Status
";