filter data by query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

Iam try to filter but query not working

private void btnFilter_Click(object sender, EventArgs e)
       {

           if (dtpFrom.Value.ToShortDateString() != "" && dtpTo.Value.ToShortDateString() != "" && comboBoxse.SelectedValue.ToString() != "")
           {
               sWhere = "Where  DocDate Between '" + dtpFrom.Text + "' AND '" + dtpTo.Text + "' AND '" +  " FirstName ='" + comboBoxse.SelectedValue.ToString()+ "'";
           }

           SqlDataAdapter objAdapter = new SqlDataAdapter(@"Select distinct DocEntry,SeriesName,DocNum,Docdate,U_C_Rcpt,TrnspName,CardCode,CardName,CustEMail,SlpName,SalesEmpEMail,FirstName,LastName,ExecMail  from  SAPSALES.ORDERSTATUS " + sWhere + "", SettingManger.Instance.Conn);
           DataTable objTable = new DataTable();
           objAdapter.Fill(objTable);

           dataGridView1.DataSource = objTable;
           dataGridView1.Columns[0].Width = 25;
           for (int i = 1; i < dataGridView1.Columns.Count; i++)
           {
               dataGridView1.Columns[i].ReadOnly = true;

           }





       }

Posted 22-Jan-14 21:52pm

Master Vinu

Updated 22-Jan-14 21:57pm

v2

Add a Solution

3 solutions

Solution 3

Don't do that! Why convert DateTime values to a (locale specific) string format, concatenate these to form an SQL command, and then send them to SQL to be converted back to DateTime values?

For that matter, why are you assuming that a DateTimePicker.Value property can ever generate a empty string?

Use a parameterized query, and pass the DateTime value in directly.
You will probably find your problem disappears...

Posted 22-Jan-14 22:01pm

OriginalGriff

Solution 2

This is horrible code. BURN it. I can erase your database if I can browse to this web page. Read up on SQL injection attacks.

Instead, write a proc that does this

where (@from is null or docdate > @from) and (@to is null or docdate < @to) and (@firstName is null or FirstName = @FirstName)

That's how you create optional parameters in SQL.

Once you do things properly, it will just start working, I am sure. I do recommend learning to use your debugger though. If you were to intercept the generated SQL and run it in your Management Studio, I bet you'd find out why it's not working as you expect.

Posted 22-Jan-14 21:58pm

Christian Graus

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)

Karthik_Mahalingam · Accepted Answer · 2014-01-22T21:58:00

Solution 1

change this, one quote is extra...

C#

"' AND '" +

C#

" AND '" +

+ "' AND '" + " FirstName ='" +

Note: be aware of SQL_injection[^]

Its always safe to use sql parameterized query
how-do-i-create-a-parameterized-sql-query-why-should-i[^]

Posted 22-Jan-14 21:58pm

Karthik_Mahalingam

Updated 22-Jan-14 22:02pm

v4

Comments

Christian Graus 23-Jan-14 3:59am

Yes, but it will still be a very stupid way to do things....

Master Vinu 23-Jan-14 4:02am

ya i have removed but gives error:

Incorrect syntax near 'MOHAN'.
Unclosed quotation mark after the character string ''.

Karthik_Mahalingam 23-Jan-14 4:03am

keep a break point on this line DataTable objTable = new DataTable(); and check what is the string on sWhere ???

Master Vinu 23-Jan-14 4:06am

it gives:
Where DocDate Between '1/22/2014' AND '1/23/2014 FirstName ='MOHAN'

Karthik_Mahalingam 23-Jan-14 4:09am

use this

sWhere = "Where DocDate Between '" + dtpFrom.Text + "' AND '" + dtpTo.Text + "' AND " + " FirstName ='" + comboBoxse.SelectedValue.ToString() + "'";

Master Vinu 23-Jan-14 4:18am

thx bro...

Karthik_Mahalingam 23-Jan-14 4:25am

Thanks Vinayak
:)