VB中连接数据库执行SQL语句后，看结果集怎么办

[cloudliu2000]

VB中连接数据库执行SQL语句后，怎么看结果集？
而且连接是否成功，怎么判断呢？

[jett]

连接是否成功，怎么判断呢？
connection.state=adstateopen

[jett]

VB中连接数据库执行SQL语句后，怎么看结果集？
dim rs as recordset
set rs=cn.execute yoursql

[blkant]

将结果集与显示控件（表格、文本框……都行）连接，然后显示就行了。

[TNT1900]

有个现成的例子:
Public Function GetAbrByOptn( _
Optional iOrder As Integer = 1, _
Optional EmpID As String = "", _
Optional ID As Integer = 0, _
Optional TargetCountryID As Integer = 0, _
Optional PassportID As String = "", _
Optional PassportOptn As Integer = 0, _
Optional StartTime As Date = "1899/01/01", _
Optional StartTimeOptn As String _
) As ADODB.Recordset

On Error GoTo ErrorProcess

Dim strSql As String
Dim strWhereOrAnd As String

Dim Conn As ADODB.Connection
Dim p_RS As ADODB.Recordset

m_SuccessOrNot = False

'' Open ADO connection
Set Conn = New ADODB.Connection
Set p_RS = New ADODB.Recordset

'' Build SQL statement
strSql = "SELECT * FROM T_AbroadList "

'' 员工编码的组合
If EmpID <> "" Then
strSql = strSql & "WHERE EmpID = '" & EmpID & "' "
End If
'' 内部编号组合
If ID <> 0 Then
strSql = strSql & cStrWhereOrAnd(strSql) & " ID = " & ID & " "
End If

'' 国家条件组合
If TargetCountryID <> 0 Then
strSql = strSql & cStrWhereOrAnd(strSql) & " TargetCountryID = " & TargetCountryID & " "
End If

'' 护照及其条件的组合
If PassportID <> "" Then
Select Case PassportOptn
Case 0
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID = '" & PassportID & "' "

Case 1
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '%" & PassportID & "%' "

Case 2
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '%" & PassportID & "' "

Case 3
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '" & PassportID & "%' "

Case Else
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '" & PassportID & "' "
End Select
End If

'' 开始时间及其条件的组合
If StartTime <> "1899/01/01" Then
strSql = strSql & cStrWhereOrAnd(strSql) & " StartTime " & StartTimeOptn & "#" & StartTime & "# "
End If

'' 结果集排列顺序
Select Case iOrder
'' Order By EmpID, ID
Case 1
strSql = strSql & "Order by EmpID,ID"

Case Else
strSql = strSql & "Order by EmpID,ID"
End Select

'' Open the Connection
Conn.Open G_ADO_ConStr

'' Open the recordset
p_RS.Open strSql, Conn, adOpenStatic

Set GetAbrByOptn = p_RS
m_SuccessOrNot = True '' Success Flag

Exit Function

ErrorProcess:
Err.Raise Err.Number & Err.Source
Set GetAbrByOptn = Nothing
m_SuccessOrNot = False '' False flag
End Function


使用结果集合:
Private Sub cmdGetAbrByOptn_Click()

On Error GoTo ErrorProcess

Dim Obj1 As Object
Dim sTmp As String
Dim sTmp2 As String
Dim iTmp As Integer

Dim i As Integer
Dim iMax As Integer

Dim p_Rs As ADODB.Recordset

Set p_Rs = New ADODB.Recordset

Set Obj1 = CreateObject("BCLHR.clsOthers")

'Set p_Rs = Obj1.GetAbrByOptn
'Set p_Rs = Obj1.GetAbrByOptn(1, "emp001")
'Set p_Rs = Obj1.GetAbrByOptn(, "emp001")
'Set p_Rs = Obj1.GetAbrByOptn(, "emp001", 1)
Set p_Rs = Obj1.GetAbrByOptn(, , , , "China", 1)
' MsgBox p_Rs.RecordCount

' Set p_Rs = Obj1.GetAbrByOptn(, "emp001", , , "China", 1)
' MsgBox p_Rs.RecordCount
'
' Set p_Rs = Obj1.GetAbrByOptn(, "emp001", , , "China", 2)
' MsgBox p_Rs.RecordCount
'
' Set p_Rs = Obj1.GetAbrByOptn(, "emp001", , , "China", 3)
' MsgBox p_Rs.RecordCount
'
' Set p_Rs = Obj1.GetAbrByOptn(, "emp001", , , "China", 4)
'
iMax = p_Rs.Fields.count

MsgBox p_Rs.RecordCount

i = 0
cmbList.Clear
While Not (p_Rs.EOF)
cmbList.AddItem Trim(p_Rs(0)) & " " & Trim(p_Rs(1)) & " " & Trim(p_Rs(2)) & " " & Trim(p_Rs(3)) & " " & Trim(p_Rs(4)) & " " & Trim(p_Rs(5)) & " " & Trim(p_Rs(6)) & " " & Trim(p_Rs(7)) & " " & Trim(p_Rs(8))
i = i + 1
p_Rs.MoveNext
Wend

Exit Sub

ErrorProcess:
Err.Raise Err.Number, Err.Source, Err.Description
End Sub

[vivan19781111]

把结果Debug.Print出来,不就行了吗
判断是否成功可以用
On Error goto error1
连接数据库语句
If Err.Number = 0 Then
MsgBox "连接数据库成功！"
End If

error1:
If Err.Number <> 0 Then
MsgBox Err.Number & vbCr & Err.Description
End If

[守破之间]

上面的老哥也太夸张了！其实设一个number型变量，将rs.count赋给它！你就可以知道记录集中有多少条记录了。rs指的是记录集对象。
dim rs as new ADODB.Recordset即是。

[inforum]

连接是否成功
connection.state=adstateopen
看结果集
dim db as connection
dim rs as recordset
set db=New connection
db.open "..."
set rs=db.execute "SELECT * FROM ..."
debug.print rs!Field1 & vbTab & rs!Field2

[cloudliu2000]

Dim rs As Recordset
Set rs = g_objDBC.Execute "select * from aaa"


出错
compile error
expected end of statement

[hooohooooooo]

用一个DBGrid和你的数据源连一下就可以了咯

[zhouqi66]

dim cn as new adodb.connection
dim rs as new adodb.recordset
dim databasename,servername,username as string
dim i as long
servername="aaa"
databasename="aaa"
username="aaa"
On Error GoTo error1
cn.open "Provider=SQLOLEDB.1;Persist Securityi nfo=False;User ID=" & UserName & ";Initial Catalog=" & DatabaseName & ";Data Source=" & ServerName & " ;Connect Timeout=30"
rs.open "select * from aaa",cn
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
End If
i = .RecordCount
exit sub
error1:
msgbox "打开数据库出错"