我用了ADO将一个DATAGRID和一个ACCESS数据库捆绑在了一起,现在想要做一个查询,我添加了一个combo控键和一个text控键,一个command,combo list里面我添加了3个数据库表的列名,我希望按一下command键能执行combo list 里面的选择的列的值等于text里面输入的值的结果,并且返回值后,datagrid会显示到这个记录,希望大家指点!!
Private Sub Command1_Click() Adodc1.RecordSource = "select * from [authors] where " & Me.Combo1.Text & "=" & Text1.Text Adodc1.Refresh End SubPrivate Sub Form_Load() Me.Combo1.AddItem "Au_ID" Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\练习储存\数据库文件\exp.MDB;Persist Security Info=False" Adodc1.RecordSource = "Select * from [authors]" Set Me.DataGrid1.DataSource = Adodc1 End Sub
大致如下(具体的请自己改过来):'引用ADO("工程"/"引用"/Microsoft ActiveX Data Objects 2.X Library)Dim cn As New ADODB.Connection, rs As New ADODB.RecordsetPrivate Sub Command1_Click() If Trim(Combo1.Text) = "" Then Exit Sub '没有选择列则退出Click事件 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;User Id=admin;Password=;" rs.CursorLocation = adUseClient '设置客户端游标
rs.Open "select * from 表 where " & Combo1.Text & "='" & Text1.Text & "'", cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = rs '把得到的结果显示到DataGrid1里 End SubPrivate Sub Form_Unload(Cancel As Integer) If rs.State <> adStateClosed Then rs.Close If cn.State <> adStateClosed Then cn.Close Set rs = Nothing Set cn = Nothing End Sub
Public Sub viewdata() '定义显示数据的函数 For i = 0 To 16 '列数(17列) If Data1.Recordset.Fields(i) <> "" Then 数据库表名(i).Text = Data1.Recordset.Fields(i) Else 数据库表名(i).Text = "" Next i End SubPrivate Sub ComFind_Click() On Error GoTo e
If Me.Text1.Text = "" Then MsgBox "请输入查询内容!", vbOKOnly + 32, "出错啦!" Me.Text1.SetFocus Exit Sub End If Data1.RecordSource = "select * from 数据库表名 where (数据库表名." & Combo1.Text & " " & "like " + Chr(34) + "*" + Text1.Text + "*" + Chr(34) + ")"
Data1.Refresh Call viewdata '调用函数
Exit Sub e: MsgBox "抱歉!没有纪录", vbOKOnly + 32, "出错啦!" End Sub
Adodc1.RecordSource = "select * from [authors] where " & Me.Combo1.Text & "=" & Text1.Text
Adodc1.Refresh
End SubPrivate Sub Form_Load()
Me.Combo1.AddItem "Au_ID"
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\练习储存\数据库文件\exp.MDB;Persist Security Info=False"
Adodc1.RecordSource = "Select * from [authors]"
Set Me.DataGrid1.DataSource = Adodc1
End Sub
If Trim(Combo1.Text) = "" Then Exit Sub '没有选择列则退出Click事件
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;User Id=admin;Password=;"
rs.CursorLocation = adUseClient '设置客户端游标
rs.Open "select * from 表 where " & Combo1.Text & "='" & Text1.Text & "'", cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = rs '把得到的结果显示到DataGrid1里
End SubPrivate Sub Form_Unload(Cancel As Integer)
If rs.State <> adStateClosed Then rs.Close
If cn.State <> adStateClosed Then cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
1.使用ADO控件在他的属性里面连接到数据库。
2.设置你的DATAGRID的DATASOURE值为ADODC1
3.你添加一个DataCombo1控件!(你不必添加一个command,combo list),你添加了这个控件后,你直接设置他的两个属性,一个是DataSoure把他设置为ADODC1把DATAFIEL设置成你想要显示的字段名。然后再设置他的查询语句。
"select * from 表名 where 条件1='" + DataCombo1.Text + "'and 条件2='" + Text1(0).Text + "'", cnn, adOpenKeyset, adLockOptimistic
For i = 0 To 16 '列数(17列)
If Data1.Recordset.Fields(i) <> "" Then 数据库表名(i).Text = Data1.Recordset.Fields(i) Else 数据库表名(i).Text = ""
Next i
End SubPrivate Sub ComFind_Click() On Error GoTo e
If Me.Text1.Text = "" Then
MsgBox "请输入查询内容!", vbOKOnly + 32, "出错啦!"
Me.Text1.SetFocus
Exit Sub
End If
Data1.RecordSource = "select * from 数据库表名 where (数据库表名." & Combo1.Text & " " & "like " + Chr(34) + "*" + Text1.Text + "*" + Chr(34) + ")"
Data1.Refresh
Call viewdata '调用函数
Exit Sub
e:
MsgBox "抱歉!没有纪录", vbOKOnly + 32, "出错啦!"
End Sub
rs.Open "select * from table1 where " & Combo1.Text & "='" & Text1.Text & "'", cn, adOpenKeyset, adLockOptimistic
运行到这句出错~~~
另外,上面的代码不完整,需要补充一写条件,具体的自己改过来Private Sub Command1_Click()
If Trim(Combo1.Text) = "" Then Exit Sub '没有选择列则退出Click事件 If cn.State <> adStateOpen Then cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;User Id=admin;Password=;"
If rs.State <> adStateClosed Then rs.Close
rs.CursorLocation = adUseClient '设置客户端游标 rs.Open "select * from 表 where " & Combo1.Text & "='" & Text2.Text & "'", cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = rs '把得到的结果显示到DataGrid1里
End Sub