对象变量或With块变量未设置问题！！

[ln0722]

我要模拟抽奖系统，两个按钮一个Label或TextBox，点击开始按钮，就是让数据库中的号码不停地在窗体上多行滚动，点击停止按钮，就停下来。但不能重复抽！！！
在下是个菜鸟只能想到如下：
Dim CN As ADODB.Connection
Dim StartLoop As Boolean
Dim SqlStr As String

Private Sub Form_load()
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\...\db1.mdb;Persist Security Info=False"
CN.Open
End Sub

Private Sub CommandButton1_Click()
Dim starTime As Single
Dim rs As ADODB.Recordset
StartLoop = True
SqlStr = "select call_no from pd_users where 1=1"
Set rs = CN.Execute(SqlStr)
rs.MoveFirst
Do While StartLoop
starTime = Timer
Do While Timer - starTime < 0.1
DoEvents
If StartLoop = False Then Exit Do
Loop
TextBox1 = rs("call_no")
rs.MoveNext
On Error Resume Next
If rs.EOF Then
rs.MoveFirst
Else
rs.MoveNext
End If
Loop
rs.Close
End Sub

Private Sub CommandButton2_Click()
StartLoop = False
End Sub
系统提示说：对象变量或With块变量未设置，并指向这句“Set rs = CN.Execute(SqlStr)”请大家帮忙看看；
还有，我在SQL语句查询后在多一步操作，将查询处的call_no的state字段的值改为1
应该怎么改！！谢谢大家！！

[ln0722]

我已将程序改为如下：
Dim CN As ADODB.Connection
Dim StartLoop As Boolean
Dim SqlStr As String

Private Sub Form_load()
Set CN = New ADODB.Connection
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\...\db1.mdb;Persist Security Info=False"
CN.Open
End Sub

Private Sub CommandButton1_Click()
Dim starTime As Single
Dim rs As ADODB.Recordset
StartLoop = True
SqlStr = "select call_no from pd_users where 1=1"
Set rs = CN.Execute(SqlStr)
rs.MoveFirst
Do While StartLoop
starTime = Timer
Do While Timer - starTime < 0.1
DoEvents
If StartLoop = False Then Exit Do
Loop
TextBox1 = rs("call_no")
rs.MoveNext
On Error Resume Next
If rs.EOF Then
rs.MoveFirst
Else
rs.MoveNext
End If
Loop
rs.Close
End Sub

Private Sub CommandButton2_Click()
StartLoop = False
End Sub
系统仍提示说：对象变量或With块变量未设置，并指向这句“Set rs = CN.Execute(SqlStr)”
还有，由于小弟是个菜鸟，可能理解的不对，你的意思是说将SqlStr = "select call_no from pd_users where 1=1"替换为SqlStr = "update pd_users set call_no=call_no+1 where 1=1"么？？

[daisy8675]

Dim CN As ADODB.Connection

改成 Dim CN As new ADODB.Connection

[pcm112]

在Dim rs As ADODB.Recordset后面加一句：
set rs=new adodb.recordset

[wxrwan]

set CN=new adodb.connection

[落伍者]

Dim CN As new ADODB.Connection

[lifat]

你未将ADODB.Connection与ADODB.recordset实例化！需要加上这两句：
Dim CN As New ADODB.Connection
Din rs As New ADODB.Recordset

[ln0722]

我已将程序改为如下：
Dim CN As New ADODB.Connection
Dim StartLoop As Boolean
Dim SqlStr As String


Private Sub Form_load()
Set CN = New ADODB.Connection
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\...\db1.mdb;Persist Security Info=False"
CN.Open
End Sub



Private Sub CommandButton1_Click()
Dim starTime As Single
Dim rs As New ADODB.Recordset
StartLoop = True
SqlStr = "select call_no from pd_users where 1=1"
Set rs = CN.Execute(SqlStr)
rs.MoveFirst
Do While StartLoop
starTime = Timer
Do While Timer - starTime < 0.1
DoEvents
If StartLoop = False Then Exit Do
Loop
TextBox1 = rs("call_no")
rs.MoveNext
On Error Resume Next
If rs.EOF Then
rs.MoveFirst
Else
rs.MoveNext
End If
Loop
rs.Close
End Sub

Private Sub CommandButton2_Click()
StartLoop = False
End Sub
系统提示说：对象关闭时，操作不被允许。并指向这句“Set rs = CN.Execute(SqlStr)”

[clark_rain]

查询后，令sqlstr="update pd_users set state=1 where call_no=" & textbox1.text

[clark_rain]

忘了,然后cn.execute sqlstr

[clark_rain]

TextBox1 = rs("call_no")??没见过这样的写法?不出错吗?

[lhzongji]

声明ADO的CONN最好加NEW关键字.

另,如果你的SQL语句有错误,你的RS也会出这样的错误

[lifat]

试一下：

Set rs = CN.Execute(SqlStr)
改为：
rs.Open SqlStr, CN, adOpenStatic, adLockOptimistic

还有：
TextBox1 = rs("call_no")应该改为TextBox1 = rs！[call_no]

[ln0722]

我已将程序改为如下：
Dim CN As New ADODB.Connection
Dim StartLoop As Boolean
Dim SqlStr As String


Private Sub Form_load()
Set CN = New ADODB.Connection
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\jbproject\ÐÂÆÀ·Öϵͳ\db1.mdb;Persist Security Info=False"
CN.Open
End Sub



Private Sub CommandButton1_Click()
Dim starTime As Single
Dim rs As New ADODB.Recordset
StartLoop = True
SqlStr = "select id from test where state=0"
SqlStr = "update test set state=1 where id=" & TextBox1.Text
Set rs = CN.Execute(SqlStr)
rs.MoveFirst
Do While StartLoop
starTime = Timer
Do While Timer - starTime < 0.1
DoEvents
If StartLoop = False Then Exit Do
Loop
TextBox1 = rs![call_no]
rs.MoveNext
On Error Resume Next
If rs.EOF Then
rs.MoveFirst
Else
rs.MoveNext
End If
Loop
rs.Close
End Sub

Private Sub CommandButton2_Click()
StartLoop = False
End Sub

系统提示说：对象关闭时，操作不被允许。并指向这句“Set rs = CN.Execute(SqlStr)”
数据库是access，只有一个表test，两个字段id、state，id中就是数字，state初始都是0
SQL因该不会有错吧

[jordi2014]

你在cn.open 处设个断点，执行执行到改句，单步执行，执行后，你查看一下cn对象的状态是否为打开。如果没有打开，那么执行其方法就会出错。

另外，如果你在dim 时用了new 那么就不需要 set cn=new adodb.connection了。如果dim时没有new,那么就需要后边这一句。

[libiyang]

把Dim rs As ADODB.Recordset改为
Dim rs As New ADODB.Recordset

[csdnchenxu]

应该是你没有设置一个数据库 .connection
set rs.ActiveConnection = 某个数据库名

[羽毛之家]

这个我刚遇到过,就是你要再定一个
set cn=new adodb.connection
set rs=new adodb.Recordset

[舍南舍北]

Dim CN As ADODB.Connection
Dim rs As ADODB.Recordset
改為

Dim CN As new ADODB.Connection
Dim rs As new ADODB.Recordset

[bluesky23]

刷新一下，
rs.refresh

[kxyzjm]

是否缺少*.dll和*.lib文件的引用，如果不缺少引用和set语句也没有问题的话，应该不会出现这样的问题。