sql = "select name,phyname from sysdevices"
Set rs = conn.Execute(sql)
现在的问题是我想遍历rs的每条记录,找出name='master'的记录
遍历一条记录的各个字段倒会,遍历每条记录怎么搞啊?
本人正step by step学习SQL中,麻烦高手来指点下了!!!
3Q
Option Explicit
'先添加两个ListBox控件..分别用来存放遍历查询和Select查询返回的结果
'再添加两个CommandButton控件.Caption分别为遍历和Select查询
'接着在工程->引用->Microsoft ActiveX Data Objects 2.7 Library
Private dbConn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
Call whileEndQuery
End Sub
Private Sub Command2_Click()
Call selectQuery
End Sub
Private Sub Form_Load()
Dim conString As String
conString = "Provider=SQLOLEDB;Data Source=127.0.0.1;Initial Catalog=Northwind;" _
& "User ID=sa;Password=admin"
'这里我连接的是Sql Server数据库.你也可以连接Access数据库,相应的连接字符串是:
'Provider=Microsoft.Jet.OleDb.4.0;Data Source=d:\mdb\Northwind.mdb
Set dbConn = CreateObject("ADODB.Connection") '创建Connection实例
dbConn.ConnectionString = conString
dbConn.Open
End Sub
Private Sub selectQuery()
Dim sql As String
Dim icurrRecord As Long
sql = "select [OrderID] from [orders] where [ShipCountry]='USA'"
'这里我要查找的是ShipCountry字段为USA的OrderID号是多少.
Set rst = CreateObject("ADODB.Recordset")
With rst
.CursorLocation = adUseClient
.Open sql, dbConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If .RecordCount > 0 Then '如果查询之后有记录的话
.MoveFirst
For icurrRecord = 0 To .RecordCount - 1 '循环得到查找出来的值
List2.AddItem .Fields("OrderID").Value
.MoveNext '移到下一条
Next
End If
.Close
End With
End Sub
Private Sub whileEndQuery() '遍历查找
Dim sql As String
Dim icurrRecord As Long
sql = "select * from [orders]"
Set rst = CreateObject("ADODB.Recordset")
With rst
.CursorLocation = adUseClient
.Open sql, dbConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If .RecordCount > 0 Then
.MoveFirst
For icurrRecord = 0 To .RecordCount - 1
If .Fields("ShipCountry").Value = "USA" Then '如果它的值是USA的话
List1.AddItem .Fields("OrderID").Value '添加到list1中
End If
.MoveNext '移到下一条
Next
End If
.Close
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set rst = Nothing
Set dbConn = Nothing
End Sub