1,216
社区成员
发帖
与我相关
我的任务
分享菜鸟请教3个小问题：查询语句、循环、存储过程
Private Sub Command1_Click()
Lv.ListItems.Clear '清空listview
'全字段模糊查询
If Combo1.Text = "模糊查询" Then
Call OpenConn
sql = "select * From CstInfo Where CstID Like '%" & Text1.Text & "%'" _
& "Or CstPYCode Like '%" & Text1.Text & "%'" _
& "Or CstName Like '%" & Text1.Text & "%'" _
& "Or CstTel Like '%" & Text1.Text & "%'" _
& "Or CstAddr Like '%" & Text1.Text & "%'" _
& "Or CstType Like '%" & Text1.Text & "%'" _
& "Or CstInure Like '%" & Text1.Text & "%'" _
& "Or CstPrice Like '%" & Text1.Text & "%'" _
& "Or CstArea Like '%" & Text1.Text & "%'" _
& "Or CstMny Like '%" & Text1.Text & "%'" _
& "Or CstMngor Like '%" & Text1.Text & "%'" _
& "Or CstMngorPY Like '%" & Text1.Text & "%'" _
& "Or CstMngorTel Like '%" & Text1.Text & "%'" _
& "Or OpData Like '%" & Text1.Text & "%'" _
& "Or CstOptor Like '%" & Text1.Text & "%'" _
& "Or rmk Like '%" & Text1.Text & "%'"
Call addInfo
'按Combo1的选定条件查询
ElseIf Combo1.Text <> "模糊查询" Then
'Dim Fed As String '条件匹配字段
If Combo1.Text = "按电话查" Then
sql = "select * From CstInfo Where CstTel Like '%" & Text1.Text & "%'" _
& "Or CstMngorTel Like '%" & Text1.Text & "%'" _
& "Or Rmk Like '%" & Text1.Text & "%'"
' Fed = "CstTel"
' Fed = "CstMngorTel"
End If
If Combo1.Text = "按拼音查" Then
sql = "select * From CstInfo Where CstPYCode Like '%" & Text1.Text & "%'" _
& "Or CstMngorPY Like '%" & Text1.Text & "%'" _
& "Or Rmk Like '%" & Text1.Text & "%'"
' Fed = "CstMngorPY"
' Fed = "CstPYCode"
End If
If Combo1.Text = "按名称查" Then
sql = "select * From CstInfo Where CstName Like '%" & Text1.Text & "%'" _
& "Or CstMngor Like '%" & Text1.Text & "%'" _
& "Or Rmk Like '%" & Text1.Text & "%'"
' Fed = "CstName"
' Fed = "CstMngor"
End If
If Combo1.Text = "按地址查" Then
sql = "select * From CstInfo Where CstAddr Like '%" & Text1.Text & "%'" _
& "Or Rmk Like '%" & Text1.Text & "%'"
' Fed = "CstAddr"
' Fed = "Rmk"
End If
Call addInfo
End If
End Sub这是一段全字段模糊查询和多字段条件查询的代码，看起来很繁杂，这些语句可以简化吗？怎样简化？
尤其是红色的这段代码，If又If，循环太多，还有什么更有效率的办法或更简单的代码能实现吗(最好能说明原理而不仅仅是代码)？
我在网上看到，较长的查询语句，可以写为存储过程进行多次、多处调用，存储过程是什么？能举个简单的例子吗？比如一小段代码，或一个小例子......
请各位兄台多多指教！小弟初次接触语言，什么都不懂，见笑了！
...全文
请发表友善的回复…
发表回复
现在还是人类 2008-09-27
- 打赏
- 举报
把东西作成函数可以简化代码，而且这样的判断用 Select 语句比较合适：
Dim SQL As String
Private Sub Command1_Click()
Lv.ListItems.Clear
SQL = ""
Select Case Combo1.Text
Case "模糊查询": SQL = GetMySQL(Text1.Text, "CstInfo", "CstID|CstPYCode|CstName|CstTel|CstAddr|CstType|CstInure|CstPrice|CstArea|CstMny|CstMngor|CstMngorPY|CstMngorTel|OpData|CstOptor|rmk")
Case "按电话查": SQL = GetMySQL(Text1.Text, "CstInfo", "CstTel|CstMngorTel|Rmk")
Case "按拼音查": SQL = GetMySQL(Text1.Text, "CstInfo", "CstPYCode|CstMngorPY|Rmk")
Case "按名称查": SQL = GetMySQL(Text1.Text, "CstInfo", "CstName|CstMngor|Rmk")
Case "按地址查": SQL = GetMySQL(Text1.Text, "CstInfo", "CstAddr|Rmk")
End Select
If Len(SQL) <> 0 Then
Call OpenConn
Call AddInfo
End If
End Sub
Function GetMySQL(SearchText As String, FormName As String, FieldNames As String) As String
Dim TempArray() As String, TempStr As String, X As Long
TempArray = Split(FieldNames, "|")
For X = 0 To UBound(TempArray)
If X = 0 Then
TempStr = TempArray(X) & " Like '%" & SearchText & "%'"
Else
TempStr = TempStr & " Or " & TempArray(X) & " Like '%" & SearchText & "%'"
End If
Next X
GetMySQL = "Select * From " & FormName & " Where " & TempStr
End Function
tongnaifu 2008-09-27
- 打赏
- 举报
使用case ,具体请查语法
select case Combo1.Text
case "模糊查询"
//语句1
case "按电话查"
//语句2
...
end select
select case Combo1.Text
case "模糊查询"
//语句1
case "按电话查"
//语句2
...
end select
多难成佛 2008-09-27
- 打赏
- 举报
谢谢两位，你们让我多了一条路！非常感谢！
