有如下一段程序想将SQL的查询结果显示在MSHflexgrid中,但执行到 Me.MSHFlexGrid1.DataSource = rs 语句时,提示“未找到方法或成员”,有时又提示“类型不对”。本人相将SQL Server中的查询结果显示在MSHflexgrid中显示,程序如下:(注连接SQL服务器是没有问题的,selectSQL函数在datagrid中使用无任何问题。
请教各位高手Option Explicit
Dim rs As ADODB.Recordset
Dim sql As String
Dim msg As String
Dim Index As Integer
Dim Flag As String
Dim ConnectionString As StringPublic Function GetConnStr() As String
GetConnStr = ConnectionString
End Function
Public Function OpenConn(ByRef Conn As ADODB.Connection) As Boolean
'打开数据库连接,连接成功返回true,出错时返回false
Set Conn = New ADODB.Connection
'出错处理
On Error GoTo ErrorHandle
Conn.Open GetConnStr
OpenConn = True
Exit Function
ErrorHandle:
MsgBox "连接数据库失败!请重新连接!"
OpenConn = False
Exit Function
End Function
Public Function SelectSQL(ByVal sql As String, ByRef msg As String) As ADODB.Recordset
'执行SQL语句,返回ADODB.Recordset
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'出错处理
On Error GoTo ErrorHandle
'判断SQL语句
sTokens = Split(sql)
If InStr("SELECT", UCase((sTokens(0)))) Then
'打开数据库连接
If OpenConn(Conn) Then
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open Trim$(sql), Conn, adOpenDynamic, adLockOptimistic
Set SelectSQL = rst
msg = "查询到" & rst.RecordCount & " 条记录! "
End If
Else
msg = "SQL语句有误:" & sql
End If
Finally_Exit:
Set rst = Nothing
Set Conn = Nothing
Exit Function
ErrorHandle:
msg = "查询错误: " & Err.Description
'MsgString = "查询错误: " & Err.Description
Resume Finally_Exit
End FunctionPrivate Sub Command1_Click()
' Dim Conn As ADODB.Connection
' ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;"
' ConnectionString = ConnectionString & "User ID=" & Trim(txtUserId.Text) & ";Password=" & Trim(txtPwd.Text) & ";"
' ConnectionString = ConnectionString & "Initial Catalog=" & Trim(txtDatabase.Text) & ";Data Source=" & Trim(txtServer.Text) Set rs = Nothing
sql = " select * from 职工信息表 order by 职工ID"
Set rs = SelectSQL(sql, msg)
Me.MSHFlexGrid1.DataSource = rs
MSHFlexgrid1.Refresh
请教各位高手Option Explicit
Dim rs As ADODB.Recordset
Dim sql As String
Dim msg As String
Dim Index As Integer
Dim Flag As String
Dim ConnectionString As StringPublic Function GetConnStr() As String
GetConnStr = ConnectionString
End Function
Public Function OpenConn(ByRef Conn As ADODB.Connection) As Boolean
'打开数据库连接,连接成功返回true,出错时返回false
Set Conn = New ADODB.Connection
'出错处理
On Error GoTo ErrorHandle
Conn.Open GetConnStr
OpenConn = True
Exit Function
ErrorHandle:
MsgBox "连接数据库失败!请重新连接!"
OpenConn = False
Exit Function
End Function
Public Function SelectSQL(ByVal sql As String, ByRef msg As String) As ADODB.Recordset
'执行SQL语句,返回ADODB.Recordset
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'出错处理
On Error GoTo ErrorHandle
'判断SQL语句
sTokens = Split(sql)
If InStr("SELECT", UCase((sTokens(0)))) Then
'打开数据库连接
If OpenConn(Conn) Then
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open Trim$(sql), Conn, adOpenDynamic, adLockOptimistic
Set SelectSQL = rst
msg = "查询到" & rst.RecordCount & " 条记录! "
End If
Else
msg = "SQL语句有误:" & sql
End If
Finally_Exit:
Set rst = Nothing
Set Conn = Nothing
Exit Function
ErrorHandle:
msg = "查询错误: " & Err.Description
'MsgString = "查询错误: " & Err.Description
Resume Finally_Exit
End FunctionPrivate Sub Command1_Click()
' Dim Conn As ADODB.Connection
' ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;"
' ConnectionString = ConnectionString & "User ID=" & Trim(txtUserId.Text) & ";Password=" & Trim(txtPwd.Text) & ";"
' ConnectionString = ConnectionString & "Initial Catalog=" & Trim(txtDatabase.Text) & ";Data Source=" & Trim(txtServer.Text) Set rs = Nothing
sql = " select * from 职工信息表 order by 职工ID"
Set rs = SelectSQL(sql, msg)
Me.MSHFlexGrid1.DataSource = rs
MSHFlexgrid1.Refresh
if rs.state<>adstateclosed then rs.close
rs.open sql,conn,adopenkeyset,adlockreadonly
set Me.MSHFlexGrid1.DataSource = rs
rs.close
set rs=nothing