一个困扰我好久的问题：sql多条件查询！！

[亚历山大-马龙]

我现在想实现一个多条件查询，就是有n个可选条件，但是可以选，也可以不选，所以这样一来是不是就有了很多种if条件判断呢？我现在用的是n个chechbox来判断用户是否选中该查询条件，然后改变相应的查询条件。
想必各位都碰到过，
有没有更简单的实现方法呢？
请大家说说。

[leeight]

那就认为全部选上了，然后给用户没有选的赋默认的值， 最好将查询写成存储过程，以后修改起来比较容易的

[亚历山大-马龙]

各位大虾，谢了！！！！！！！！！

[阿露]

我有好多问题都不会怎么办啊？

[singlepine]

StringBuilder sql=new StringBuilder();
private void Page_Load(object sender, System.EventArgs e)
{

}
protected void Condition(Control ctrl)
{
foreach (Control c in ctrl.Controls)
{
if(c is CheckBox)
{
if(((CheckBox)(c)).Checked==true)
{
sql.Append(" and 1=1");//这里是你需要添加的where条件
}
}
Condition(c);
}
}
private void Button1_Click(object sender, System.EventArgs e)
{
Condition(this);
string s="select * from tablename where 1=1";
s=s+sql;
Response.Write(s);
}

[xiaoyuehen]

Private Function __GetBaseSql() As String
Dim strProviderCode As String = SessionGet_ProviderCode()
'查询参数列表
Dim strQueryString As String = ""

Dim strKeyword As String = toStr(Request.QueryString("kw")).Trim
Dim strType As Integer = toNum(Request.QueryString("t"), 0)
Dim strLib As String = toStr(Request.QueryString("Catalog")).Trim

strQueryString &= "&Catalog=" & strLib
strQueryString &= "&kw=" & System.Web.HttpUtility.UrlEncodeUnicode(strKeyword)
strQueryString &= "&t=" & strType.ToString

'类别条件
Dim strLibCondiction As String = ""

'其他条件
Dim strOtherCondiction As String = ""

'综合条件
Dim strQueryCondiction As String = ""

Dim strSql As String = ""
Select Case strType
Case 1
Dim booName As String = toStr(Request.QueryString("name")).Trim
Dim booCode As String = toStr(Request.QueryString("code")).Trim
Dim booProName As String = toStr(Request.QueryString("proname")).Trim
Dim booProCode As String = toStr(Request.QueryString("procode")).Trim

strQueryString &= "&name=" & booName
strQueryString &= "&code=" & booCode
strQueryString &= "&proname=" & booProName
strQueryString &= "&procode=" & booProCode

strSql = " from [Product]"

If strLib <> "" Then
strLibCondiction &= " WHERE lib_code LIKE '" & toSql(strLib) & "%'"
End If

If booName = "True" And strKeyword <> "" Then
strOtherCondiction &= " OR [name] LIKE '%" & toSql(strKeyword) & "%' OR [code] LIKE '%" & toSql(strKeyword) & "%'"
End If

If booCode = "True" And strKeyword <> "" And sys_CheckPopedomItem("4003", False) Then
strOtherCondiction &= " OR [productCode] LIKE '%" & toSql(strKeyword) & "%'"
End If

If booProName = "True" And strKeyword <> "" And sys_CheckPopedomItem("4002", False) Then
strOtherCondiction &= " OR [pro_name] LIKE '%" & toSql(strKeyword) & "%'"
End If

If booProCode = "True" And strKeyword <> "" And sys_CheckPopedomItem("4001", False) Then
strOtherCondiction &= " OR [pro_code] LIKE '%" & toSql(strKeyword) & "%'"
End If

If strOtherCondiction <> "" Then
If strLibCondiction <> "" Then
strOtherCondiction = " AND (" & Right(strOtherCondiction, Len(strOtherCondiction) - 4) & ")"
Else
strOtherCondiction = " WHERE " & Right(strOtherCondiction, Len(strOtherCondiction) - 4)
End If
End If

strQueryCondiction = strLibCondiction & strOtherCondiction

If strProviderCode <> "" Then
If strQueryCondiction <> "" Then
strQueryCondiction &= " AND pro_code = '" & toSql(strProviderCode) & "'"
Else
strQueryCondiction &= " WHERE pro_code = '" & toSql(strProviderCode) & "'"
End If
End If

strSql &= strQueryCondiction
Case 2
Dim booCart As String = toStr(Request.QueryString("cart")).Trim
Dim booCarf As String = toStr(Request.QueryString("carf")).Trim
If booCart <> "True" And booCart <> "True" Then booCart = "True"

strQueryString &= "&cart=" & booCart
strQueryString &= "&carf=" & booCarf

If strLib <> "" Then
strLibCondiction &= " AND lib_code LIKE '" & toSql(strLib) & "%'"
End If

strSql = " from [Product] WHERE [ID] IN (SELECT [ProductID] FROM [Product_car]"

If booCarf = "True" And strKeyword <> "" Then
strOtherCondiction &= " OR [name] LIKE '%" & toSql(strKeyword) & "%' "
End If

If booCart = "True" And strKeyword <> "" Then
strOtherCondiction &= " OR [bianhao] LIKE '%" & toSql(strKeyword) & "%' "
End If

If strOtherCondiction <> "" Then
strOtherCondiction = " WHERE " & Right(strOtherCondiction, Len(strOtherCondiction) - 4)
End If

strSql &= strOtherCondiction & ")" & strLibCondiction

If strProviderCode <> "" Then
strSql &= " AND pro_code = '" & toSql(strProviderCode) & "'"
End If
Case 3
strSql = " from [Product] WHERE [ID] IN (SELECT [ProductID] FROM [Product_OEMNO] WHERE [bianhao] LIKE '%" & toSql(strKeyword) & "%')"

If strLib <> "" Then
strLibCondiction &= " AND lib_code LIKE '" & toSql(strLib) & "%'"
End If

If strProviderCode <> "" Then
strLibCondiction &= " AND pro_code = '" & toSql(strProviderCode) & "'"
End If

strSql &= strLibCondiction
Case 4
strSql = " from [Product] WHERE [ID] IN (SELECT [ProductID] FROM [Product_quanwei] WHERE [bianhao] LIKE '%" & toSql(strKeyword) & "%')"

If strLib <> "" Then
strLibCondiction &= " AND lib_code LIKE '" & toSql(strLib) & "%'"
End If

If strProviderCode <> "" Then
strLibCondiction &= " AND pro_code = '" & toSql(strProviderCode) & "'"
End If

strSql &= strLibCondiction
Case 0
'直接访问
strSql = " from [Product]"
If strLib <> "" Then
strQueryCondiction &= " AND lib_code LIKE '" & toSql(strLib) & "%'"
End If

If strProviderCode <> "" Then
strQueryCondiction &= " AND pro_code = '" & toSql(strProviderCode) & "'"
End If

If strQueryCondiction <> "" Then
strQueryCondiction = " WHERE " & Right(strQueryCondiction, Len(strQueryCondiction) - 4)
End If

strSql &= strQueryCondiction
End Select

If strType >= 0 And strType < 5 Then
strQueryString = Right(strQueryString, Len(strQueryString) - 1)
'打印列表
lblPrint.Text = "<a href=""javascript:shows('Product_List_Print.aspx?" & strQueryString & "');"">打印(图)</a>"
'打印列表
lblPrintN.Text = "<a href=""javascript:shows('Product_List_PrintN.aspx?" & strQueryString & "');"">打印(简)</a>"
Else
'5 查询按钮触发后以字符串发送(如打印)
strSql = __GetNormalSearchSql(strKeyword, True)
End If

Return strSql
End Function



************************
*风吹鸡蛋壳, 财去人安乐*
************************

[guopeng_28]

其实可以这样做
sqlstr="select * from table where id>0"
在下面简单判断一下
if textbox.text<>"" and textbox.text<> "默认的那个值"
sqlstr=sqlstr &" and name ='" & textbox.text & "'"
end if
.
.
这样的话,有条件就加入where里面,没有的话,就跳过了