1180
社区成员
- 主页
...全文
请发表友善的回复…
发表回复
zhouqi66 2002-03-29
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 "打开数据库出错"
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 "打开数据库出错"
hooohooooooo 2002-03-29
用一个DBGrid和你的数据源连一下就可以了咯
cloudliu2000 2002-03-29
Dim rs As Recordset
Set rs = g_objDBC.Execute "select * from aaa"
出错
compile error
expected end of statement
Set rs = g_objDBC.Execute "select * from aaa"
出错
compile error
expected end of statement
inforum 2002-03-29
连接是否成功
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
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
守破之间 2002-03-29
上面的老哥也太夸张了！其实设一个number型变量，将rs.count赋给它！你就可以知道记录集中有多少条记录了。rs指的是记录集对象。
dim rs as new ADODB.Recordset即是。
dim rs as new ADODB.Recordset即是。
vivan19781111 2002-03-29
把结果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
判断是否成功可以用
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
TNT1900 2002-03-29
有个现成的例子:
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
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
blkant 2002-03-29
将结果集与显示控件（表格、文本框……都行）连接，然后显示就行了。
jett 2002-03-29
VB中连接数据库执行SQL语句后，怎么看结果集？
dim rs as recordset
set rs=cn.execute yoursql
dim rs as recordset
set rs=cn.execute yoursql
jett 2002-03-29
连接是否成功，怎么判断呢？
connection.state=adstateopen
connection.state=adstateopen