按查询过滤数据 [英] filter data by query

问题描述

Iam尝试过滤但查询无法正常工作

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;

           }





       }

推荐答案

改变这个，一个报价是额外的......

change this, one quote is extra...

"' AND '" +

" AND '" +

+'AND'+FirstName ='+



注意： 注意 SQL_injection [ ^ ]



使用 sql参数化查询总是安全的

how-do-i-create-a-parameterized-sql- query-why-should-i [ ^ ]

+ "' 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[^]

不要这样做！为什么将DateTime值转换为（特定于语言环境）字符串格式，将它们连接起来形成一个SQL命令，然后将它们发送到SQL以转换回DateTime值？



就此而言，为什么你假设DateTimePicker.Value属性可以生成一个空字符串？



使用参数化查询，并直接传递DateTime值。

你可能会发现你的问题消失了......

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...

这是一个可怕的代码。烧掉它吧。如果我可以浏览此网页，我可以删除您的数据库。阅读SQL注入攻击。



相反，写一个这样做的过程

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)

这就是你在SQL中创建可选参数的方法。



一旦你做得好，它就会开始工作，我相信。我建议学习使用你的调试器。如果您要拦截生成的SQL并在Management Studio中运行它，我打赌你会发现它为什么没有按预期工作。

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.

这篇关于按查询过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！