VB中復(fù)合查詢的實(shí)現(xiàn)

徐彬，李成

摘要：VB中的復(fù)選框可以實(shí)現(xiàn)多項(xiàng)選擇的功能。實(shí)際工作中，我們經(jīng)常需要用到復(fù)選框來實(shí)現(xiàn)復(fù)雜的查詢功能。該文以實(shí)例探討了VB中利用復(fù)選框?qū)崿F(xiàn)復(fù)合查詢的方法。

關(guān)鍵詞：VB；復(fù)選框；復(fù)合查詢

中圖分類號(hào)：TP311文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1009-3044(2012)02-0361-03

Approach for Complex Query on VB

XU Bin, LI Cheng

(Department of Information Engineering,Nanjing Yingtian College,Nanjing 210046, China)

Abstract: Check box can accomplish the function of multiple choice in VB.In practical work,we frequently use check box to complete the function of complex query. This paper gives example to discuss the methods of use check box to achieve complex query.

Key words: VB; check box; complex query

在VB中，復(fù)選框控件能夠?qū)崿F(xiàn)多重選擇的功能。利用該控件，結(jié)合數(shù)據(jù)庫，可以實(shí)現(xiàn)對(duì)數(shù)據(jù)表的復(fù)合查詢。本文通過具體實(shí)例討論了VB中如何利用復(fù)選框控件來實(shí)現(xiàn)對(duì)數(shù)據(jù)庫中數(shù)據(jù)的復(fù)合查詢。

1開發(fā)工具

軟件開發(fā)工具為VB，數(shù)據(jù)庫為Access。

VB是微軟公司開發(fā)的編程語言。它采用了面向?qū)ο蟮某绦蛟O(shè)計(jì)思想、事件驅(qū)動(dòng)的編程機(jī)制。它具備強(qiáng)大的數(shù)據(jù)庫訪問功能，提供了對(duì)多種數(shù)據(jù)庫的訪問方法，可以方便地訪問Oracle、SQL Server、Access等數(shù)據(jù)庫。

Access是一個(gè)功能強(qiáng)大、方便靈活、適用于單機(jī)環(huán)境下使用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，它同時(shí)提供了一個(gè)完整的數(shù)據(jù)庫應(yīng)用開發(fā)工具，可用于開發(fā)適合于特定的數(shù)據(jù)庫管理的Windows應(yīng)用程序。

2實(shí)現(xiàn)方法

在VB中，單擊“工程”菜單下的“部件”，勾選“Microsoft Hierarchical FlexGrid Control 6.0（OLEDB）”，即在工具箱中添加MSH? FlexGrid控件，用以顯示查詢結(jié)果；單擊“工程”菜單下的“引用”，勾選“Microsoft ActiveX Data Objects 2.8 Library”，即引用ADO對(duì)象。

Access數(shù)據(jù)庫名為“加分管理”，在“加分管理”數(shù)據(jù)庫中有一個(gè)名為“總表”的數(shù)據(jù)表，該數(shù)據(jù)表結(jié)構(gòu)如表1所示。

表1“總表”結(jié)構(gòu)

查詢界面中的復(fù)選框主要設(shè)計(jì)了“按性別”、“按專業(yè)”、“按科類”、“按總分”進(jìn)行復(fù)合查詢，查詢?cè)O(shè)計(jì)界面如圖1所示。“查詢”按鈕代碼如下：

Private Sub Command1_Click()

Dim s, s1, s2 As String

Dim str_text As String

Dim count,num As Integer

Dim i, j, k As Integer

Dim Mydb As New ADODB.Recordset

找出第一個(gè)被選中的復(fù)選框?qū)?yīng)的i值

For i = 0 To 3

If Check1(i).Value = 1 Then

Exit For

End If

Next i

Select Case i

Case 0

s = "xb=" & Combo1.Text & ""

Case 1

s = "zydh1 like " & Text6.Text & "%"

Case 2

s = "category like " & Combo2.Text & "%"

Case 3

s = "total between " & Val(Text7.Text) & " and " & Val(Text8.Text)

End Select

統(tǒng)計(jì)被選中的復(fù)選框數(shù)

For j = 0 To 3

If Check1(j).Value = 1 Then

count = count + 1

End If

Next j

If count = 1 Then

s = "select ksh as考生號(hào),xm as姓名,xb as性別,total as總分,zydh1 as報(bào)考第一專業(yè),category as類別from zong where " & s Set Mydb = ExeCutesql(s, str_text)

Set MSHFlexGrid1.DataSource = Mydb

Label4.Caption = str_text

ElseIf count > 1 Then

num = 1

For k = i + 1 To 3

If Check1(k).Value = 1 Then

Select Case k

Case 0

s1 = "xb=" & Combo1.Text & ""

s2 = s2 & " and " & s1

Case 1

s1 = "zydh1 like " & Text6.Text & "%"

s2 = s2 & " and " & s1

Case 2

s1 = "category like " & Combo2.Text & "%"

s2 = s2 & " and " & s1

Case 3

s1 = "total between " & Val(Text7.Text) & " and " & Val(Text8.Text)

s2 = s2 & " and " & s1

End Select

num = num + 1

If num = count Then Exit For

End If

Next k

s2 = s & s2

s = "select ksh as考生號(hào),xm as姓名,xb as性別,total as總分,zydh1 as報(bào)考第一專業(yè),category as類別from zong where " & s2 Set Mydb = ExeCutesql(s, str_text)

Set MSHFlexGrid1.DataSource = Mydb

Label4.Caption = str_text

End If

End Sub

此外還設(shè)計(jì)了兩個(gè)函數(shù)，分別用于數(shù)據(jù)庫的連接和執(zhí)行相應(yīng)查詢，函數(shù)如下：

連接數(shù)據(jù)庫

Private Function Connectstring() As String

Str_path = CurDir() & "" & "加分管理.MDB"

Connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Str_path & ";Persist Security Info=False" End Function

執(zhí)行查詢

Private Function ExeCutesql(ByVal Sql As String, Msgstring As String) As ADODB.Recordset

Dim cnn As New ADODB.Connection

Dim Rst As ADODB.Recordset

Dim Stokens() As String 數(shù)組

On Error GoTo executesql_error

Stokens = Split(Sql) 將sql語句按關(guān)鍵字保存在數(shù)組中

Set cnn = New ADODB.Connection

cnn.Open Connectstring

If InStr("INSERT,DELETE,UPDATE", UCase$(Stokens(0))) <> 0 Then

cnn.Execute Sql

Msgstring = Stokens(0) & "操作成功"

Else

Set Rst = New ADODB.Recordset

Rst.Open Trim$(Sql), cnn, adOpenKeyset, adLockOptimistic

Set ExeCutesql = Rst

Msgstring = "查詢到" & Rst.RecordCount & "條記錄"

End If

executesql_exit:

Set Rst = Nothing 釋放記錄集

Set cnn = Nothing 釋放連接語句

Exit Function

executesql_error:

Msgstring = "查詢錯(cuò)誤:" & Err.Description

Resume executesql_exit

End Function

查詢結(jié)果界面如圖2所示。

圖1查詢?cè)O(shè)計(jì)界面

圖2查詢結(jié)果界面

3結(jié)束語

以上實(shí)例對(duì)如何利用復(fù)選框控件實(shí)現(xiàn)對(duì)數(shù)據(jù)表的復(fù)合查詢等相關(guān)問題做了有益的探討，可以應(yīng)用于具體的數(shù)據(jù)庫系統(tǒng)的開發(fā)，具有較強(qiáng)的實(shí)用性。

參考文獻(xiàn)：

[1]石付,恒王斌.基于VB6的組合查詢系統(tǒng)的開發(fā)[J].電腦編程技巧與維護(hù),2004(1).

[2]沈祥玖.VB程序設(shè)計(jì)[M]. 2版.北京:高等教育出版社,2009.

[3]巫張英.Access數(shù)據(jù)庫基礎(chǔ)與應(yīng)用教程[M].北京:人民郵電出版社,2009.