SQL数据库服务器和客户端位于同一网段的不同机器上。
在SQL数据库上建立了ODBC数据源,在客户端上使用如下语句连接:
Dim DB As New ADODB.Connection
Dim Rec(5) As New Recordset
DB.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=sqldb"
Rec(1).Open "SELECT * FROM INVOICE", DB, 1, 3
...Rec(0)没有被使用过
现在我想在Rec(1)中按照字段INV_ID进行记录查询
使用了两种方法:
1:
Set Rec(0) = DB.Execute("Select * From INVOICE where INV_ID like '" & Str & "'")
2:
Rec(1).Find "INV_ID ='" & Str & "'", 1, adSearchForward, 1
请问一下,上面哪种方法可以正确地进行模糊查询呢??
如果可以模糊查询的的话,得到的查询记录集是否可以进行MOVE操作呢??我使用第一种方法查询的时候,得到的Rec(0)似乎无法用MOVELAST,也就无法得到RecordCount了。
第二种方法我觉得似乎无法进行模糊查询,哪位知道确切结果的给个回答吧。谢谢
在SQL数据库上建立了ODBC数据源,在客户端上使用如下语句连接:
Dim DB As New ADODB.Connection
Dim Rec(5) As New Recordset
DB.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=sqldb"
Rec(1).Open "SELECT * FROM INVOICE", DB, 1, 3
...Rec(0)没有被使用过
现在我想在Rec(1)中按照字段INV_ID进行记录查询
使用了两种方法:
1:
Set Rec(0) = DB.Execute("Select * From INVOICE where INV_ID like '" & Str & "'")
2:
Rec(1).Find "INV_ID ='" & Str & "'", 1, adSearchForward, 1
请问一下,上面哪种方法可以正确地进行模糊查询呢??
如果可以模糊查询的的话,得到的查询记录集是否可以进行MOVE操作呢??我使用第一种方法查询的时候,得到的Rec(0)似乎无法用MOVELAST,也就无法得到RecordCount了。
第二种方法我觉得似乎无法进行模糊查询,哪位知道确切结果的给个回答吧。谢谢
rs.open "Select * From INVOICE where INV_ID like '" & Str & "%'",db,adopenkeyset,adlockreadonly
我目前用的是:
Set Rec(0) = DB.Execute("Select * From INVOICE where INV_ID like '" & Str & "'")
Str来自一个INPUTBOX输入。
zyg0能否说一下你是如何得到RecordCount的么?
楼上没有使用参数,我也尝试了加参数DB.Execute("...",1,1)结果依然有问题。
似乎返回的表无法使用游标,奇怪。
可以用切断记录集(类似于ADO.NET的DataSet)
还是ADO.NET好用
据说新出的 ADO.NET vNext相当于ORM了
用dlinq可以直接把查询写在程序里~
另外用一个查询吧,把Select * 换为Select count(*)
============================================
楼主的第一种方法不是从rec(1)取记录而是直接从Table中取记录吧
Dim Cn As New ADODB.Connection
Dim Tmp As New ADODB.RecordsetPublic Sub FilterField(rstTemp As ADODB.Recordset, StrField As String, strFilter As String)
rstTemp.Filter = StrField & " like '" & strFilter & "'"
Set Tmp = rstTemp
End SubPrivate Sub Command1_Click()
Dim Rst As New ADODB.Recordset
Call FilterField(Rst, "字段", "值%")
MsgBox Tmp.RecordCount
End Sub
不知能否满足兄弟的要求
本地建立的ODBC数据源: HYFP
数据是从原有的ACCESS中导入的.Dim DB As New ADODB.Connection
Dim Rec(6) As New Recordset
Dim SQL(10) As String
Dim I as Long
...
SQL(1) = "Select * From INVOICE"
SQL(2) = "Select * From BANK"
SQL(3) = "Select * From PORT"
SQL(4) = "Select * From VESSELS"
SQL(5) = "Select * From DETAIL"
DB.Open SQL(0)
For I = 1 To 5
Rec(I).Open SQL(I), DB, 1, 3
Rec(I).MoveLast
Next结果运行到I=2的时候Rec(I).MoveLast就出错,
而REC(1)就可以MOVELAST,可以得到RECORDCOUNT
而REC(2)就死活不行,明明有记录的.
居然在这里折腾了我两天,晕死.大家帮帮忙
Recordset不能使用数组操作
在你使用的时候
编译器会对 Rec(2)有2个理解
1,你理想中的Recordset数组
2 rec对象的第2个字段
我感觉他理解成了第2个意思因此出错 去掉数组吧
Dim Rec2 As New Recordset
Dim Rec3 As New Recordset
Dim Rec4 As New Recordset
Dim Rec5 As New RecordsetSQL(0) = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=admin;Initial Catalog=HYFP;Data Source=DHCP-SERVER"
SQL(1) = "Select * From INVOICE"
SQL(2) = "Select * From BANK"
SQL(3) = "Select * From PORT"
SQL(4) = "Select * From VESSELS"
SQL(5) = "Select * From DETAIL"DB.Open SQL(0) Rec1.Open SQL(1), DB, 1, 3
Rec2.Open SQL(2), DB, 1, 3
Rec3.Open SQL(3), DB, 1, 3
Rec4.Open SQL(4), DB, 1, 3
Rec5.Open SQL(5), DB, 1, 3Rec1.MoveLast
Rec2.MoveLast
Rec3.MoveLast
Rec4.MoveLast
Rec5.MoveLastDebug.Print Rec1.RecordCount
Debug.Print Rec2.RecordCount
Debug.Print Rec3.RecordCount
Debug.Print Rec4.RecordCount
Debug.Print Rec5.RecordCountRec1.Close
Rec2.Close
Rec3.Close
Rec4.Close
Rec5.CloseDB.Close
Set DB = Nothing各个表内均有数据,但是只能得到第一个表[INVOICE]的RECORDCOUNT
其余都是-1
Rec2.Open SQL(2), sql(0), 1, 3
Rec3.Open SQL(3), sql(0), 1, 3
Rec4.Open SQL(4), sql(0), 1, 3
Rec5.Open SQL(5), sql(0), 1, 3
'这样看看
但是如果改成RecX.Open SQL(X), sql(0), 1, 1 '只读
就可以得到RecordCount了.不过,依然有问题,且不说只读方式打开不能满足我的需求,我将原来的语句改成这样:
'Rec1.Open SQL(1), DB, 1, 3
Rec2.Open SQL(2), DB, 1, 3
Rec3.Open SQL(3), DB, 1, 3
Rec4.Open SQL(4), DB, 1, 3
Rec5.Open SQL(5), DB, 1, 3'Rec1.MoveLast
Rec2.MoveLast
Rec3.MoveLast
Rec4.MoveLast
Rec5.MoveLast只打开后面的4个表.但是结果依然无法得到RecordoCount
看上去又象是SQL表有问题,而同样的SQL语句在SQL管理器中执行却没有任何问题.
怪啊
SQL(2) = "Select * From BANK"
SQL(3) = "Select * From PORT"
SQL(4) = "Select * From VESSELS"
SQL(5) = "Select * From DETAIL"
你把sql语句位置颠倒下看看是不是还能返回第一个
2.换测试的数据库侃侃
如果还有这个问题 估计是数据库的安装问题
Rec2.CursorLocation = adUseClient
Rec2.Open SQL(2), DB, 1, 3
很奇怪啊
"Provider = SQLOLEDB.1;Persist Security Info = False;" & _
"User ID = adminID;Password = youPwd;Data Source = youSERVER;" & _
"Initial Catalog = youDB"
Public DB As New ADODB.Connectiondim rs(5) as new recordset
dim sql stringdb.open "Provider = SQLOLEDB.1;Persist Security Info = False;" & _
"User ID = adminID;Password = youPwd;Data Source = youSERVER;" & _
"Initial Catalog = youDB"sql="Select * From INVOICE" & vbcrlf & _
"Select * From BANK" & vbcrlf & _
"Select * From PORT" & vbcrlf & _
"Select * From VESSELS" & vbcrlf & _
"Select * From DETAIL"
Dim i As Integer
For i = 1 To 5
rs(i).CursorLocation = adUseClient
Nextrs(1).Open Sql, Conn, adOpenKeyset, adLockOptimistic
Set rs(2) = rs(1).NextRecordset
Set rs(3) = rs(2).NextRecordset
Set rs(4) = rs(3).NextRecordset
Set rs(5) = rs(4).NextRecordsetDebug.Print rs(1).RecordCount
Debug.Print rs(2).RecordCount
Debug.Print rs(3).RecordCount
Debug.Print rs(4).RecordCount
Debug.Print rs(5).RecordCount
db.CursorLocation = adUseClient
Dim Rs As New ADODB.Recordset
db.CursorLocation = adUseClient
试试看
最终代码入下:
Public DB As New ADODB.Connection
Public Rec(9) As New ADODB.Recordset
Public SQL(10) As String
...SQL(0) = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FP;Data Source=DHCP-SERVER"
SQL(1) = "Select * From FP_INVOICE"
...
SQL(9) = "Select * From FP_INV_Type"DB.CursorLocation = adUseClient
DB.ConnectionString = SQL(0)
DB.Open
For I = 1 To 9
With Rec(I)
.CursorLocation = adUseClient
.Open SQL(I), DB, adUseClient, 3
.MoveLast
Bar1.Value = I
End With
Next
...可以取到RECORDCOUNT,并且可以MOVELAST等游标操作了.并且发现了一个奇怪的地方:只要我在SQL数据库中使用nvarchar类型就会出错,改成varchar就又好了,我的SQL是英文版,不知道大家是否有类似现象发生过.结贴,散分了.