搜索10个字段
搜索10个字段
提问于 2011-11-23 09:06:42
我有10个字段，需要搜索由10个文本框组成，每个文本框在数据库中有自己的指定列，需要searched..It有一个搜索按钮。我的问题是，我无法控制这些框中的哪些将由user..Therefore填充，我需要动态检查字段是否为空或not..attached have是我的示例代码。我希望你们能给我一个更简单的方法。
文本框: 1.注册日期2.链接代码3.建筑物代码4.建筑物描述5.街道描述6.村庄描述7.地区描述8.地区描述9.州描述10.国家描述
在以下函数项下的示例代码中:参数actDate = textbox注册日期参数linkCode = textbox链接编码参数bldgCode = textbox Bldg code参数bldgDesc = textbox Bldg Desc
到目前为止，我只在我的代码中添加了4个textbox。我停下来是因为我想知道是否有更简单的方法来做到这一点。谢谢。
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
accountDt = New DataTable
accountDt = Items(txtActDate.Text, txtLinkCode.Text, txtBldgCode.Text, txtBldgDesc.Text, "select * from ac_account", "ACTIVATION_DATE_D")
LoadData(btnCheckAll, btnUnCheckAll, btnAccountWrite, btnAccountFirst, btnAccountPrevious, btnAccountNext, btnAccountLast, _
lblAccountPage, lstAccount, "ACCOUNT_NAME_V", "ACTIVATION_DATE_D", "ACTIVATED_BY_N", accountDt, "ACSP_AccountLoad", _
"ACTIVATION_DATE_D", btnMatrix)
txtActDate.Text = ""
txtLinkCode.Text = ""
txtBldgCode.Text = ""
txtBldgDesc.Text = ""结束子对象
Public Function Items(ByVal actDate As String, ByVal linkCode As String, ByVal bldgCode As String, ByVal bldgDesc As String, ByVal query As String, _
ByVal actRegDate As String) As DataTable
Try
If actDate <> "" And linkCode <> "" And bldgCode <> "" And bldgDesc <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and ACCOUNT_LINK_CODE_N = '" & linkCode & "' and BUILDING_CODE_V = '" & bldgCode & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf actDate <> "" And linkCode <> "" And bldgCode <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and ACCOUNT_LINK_CODE_N = '" & linkCode & "' and BUILDING_CODE_V = '" & bldgCode & "'"
ElseIf actDate <> "" And linkCode <> "" And bldgDesc <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and ACCOUNT_LINK_CODE_N = '" & linkCode & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf actDate <> "" And bldgCode <> "" And bldgDesc <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and BUILDING_CODE_V = '" & bldgCode & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf linkCode <> "" And bldgCode <> "" And bldgDesc <> "" Then
query = query & " where ACCOUNT_LINK_CODE_N = '" & linkCode & "' and BUILDING_CODE_V = '" & bldgCode & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf actDate <> "" And linkCode <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and ACCOUNT_LINK_CODE_N = '" & linkCode & "'"
ElseIf actDate <> "" And bldgCode <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and BUILDING_CODE_V = '" & bldgCode & "'"
ElseIf actDate <> "" And bldgDesc <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf linkCode <> "" And bldgCode <> "" Then
query = query & " where ACCOUNT_LINK_CODE_N = '" & linkCode & "' and BUILDING_CODE_V = '" & bldgCode & "'"
ElseIf linkCode <> "" And bldgDesc <> "" Then
query = query & " where ACCOUNT_LINK_CODE_N = '" & linkCode & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf bldgCode <> "" And bldgDesc <> "" Then
query = query & " where BUILDING_CODE_V = '" & bldgCode & "' and BUILDING_DESC_V = '" & bldgDesc & "'"
ElseIf actDate <> "" Then
query = query & " where convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "'"
ElseIf linkCode <> "" Then
query = query & " where ACCOUNT_LINK_CODE_N = '" & linkCode & "'"
ElseIf bldgCode.ToLower <> "" Then
query = query & " where BUILDING_CODE_V = '" & bldgCode & "'"
ElseIf bldgDesc.ToLower <> "" Then
query = query & " where BUILDING_DESC_V LIKE '" & bldgDesc & "%" & "'"
End If
conn.Open()
sqlCmd = New SqlCommand(query, conn)
sqlCmd.CommandType = CommandType.Text
sqlDt = New DataTable
sqlDa = New SqlDataAdapter(sqlCmd)
sqlDa.Fill(sqlDt)
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
Return sqlDt
End Function回答 2
Stack Overflow用户
发布于 2011-11-23 09:08:27
我们这样做(不幸的是，这是c#，但是.NET调用translate)：
List<String> phrases = new List<String>();
if (actDate != "") phrases.Add("convert(varchar(10),actRegDate,101) = '" + actDate.ToShortDateString() + "'";
if (linkCode != "") phrases.Add"(ACCOUNT_LINK_CODE_N = '" + linkCode + "'");继续向List添加短语。完成后，可以使用String.Join创建WHERE子句
String whereClause = String.Join(“AND "，词组)；
然后您可以在SQL中使用它。
一次警告:我强烈建议使用参数，而不是直接将值连接到短语中。当我以前这样做的时候，我只是将所有的参数添加到SqlCommand中，无论它们是否在实际的查询中。
但是，如果可以选择LINQ2SQL，它将为您完成所有这些工作，包括查询组合。
Stack Overflow用户
发布于 2011-11-23 20:08:33
对于每个非空的搜索框，您应该在WHERE中添加另一个条件。如果您不想跟踪某个条件是否已添加，请将1=1添加到start。每个搜索框一个IF。
query = query & "WHERE 1=1 "
IF actDate <> "" THEN
query = query & " AND convert(varchar(10)," & actRegDate & ",101) = '" & actDate & "'"
END IF
IF bldgCode <> "" THEN
query = query & " and BUILDING_CODE_V = '" & bldgCode & "'"
END IF每个搜索框都有额外的IF行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8236116
复制相关文章
相似问题
腾讯云开发者
