组合查询 ——vb

来源：互联网发布：sql语言培训班编辑：程序博客网时间：2024/06/05 10:57

机房收费系统中需要组合查询，如图

需实现的功能如图

首先，我分析了查询的流程，并画了流程图，如下：

流程图

思路总结好了，下面就是代码的编写了（以学生上机统计信息为例）：

Dim mrc As ADODB.RecordsetDim txtSQL As StringDim Msgtxt As String   If Combo3(0).Text = "" Then                                 '不需要组合查询，第一行有为空值时提示错误信息       If Combo1(0).Text = "" Then           MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"           Exit Sub       Else         If Combo2(0).Text = "" Then            MsgBox "请选择操作符", vbOKOnly + vbExclamation, "警告"            Exit Sub         Else           If Text1.Text = "" Then             MsgBox "请填写要查询的内容", vbOKOnly + vbExclamation, "警告"             Exit Sub           Else                                 '组合关系框为空且条件成立时，建立的查询，只有第一行"   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''               txtSQL = "select * from line_info where "               txtSQL = txtSQL & FName(Combo1(0).Text) & Trim(Combo2(0).Text) & "'" & Trim(Text1.Text) & "'"  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''              Set mrc = ExecuteSQL(txtSQL, Msgtxt)                            If Not (mrc.BOF And mrc.EOF) Then                  MSFlexGrid1.Rows = 1                                    MSFlexGrid1.CellAlignment = 4                  MSFlexGrid1.TextMatrix(0, 0) = "卡号"                  MSFlexGrid1.TextMatrix(0, 1) = "姓名"                  MSFlexGrid1.TextMatrix(0, 2) = "上机日期"                  MSFlexGrid1.TextMatrix(0, 3) = "上机时间"                  MSFlexGrid1.TextMatrix(0, 4) = "下机日期"                  MSFlexGrid1.TextMatrix(0, 5) = "下机时间"                  MSFlexGrid1.TextMatrix(0, 6) = "消费金额"                  MSFlexGrid1.TextMatrix(0, 7) = "金额"                  MSFlexGrid1.TextMatrix(0, 8) = "备注"                  Do While Not mrc.EOF                                   '显示数据                 With MSFlexGrid1                 .Rows = .Rows + 1 '这种方式也可防止空白行的出现                 .CellAlignment = 4                 .TextMatrix(.Rows - 1, 0) = mrc.Fields(1)                 .TextMatrix(.Rows - 1, 1) = mrc.Fields(2)                 .TextMatrix(.Rows - 1, 2) = Format(mrc.Fields(6), "yyyy-mm-dd")                 .TextMatrix(.Rows - 1, 3) = mrc.Fields(7)                 .TextMatrix(.Rows - 1, 4) = Format(mrc.Fields(8), "yyyy-mm-dd")                 .TextMatrix(.Rows - 1, 5) = mrc.Fields(9)                 .TextMatrix(.Rows - 1, 6) = mrc.Fields(11)                 .TextMatrix(.Rows - 1, 7) = mrc.Fields(12)                 .TextMatrix(.Rows - 1, 8) = mrc.Fields(13)                                   mrc.MoveNext                 End With               Loop             Else                   MsgBox "该条件下没有记录！"             End If           End If        End If      End If   End If          If Not (Combo3(0).Text = "") Then                                        'combo3（0）不为空时，分两行或三行查询       If (Combo1(0).Text = "" Or Combo1(1).Text = "") Then           MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"              '第一行与第二行都不能有空值，否则报错           Exit Sub       Else         If (Combo2(0).Text = "" Or Combo2(1).Text = "") Then            MsgBox "请选择操作符", vbOKOnly + vbExclamation, "警告"            Exit Sub         Else           If (Text1.Text = "" Or Text2.Text = "") Then             MsgBox "请填写要查询的内容", vbOKOnly + vbExclamation, "警告"             Exit Sub           Else             If Combo3(1).Text = "" Then     'combo3（1）为空值时，前两行查询                       txtSQL = "select * from line_info where "                       txtSQL = txtSQL & FName(Combo1(0).Text) & " " & Trim(Combo2(0).Text) & "'" & Trim(Text1.Text) & "'" & FName(Combo3(0).Text) & " " & FName(Combo1(1).Text) & Combo2(1).Text & "'" & Trim(Text2.Text) & "'"                                     End If              If Not Combo3(1).Text = "" Then                         'combo3（1）不为空值时，三行查询                 If (Combo1(2).Text = "") Then                    MsgBox "请选择条件3字段名", vbOKOnly + vbExclamation, "警告"              '第一行与第二行都不能有空值，否则报错                    Exit Sub                 Else                    If (Combo2(2).Text = "") Then                      MsgBox "请选择条件3操作符", vbOKOnly + vbExclamation, "警告"                      Exit Sub                    Else                      If (Text3.Text = "") Then                         MsgBox "请填写条件3要查询的内容", vbOKOnly + vbExclamation, "警告"                         Exit Sub                      Else                     '没有空值                                               txtSQL = "select * from line_info where "                                                  txtSQL = txtSQL & FName(Combo1(0).Text) & Trim(Combo2(0).Text) & "'" & Trim(Text1.Text) & "'" & FName(Combo3(0).Text) & " " & FName(Combo1(1).Text) & Trim(Combo2(1).Text) & "'" & Trim(Text2.Text) & "' " & FName(Combo3(1).Text) & " " & FName(Combo1(2).Text) & Trim(Combo2(2).Text) & "'" & Trim(Text3.Text) & "'"                       End If                    End If                  End If               End If            End If          End If        End If     End If              Set mrc = ExecuteSQL(txtSQL, Msgtxt)                            If Not (mrc.BOF And mrc.EOF) Then                  MSFlexGrid1.Rows = 1                                    MSFlexGrid1.CellAlignment = 4                  MSFlexGrid1.TextMatrix(0, 0) = "卡号"                  MSFlexGrid1.TextMatrix(0, 1) = "姓名"                  MSFlexGrid1.TextMatrix(0, 2) = "上机日期"                  MSFlexGrid1.TextMatrix(0, 3) = "上机时间"                  MSFlexGrid1.TextMatrix(0, 4) = "下机日期"                  MSFlexGrid1.TextMatrix(0, 5) = "下机时间"                  MSFlexGrid1.TextMatrix(0, 6) = "消费金额"                  MSFlexGrid1.TextMatrix(0, 7) = "余额"                  MSFlexGrid1.TextMatrix(0, 8) = "备注"                  Do While Not mrc.EOF                                   '显示数据                 With MSFlexGrid1                 .Rows = .Rows + 1 '这种方式也可防止空白行的出现                 .CellAlignment = 4                 .TextMatrix(.Rows - 1, 0) = mrc.Fields(1)                 .TextMatrix(.Rows - 1, 1) = mrc.Fields(2)                 .TextMatrix(.Rows - 1, 2) = Format(mrc.Fields(6), "yyyy-mm-dd")                 .TextMatrix(.Rows - 1, 3) = mrc.Fields(7)                 .TextMatrix(.Rows - 1, 4) = Format(mrc.Fields(8), "yyyy-mm-dd")                 .TextMatrix(.Rows - 1, 5) = mrc.Fields(9)                 .TextMatrix(.Rows - 1, 6) = mrc.Fields(11)                 .TextMatrix(.Rows - 1, 7) = mrc.Fields(12)                 .TextMatrix(.Rows - 1, 8) = mrc.Fields(13)                                  mrc.MoveNext                 End With               Loop             Else                   MsgBox "该条件下没有记录！"             End If                     mrc.Close

逻辑不难，但是查询的时候我的思维有些问题，太麻烦了，在跟别人交流时，发现这种查询方法比我的简单多了，学到了很多，尤其是思维方式，自己应该积极转变，多多学习。

 txtSQL = "select * from line_info where " txtSQL = txtSQL & ...

0 0