我在form上放置了3个text(text1--text3)控件用于显示查询结果,一个text(text4)控件用于输入查询条件,一个command控件用于提交命令,现在,有一个问题:
我用cmd.CommandText = "select * from user where userid='123456'"
命令,程序能正常执行,如果将该语句改为变量:
cmd.CommandText = "select * from user where userid='"&tiaojian&"'"
则错误,写成rst.Filter = "userid='"&tiaojian&"'"
也是错误的,请问该怎么写过滤条件?
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim i As Integer
Dim tiaojian As String
tiaojian = Trim(Text4.Text) '输入的条件
Debug.Print tiaojian
cn.Open "DSN=ytf_access;DBQ=G:\MYWEB\FJSXGX\DATABASE\sxgx1.mdb;DefaultDir=G:\MYWEB\FJSXGX\DATABASE;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Set cmd.ActiveConnection = cn
cmd.CommandText = "select * from user where userid='123456'"
rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenDynamic, adLockBatchOptimistic
rst.Sort = "userid"
'rst.Filter = "userid='123456'"
With rst
Text1.Text = .Fields("userid")
Text2.Text = .Fields("userpass")
Text3.Text = .Fields("UserName")
End With
Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
我用cmd.CommandText = "select * from user where userid='123456'"
命令,程序能正常执行,如果将该语句改为变量:
cmd.CommandText = "select * from user where userid='"&tiaojian&"'"
则错误,写成rst.Filter = "userid='"&tiaojian&"'"
也是错误的,请问该怎么写过滤条件?
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim i As Integer
Dim tiaojian As String
tiaojian = Trim(Text4.Text) '输入的条件
Debug.Print tiaojian
cn.Open "DSN=ytf_access;DBQ=G:\MYWEB\FJSXGX\DATABASE\sxgx1.mdb;DefaultDir=G:\MYWEB\FJSXGX\DATABASE;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Set cmd.ActiveConnection = cn
cmd.CommandText = "select * from user where userid='123456'"
rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenDynamic, adLockBatchOptimistic
rst.Sort = "userid"
'rst.Filter = "userid='123456'"
With rst
Text1.Text = .Fields("userid")
Text2.Text = .Fields("userpass")
Text3.Text = .Fields("UserName")
End With
Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim i As Integer
Dim tiaojian As String
tiaojian = Trim(Text4.Text) '输入的条件
Debug.Print tiaojian
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\db1.mdb;Persist Security Info=False"
Set cmd.ActiveConnection = cn
'cmd.CommandText = "select * from user where userid='123456'"
cmd.CommandText = "select * from 收支表 where 说明='" & Trim(Text4.Text) & "'"
rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenDynamic, adLockBatchOptimistic
rst.Sort = "日期"
'rst.Filter = "userid='123456'"
With rst
Text1.Text = .Fields("日期")
Text2.Text = .Fields("金额")
Text3.Text = .Fields("说明")
End With
Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
我将&改成+就好了,不知道为什么,+是求和,&是连接,怎么用+行而用&不行呢?
猜测而已,有空查查msdn....
cmd.CommandText = "select * from user where userid='" & tiaojian & "'"