sql插入平均值的问题

Question

  Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = conn
    .Open "select avg(phy) as ach  from tableaa"
   
.Open "insert into tableaa(phy) values(''&ach&'')"

提示出错信息:
run-time error '3705'
operation is not allowed when the object is open

Answer 1

尝试把rst关闭以后（调用类似Close方法），然后重新打开执行insert语句（预先保留ach到某个变量中，然后在使用这个变量）。

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 2

声明两个不同变量名称的ADODB.Recordset

再各自Open试试看

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/

Answer 3

 Dim ach  As Integer

Set conn = New ADODB.Connection
    With conn
        ' Set the OleDB provider for the connection.
        .Provider = "Microsoft.JET.OLEDB.4.0"
        ' Open a connection to Northwind.mdb.
        .Open "c:\yyiblio.mdb"
    End With
    'select AVG(A) as Aavg,AVG(B) as Bavg from table
   
    Set rst = New ADODB.Recordset
    Set rst1 = New ADODB.Recordset
    With rst
        .ActiveConnection = conn
    .Open "select avg(phy) as ach  from tableaa"   '这里没有起到作用,  ach=0

 

  End With
   
    With rst1
     .ActiveConnection = conn

.Open "insert into tableaa(phy) values(' " & ach & " ')"
        End With

Answer 4

ach为0

Answer 5

Dim ach  As Integer
    Set conn = New ADODB.Connection
    With conn
            .Provider = "Microsoft.JET.OLEDB.4.0"
        .Open "c:\yyiblio.mdb"
    End With
   
    Set rst = New ADODB.Recordset
    Set rst1 = New ADODB.Recordset
    With rst
        .ActiveConnection = conn
      .Open "select avg(phy) as ach  from tableaa"
   
MsgBox ach    '这里提示ach为0

Answer 6

table:

 

ID	name	sex	match	chinese	english	phy	chem	total
CY001	cred	male	104	112	101	85	99	697
CY002	rose	female	87	105	98	119	101	693
CY003	helen	male	120	98	68	80	75	623
CY004	jack	female	98	68	115	91	85	642
CY005	tonny	male	96	88	111	87	68	624

Answer 7

你需要把数据读出来  http://msdn.microsoft.com/en-us/library/bb177501(v=office.12).aspx

另外，你可以用一条sql语句一起执行：

Insert into tableaa(phy) values select avg(phy) as ach  from tableaa 

另外VB6的问题请follow这个帖子：http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6a0719fe-14af-47f7-9f51-a8ea2b9c8d6b

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 8

ach1 = rst("ach")

MsgBox ach1      //here right

With rst1

.ActiveConnection = conn

.Open "Insert into tableaa values ('cy00bb','aavb','mmm', &ach1 ,'0','12','12','9','9')"                            //here mistake

Answer 9

请在&和ach1之间加一个空格看看。

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 10

为什么 你不合并成一行执行呢？ 

当你分两次执行的时候，第二次的recordset找不到它曾经用过的变量啊，要不然你就这样：

ach1 = rst("ach")

MsgBox ach1      //here right

With rst1

.ActiveConnection = conn

.Open "Insert into tableaa values ('cy00bb','aavb','mmm', “ & ach1 & ” ,'0','12','12','9','9')" 

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 11

你需要把数据读出来  http://msdn.microsoft.com/en-us/library/bb177501(v=office.12).aspx

另外，你可以用一条sql语句一起执行：

Insert into tableaa(phy) values select avg(phy) as ach  from tableaa 

另外VB6的问题请follow这个帖子：http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6a0719fe-14af-47f7-9f51-a8ea2b9c8d6b

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

.Open " Insert into tableaa (phy) values select avg(phy) as achhh  from tableaa"

提示 syntax error in INSERT INTO statement.  

可以这样吗:

.Open " Insert into tableaa (phy) values select avg(phy)   from tableaa"

Answer 12

不好意思 我写错了

 Insert into tableaa (phy)

select avg(phy) as achhh  from tableaa

没有Values

 

新年快乐！

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.