在窗体中添加了类模块里面有函数
Public Function ExecuteSQL(ByVal sql As String, rst As ADODB.Recordset, Optional enablewrite As Boolean = True) As Boolean
Dim con As ADODB.Connection
On Error GoTo execute_Error
Set con = New ADODB.Connection
con.Open "filedsn=zhiwushezhi.dsn;uid=sa;pwd=sa"
Set rst = New ADODB.Recordset
If enablewrite Then
rst.Open Trim(sql), con, adOpenStatic, adLockOptimistic
Else
rst.Open Trim(sql), con, adOpenStatic, adLockReadOnly
End If
ExecuteSQL = True
con.Close
Exit Function
execute_Error:
ExecuteSQL = False
Exit Function
End FunctionPublic Function viewdata(ByVal txtsql As String, DataGrid1 As DataGrid) As Integer Dim rstdata As ADODB.Recordset
Set rstdata = New ADODB.Recordset
Dim result As String
result = ExecuteSQL(txtsql, rstdata, False)
If rstdata.RecordCount <> 0 Then
Set DataGrid1.DataSource = rstdata
viewdata = rstdata.RecordCount
Else
MsgBox "还没有数据!", vbOKOnly + vbExclamation, "警告"
viewdata = 0
End If
End Function
运行显示If rstdata.RecordCount <> 0 Then这行有错误“对象关闭时,不允许操作”
是因为连接数据库没有成功吗,怎么做啊?
请高手帮帮忙啊!非常感谢!!
Public Function ExecuteSQL(ByVal sql As String, rst As ADODB.Recordset, Optional enablewrite As Boolean = True) As Boolean
Dim con As ADODB.Connection
On Error GoTo execute_Error
Set con = New ADODB.Connection
con.Open "filedsn=zhiwushezhi.dsn;uid=sa;pwd=sa"
Set rst = New ADODB.Recordset
If enablewrite Then
rst.Open Trim(sql), con, adOpenStatic, adLockOptimistic
Else
rst.Open Trim(sql), con, adOpenStatic, adLockReadOnly
End If
ExecuteSQL = True
con.Close
Exit Function
execute_Error:
ExecuteSQL = False
Exit Function
End FunctionPublic Function viewdata(ByVal txtsql As String, DataGrid1 As DataGrid) As Integer Dim rstdata As ADODB.Recordset
Set rstdata = New ADODB.Recordset
Dim result As String
result = ExecuteSQL(txtsql, rstdata, False)
If rstdata.RecordCount <> 0 Then
Set DataGrid1.DataSource = rstdata
viewdata = rstdata.RecordCount
Else
MsgBox "还没有数据!", vbOKOnly + vbExclamation, "警告"
viewdata = 0
End If
End Function
运行显示If rstdata.RecordCount <> 0 Then这行有错误“对象关闭时,不允许操作”
是因为连接数据库没有成功吗,怎么做啊?
请高手帮帮忙啊!非常感谢!!
con.Open "filedsn=zhiwushezhi.dsn;uid=sa;pwd=sa"
msgbox con.state先看看有没有连接上数据库
这行是什么意思啊?
一条条运行了,就显示那行错误If rstdata.RecordCount <> 0 Then
是什么意思啊,不太明白啊
就是在你的代码:
con.Open "filedsn=zhiwushezhi.dsn;uid=sa;pwd=sa"
下面加一句:
msgbox con.state
如果正常连接上了数据库,con.state=1
如果把相关的recordset内容去掉,其他的功能还是可以实现。就是说datagrid控件中不能显示执行SQL语句得到的recordset的内容
是不是表示执行SQL语句失败?为什么会这样?
再最后 关掉它并释放链接对象
Set rstdata = New ADODB.Recordset
Dim result As String
result = ExecuteSQL(txtsql, rstdata, False)
If result <> 0 Then
Set DataGrid1.DataSource = rstdata
viewdata = rstdata.RecordCount
Else
MsgBox "还没有数据!", vbOKOnly + vbExclamation, "警告"
viewdata = 0
End If
End Function
可能sql有问题
If enablewrite Then '这里是false,当然RST就没打开
rst.Open Trim(sql), con, adOpenStatic, adLockOptimistic
Else
rst.Open Trim(sql), con, adOpenStatic, adLockReadOnly
End If
不是吧。result是boolean型。类型不匹配啊和0
'On Error GoTo execute_Error就这个意思,这样才能看出错在哪
我没看仔细,不过你的错误太多 Dim result As String
result = ExecuteSQL(txtsql, rstdata, False)
If rstdata.RecordCount <> 0 Then
改成 Dim result As boolean
result = ExecuteSQL(txtsql, rstdata, False)
If result Then
你说把那个'On Error GoTo execute_Error
那后面的判断读写方式的语句就都有错,就不需要写了。我试过
能够运行,但是结果不对。
我的表里面明明有数据,但它出现对话框,警告,还没有数据。就是说datagrid控件里还是不能显示执行SQL语句的记录集
Set DataGrid1.DataSource = rstdata
viewdata = rstdata.RecordCount
Else
MsgBox "还没有数据!", vbOKOnly + vbExclamation, "警告"
viewdata = 0
End If
rstdata.close
Public Function viewdata(ByVal txtsql As String, DataGrid1 As DataGrid) As Integer Dim rstdata As ADODB.Recordset
Set rstdata = New ADODB.Recordset
Dim result As String '字符型
result = ExecuteSQL(txtsql, rstdata, False) ’布尔型 If rstdata.RecordCount <> 0 Then ’rstdata 中没有任何数据集,而是个空集
Set DataGrid1.DataSource = rstdata
viewdata = rstdata.RecordCount
Else
MsgBox "还没有数据!", vbOKOnly + vbExclamation, "警告"
viewdata = 0
End If
End Function
只要是改成这样的rstdata.RecordCount <> 0就是涉及到rstdata.recordcount就会出现那个错误!不明白啊!
Set rstdata = New ADODB.Recordset
Dim result As String
result = ExecuteSQL(txtsql, rstdata, False)
If rstdata.RecordCount <> 0 Then '到这里rstdata根本就没打开,也没付对象值,有数据就见鬼了
Set DataGrid1.DataSource = rstdata
viewdata = rstdata.RecordCount
Else
MsgBox "还没有数据!", vbOKOnly + vbExclamation, "警告"
viewdata = 0
End If
End Function
是执行ExecuteSQL(txtsql, rstdata, False) 有错吗?
怎么改啊?
告诉你原因吧ExecuteSQL(txtsql, rstdata, False) 我猜测 你执行这个函数应该是返回一个 数据集
然后执行
rstdata= ExecuteSQL(txtsql, rstdata, False) 这个时候 你才能有数据集你还是应该好好想想你写的函数到底要用来做什么吧!!!
到底想怎么实现
就是这样啊,执行成功的话,结果存储在与形参数据集rst对应的实参rstdata数据集内啊。
有什么问题呢
虽然一样可以把ExecuteSQL函数写成是返回数据集就是rstdata= ExecuteSQL(txtsql, rstdata, False)
但是我那样写也没什么错吧。应该。
代码是书上的。
rst是全局变量???
那你就将
If rstdata.RecordCount <> 0 Then
更改为: If rst.RecordCount <> 0 Then
试了,改了也不对。
'Set rst = New ADODB.Recordset
'con.Close
'你如果不把上面两句注释掉,能得到数据才怪了
'还有你ExecuteSQL明明返回的是bol类型,接收的result却是string类型,而且result根本没用到
'参照你上面的代码写的,你对照着看吧
Option ExplicitPrivate Sub AA()
Dim rstTemp As New ADODB.Recordset
Dim bolResult As Boolean
bolResult = BB("select * from sum1", rstTemp, False)
If bolResult Then
Dim i As Integer
i = rstTemp.RecordCount
End If
End SubPrivate Function BB(ByVal sql As String, ByVal rst As ADODB.Recordset, ByVal bolTemp As Boolean)
Dim conn As ADODB.Connection
On Error GoTo execute_Error
Set conn = New ADODB.Connection
If conn.State = 1 Then conn.Close
conn.IsolationLevel = adXactCursorStability
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;" _
& "Data Source= E:\1.mdb "
conn.Open
'Set rst = New ADODB.Recordset 你在这里再一次实例化recordset,已经不是你原来的rstdata了
rst.Open Trim(sql), conn, adOpenStatic, adLockBatchOptimistic
BB = True
'conn.Close 关闭了链接,你的recordset也会关闭
Exit Function
execute_Error:
BB = False
End FunctionPrivate Sub Form_Load()
AA
End Sub
rst.Open Trim(sql), con, adOpenStatic, adLockOptimistic
Else
rst.Open Trim(sql), con, adOpenStatic, adLockReadOnly
End If
之后不运行ExecuteSQL = True
而是转向运行ExecuteSQL =false,也就是execute_Error:
出错处理。是否说明执行SQL语句没成功啊?
debug.print 你的SQL语句,然后放入查询分析器中去执行...
如果con打开了,'Set rst = New ADODB.Recordset 'con.Close
这两句也注释掉了,设断点单步走看错在哪里,错误类型
运行 rst.Open Trim(sql), con, adOpenStatic, adLockReadOnly 出错。错误是microsoft ODBC SQL Server Driver 【SQL Server】第一行:nationid附近有语法错误
我的SQL语句是: txtsql = "select nationid as 民族编号,dutyname as 民族名称 from tbnation orderby nationid"
recordcount的值,有两个对,一个错的。
我刚刚改了。可以recordcount值都是对的。但就是没有数据
你断点设在If rstdata.RecordCount <> 0 Then
看看recordcount是多少
程序运行到这里会停在这里,然后你再下面的“立即”对话框中输入?rstdata.RecordCount
DataGrid1.DataSource=nothing
但 他函数声明有问题
Private Function BB(ByVal sql As String, ByVal rst As ADODB.Recordset, ByVal bolTemp As Boolean)
改为
Private Function BB(ByVal sql As String, ByRef rst As ADODB.Recordset, ByVal bolTemp As Boolean)
datagrid比较娇贵