Question

vb中利用ComBox控件、TxtBox控件、命令按钮！实现条件查询，并把符合条件的结果显示在MSHFlexGrid表中！

窗体结构如图所示！请问要实现这样的功能的代码怎么写？十万火急啊！！！满意的我会加分的！我要完整的代码！ 我用的是SQL Server2000数据库！不要用ADODC类似的控件！我要全是代码的！

Answer 1

你好，给你段相对完整的代码（基本是点到为止，一些地方需要你来完善），算是抛砖引玉吧。
与你要求不同的是：我把【值】的输入，改成了用ComBox。当你用Combo1选择了字段后，程序帮你把该字段的值不重复地刷进Combo3，当数据量不大的情况下，“选择”会比键盘输入来得快（当然喜欢输入也是允许的）。

用到的控件：
Combo1：字段 (ComboBox)
combo2：关系
Combo3：值
MSHFlexGrid1 表格（MSHFLXGD.OCX）
Command1 '确定
设置：
Combo1.Style = 2
Combo2.Style = 2

引用 ADO（即：Microsoft Activex Data Objects 2.6 Library，不是控件）

注意：粘贴下列代码后根据你用的数据库修改以下内容

1 数据库类型：
Private Const mDbType = "Access" 或 Private Const mDbType = "SQL"
2 设置连接字串：
Private Const sConnect = 详见程序中的【注】2
3 设置要操作的表：
Private Const mBiaoName = 名称 见【注】3

粘贴下列代码：
-----------------------------------------------------------------------
Option Explicit
Private Const MARGIN_SIZE = 60 ' 单位为缇
'列排序变量
Private m_iSortCol As Integer
Private m_iSortType As Integer
'列拖拽变量
Private m_bDragOK As Boolean
Private m_iDragCol As Integer
Private xdn As Integer, ydn As Integer

Private db As ADODB.Connection
Private Rs As ADODB.Recordset

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'【注】1：在此设置你使用的数据库类型
Private Const mDbType = "Access" '或="SQL"

'【注】2：在此设置连接字符串: 本例使用的库名为【CANDETMGL】
' 连接【Access】数据库的话：把第一行代码中【E:\数据库\CANDETMGL.MDB】
' 换成你的数据库就行了。 连接【SQL】数据库的话,如果把第二行的【CANDETMGL】
' 换成你的数据库连不上的话,自己写吧
'
Private Const sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=E:\数据库\CANDETMGL.MDB"
' Private Const sConnect = "Provider=SQLOLEDB.1;Password='';Persist Security Info=True;User ID=sa;Initial Catalog='CANDETMGL' ;Data Source=(local)"

'【注】3：在此设置要操作表的名称，本例表名为【商品库存】
Private Const mBiaoName = "商品库存"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Type DTSZ '自定义数据结构
FieldName As String
FieldType As String
End Type
Dim SZ() As DTSZ '创建动态数组

Private Function mOpenDB() As Boolean
'================================================
'功能： 建立连接
'================================================
Err.Clear
On Error GoTo mErrDb
mOpenDB = False '默认建立连接失败
Screen.MousePointer = 11
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.ConnectionString = sConnect
db.ConnectionTimeout = 15
db.Open
mOpenDB = True '标记连接成功
Screen.MousePointer = 0
On Error GoTo 0
Exit Function
mErrDb:
Screen.MousePointer = 0
MsgBox "数 据 库 连 接 失 败 !", vbInformation, "提示"
On Error GoTo 0

End Function

Private Sub mSetCombo12()
'===============================================
'功能: 填充ComboBox控件
'===============================================
Dim I As Integer
Combo1.AddItem " "

For I = 0 To Rs.Fields.Count - 1
ReDim Preserve SZ(I) '动态数组增加元素
SZ(I).FieldName = Rs.Fields(I).Name '记录字段名
SZ(I).FieldType = Rs.Fields(I).Type '记录字段类型

'把不适合分组或不能比较或排序的类型排除在外 比如：text、ntext 、 image 等数据类型
Select Case Trim(SZ(I).FieldType)
Case "129", "200", "202", "3", "5", "131", "8"
Combo1.AddItem Trim(SZ(I).FieldName) '添加字段名到Combo1
End Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'注：下面这行代码能把字段名和类型打印到立即窗口,编写组织SQL条件语句时会用到
'Debug.Print SZ(I).FieldName & " " & SZ(I).FieldType
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next I

Combo2.AddItem " "
Combo2.AddItem "大于" ' ">"
Combo2.AddItem "等于" '"="
Combo2.AddItem "小于" '"<"
Combo2.AddItem "大于等于" '">="
Combo2.AddItem "小于等于" '"<="
Combo2.AddItem "不等于" '"<>"
Combo2.AddItem "包含" '"<>"

Combo1.ListIndex = 0
Combo2.ListIndex = 0

End Sub

Private Sub mSetCombo3()
'===================================================
'功能: 刷新 Combo3
'===================================================
Dim S As String
Dim Rs1 As ADODB.Recordset
S = Trim(Combo1.Text)
If S = "" Then Exit Sub

Set Rs1 = New ADODB.Recordset
Rs1.Open "SELECT " & S & " From " & mBiaoName & " GROUP BY " & S, db, adOpenStatic, adLockReadOnly

Combo3.Clear
Combo3.AddItem "全部记录"
Do While Not Rs1.EOF()
If Not IsNull(Rs1.Fields(S)) Then Combo3.AddItem Rs1.Fields(S)
Rs1.MoveNext
Loop
Rs1.Close
Set Rs1 = Nothing

End Sub

Private Sub mSetGrid()
'=====================================================
'功能： 绑定数据源并设置表格
'=====================================================
Dim I As Integer
Set MSHFlexGrid1.DataSource = Rs
If Rs.RecordCount < 1 Then Exit Sub

With MSHFlexGrid1

.Redraw = False
For I = 0 To .Cols - 1 '设置网格列宽度
.ColWidth(I) = -1
Next I

'设置网格样式
.AllowBigSelection = True
.FillStyle = flexFillRepeat

'将标头作成粗体
.Row = 0
.Col = 0
.RowSel = .FixedRows - 1
.ColSel = .Cols - 1
.CellFontBold = True

For I = .FixedCols To .Cols() - 1 Step 2 '隔列变灰
.Col = I
.Row = .FixedRows
.RowSel = .Rows - 1
.CellBackColor = &HC0C0C0 ' 浅灰
Next I

.AllowBigSelection = False
.FillStyle = flexFillSingle
.Redraw = True

End With

End Sub

Private Sub mOpenRs(TJ As String)
'========================================================================
'功能：打开满足条件的记录集
'========================================================================
Dim sSQL As String

If Trim(TJ) = "" Then '无条件
sSQL = "SELECT * FROM " & mBiaoName
Else
sSQL = "SELECT * FROM " & mBiaoName & " WHERE " & TJ
End If

If Not Rs Is Nothing Then '如果已经定义了RS
If Rs.State <> 0 Then '如果没有关闭则关闭之
Rs.Close '关闭RS
Set Rs = Nothing '释放
End If
End If

Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open sSQL, db, adOpenForwardOnly, adLockReadOnly

End Sub

Private Function mTiaoJian() As String
'==================================================
'功能: 返回组织好的SQL条件
'==================================================

Dim I As Integer, X As Integer
Dim S1 As String, S2 As String, S3 As String
mTiaoJian = ""

If Trim(Combo1.Text) = "" Or Trim(Combo2.Text) = "" Then
MsgBox "条 件 参 数 残 缺 , 请 更 正 后 重 试 !", vbInformation, "提示"
Exit Function
End If

X = UBound(SZ) '获取数组的指定维的最大可用下标
S1 = Trim(Combo1.Text)
For I = 0 To X '获取字段类型
If Trim(SZ(I).FieldName) = S1 Then
S1 = SZ(I).FieldType
Exit For
End If
Next I

Select Case Trim(Combo2.Text)
Case "大于"
S3 = ">"
Case "等于"
S3 = "="
Case "小于"
S3 = "<"
Case "大于等于"
S3 = ">="
Case "小于等于"
S3 = "<="
Case "不等于"
S3 = "<>"
End Select

S2 = Trim(Combo1.Text) & " " & S3

'【注】下面这段代码你自己完善吧，相信你能搞定
Select Case S1
Case "129", "200", "202" '字串类型

If Trim(Combo2.Text) = "包含" Then
If mDbType = "Access" Then
S2 = "(((InStr([" & Trim(Combo1.Text) & "],'" & Trim(Combo3.Text) & "'))>0))"
Else
S2 = "charindex('" & Trim(Combo3.Text) & "'," & Trim(Combo1.Text) & " )>0 "
End If
Else
S2 = S2 & "'" & Trim(Combo3.Text) & "'"
End If

Case "3", "5", "131" '数字类型
S2 = S2 & Trim(Combo3.Text)
Case "8" '日期类型
'S2 = .....................
End Select
mTiaoJian = S2

End Function

Private Sub Combo1_Click()
mSetCombo3 '刷新Combo3
End Sub

Private Sub Combo3_Click()
If Combo3.Text = "全部记录" Then
Combo1.ListIndex = 0
Combo2.ListIndex = 0
End If
End Sub

Private Sub Command1_Click()

Dim S As String
If Combo3.Text = "全部记录" Then
mOpenRs ""
Else
S = mTiaoJian() '组织条件
mOpenRs S '根据条件筛选满足条件的记录
End If
mSetGrid '绑定数据源并设置表格

End Sub

Private Sub Form_Load()

Me.Caption = "【数据查询】"
Command1.Caption = "确定"
Picture1.Align = 1
Picture1.BorderStyle = 0

Dim B As Boolean
B = mOpenDB() '建立连接
If B = False Then Unload Me

mOpenRs "" '打开满足条件的记录集
mSetGrid '绑定数据源并设置表格
mSetCombo12 '填充【Combo1】和【Combo2】
Combo3.AddItem "全部记录"
Combo3.ListIndex = 0

End Sub

Answer 2

Dim mycon1 As New ADODB.Connection, rs1 As New ADODB.Recordset

Private Sub Form_Load()
mycon1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=你的数据库名称"
Set rs1.ActiveConnection = mycon1
'需要引用ADO（引用Microsoft Activex Data Objects 2.6 Library，不是控件），如果不添加引用，上面两句修改为：
Set mycon1 = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
'如果不添加引用，过程外的：Dim mycon1 As New ADODB.Connection, rs1 As New ADODB.Recordset修改为：
Dim mycon1, rs1
End Sub

Private Sub Command1_Click()
On Error Resume Next
s = Split("=,>,>=,<,<=,<>,like")(Combo2.ListIndex) 's为运算符
'需要判断Combo1的类型（可用rs1.Fields(i).Type来判断，其中i为字段序号），是文本、数值、日期还是...
'假定为数值，执行以下代码：
If Combo2.ListIndex > 5 Then Exit Sub '假定为数值，则like选项不宜使用
rs1.Open "select * from 你的表名 where " & Combo1.Text & s & Val(Text1), mycon1, 1, 3
MSHFlexGrid1.Cols = rs1.Fields.Count + 1
For i = 0 To rs1.Fields.Count - 1
MSHFlexGrid1.TextMatrix(0, i + 1) = rs1.Fields(i).Name
Next
Do While Not rs1.EOF
r = MSHFlexGrid1.Rows - 1
For i = 0 To rs1.Fields.Count - 1
s = rs1.Fields(i)
If IsNull(s) Then s = ""
MSHFlexGrid1.TextMatrix(r, i + 1) = s
Next
rs1.MoveNext
If Not rs1.EOF Then MSHFlexGrid1.Rows = r + 2
Loop
rs1.Close
End Sub

Answer 3

Private Sub Command2_Click()
dim ysh as string '运算符
select case trim(combo1(1).text)
case "等于"
ysh="="
case "大于"
ysh=">"
case "大于等于"
ysh=">="
case "小于"
ysh="<"
case "小于等于"
ysh="<="
case "不等于"
ysh="<>"
case "包含"
ysh="like"

end select
If combo1(0).text = "" Then
MsgBox "没有选择字段", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
sqltxt = "select * "
sqltxt = sqltxt & " from " & Trim(Combo1(0).Text)
If Combo1(3) = "" Or Combo1(2) = "" Then
MsgBox "条件不完整或没有条件", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
sqltxt = sqltxt & " where " & Trim(Combo1(2).Text) & ysh & '"& text1.text &"'"
End If
End Sub

Answer 4

动态修改 textBox1.Size.Height 比如你当前的字号 用18px可以显示 那么 如果除非用别的控件，或者重写TextBox 参考资料：c# 放一个BindingSource，