程序中先执行了这条查询,没有有任何问题:
hz.Open "SELECT DATEPART(month, bkrq) AS bkyf, SUM(bkje) AS zjbk " _
& " FROM (SELECT bkrq, bkdh, bkdw, bkje From bkjl WHERE (zcdw = '' OR zcdw IS NULL) AND datePart(month, bkrq) BETWEEN " & rq1 & " and " & rq2 & " AND bkdw LIKE '" & X & "%'" _
& "Union SELECT bkrq, bkdh,zcdw, bkje From bkjl WHERE (zcdw <> '' AND zcdw IS NOT NULL) AND datePart(month, bkrq) BETWEEN " & rq1 & " and " & rq2 & " AND zcdw LIKE '" & X & "%') DERIVEDTBL" _
& " GROUP BY DATEPART(month, bkrq) ORDER BY bkyf", db, 1, 3 '***********本月合计接下来执行这一条: hz2.Open "SELECT SUM(bkje) AS zjbk " _
& " FROM (SELECT bkrq, bkdh, bkdw, bkje From bkjl WHERE (zcdw = '' OR zcdw IS NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND bkdw LIKE '" & X & "%'" _
& "Union SELECT bkrq,bkdh, zcdw, bkje From bkjl WHERE (zcdw <> '' AND zcdw IS NOT NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND zcdw LIKE '" & X & "%') DERIVEDTBL, db, adOpenStatic '***********本月累计"问题在于,执行到第二条的时候,出现错误“实时错误3709,连接无法用于此操作。在此上下文中它可能已被关闭或无效”。
在第一条查询与第二条查询之间确实有一段代码,但都与连接“db”无关,怀疑是其它问题,因此删去第二条查询,并在相同的位置放此一条查询:
hz2.Open "select Sum (bkje) as zjbk from bkjl where month(bkrq)<=" & hz!bkyf, db, adOpenStatic
运行结果是此查询被执行,没有任何问题。纳闷的是,系统明明提示是“连接”问题,为什么却与“UNION”有关?程序被卡在这几天了,盼高手解答!(运行环是:SQLSERVER2000个人版,XPSP2,连接:cnstr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=zb;Data Source=192.168.0.1"
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open cnstr)
hz.Open "SELECT DATEPART(month, bkrq) AS bkyf, SUM(bkje) AS zjbk " _
& " FROM (SELECT bkrq, bkdh, bkdw, bkje From bkjl WHERE (zcdw = '' OR zcdw IS NULL) AND datePart(month, bkrq) BETWEEN " & rq1 & " and " & rq2 & " AND bkdw LIKE '" & X & "%'" _
& "Union SELECT bkrq, bkdh,zcdw, bkje From bkjl WHERE (zcdw <> '' AND zcdw IS NOT NULL) AND datePart(month, bkrq) BETWEEN " & rq1 & " and " & rq2 & " AND zcdw LIKE '" & X & "%') DERIVEDTBL" _
& " GROUP BY DATEPART(month, bkrq) ORDER BY bkyf", db, 1, 3 '***********本月合计接下来执行这一条: hz2.Open "SELECT SUM(bkje) AS zjbk " _
& " FROM (SELECT bkrq, bkdh, bkdw, bkje From bkjl WHERE (zcdw = '' OR zcdw IS NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND bkdw LIKE '" & X & "%'" _
& "Union SELECT bkrq,bkdh, zcdw, bkje From bkjl WHERE (zcdw <> '' AND zcdw IS NOT NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND zcdw LIKE '" & X & "%') DERIVEDTBL, db, adOpenStatic '***********本月累计"问题在于,执行到第二条的时候,出现错误“实时错误3709,连接无法用于此操作。在此上下文中它可能已被关闭或无效”。
在第一条查询与第二条查询之间确实有一段代码,但都与连接“db”无关,怀疑是其它问题,因此删去第二条查询,并在相同的位置放此一条查询:
hz2.Open "select Sum (bkje) as zjbk from bkjl where month(bkrq)<=" & hz!bkyf, db, adOpenStatic
运行结果是此查询被执行,没有任何问题。纳闷的是,系统明明提示是“连接”问题,为什么却与“UNION”有关?程序被卡在这几天了,盼高手解答!(运行环是:SQLSERVER2000个人版,XPSP2,连接:cnstr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=zb;Data Source=192.168.0.1"
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open cnstr)
这个参数是必须的。另外,调试中我还把两条查询中的条件几乎全删掉,只留“union”,问题还是一样。
& " FROM (SELECT bkrq, bkdh, bkdw, bkje From bkjl WHERE (zcdw = '' OR zcdw IS NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND bkdw LIKE '" & X & "%'" _
& "Union SELECT bkrq,bkdh, zcdw, bkje From bkjl WHERE (zcdw <> '' AND zcdw IS NOT NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND zcdw LIKE '" & X & "%') DERIVEDTBL", db, adOpenStatic '***********本月累计
这里面的最后一个双引号位置有问题,改成这样:hz2.Open "SELECT SUM(bkje) AS zjbk " _
& " FROM (SELECT bkrq, bkdh, bkdw, bkje From bkjl WHERE (zcdw = '' OR zcdw IS NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND bkdw LIKE '" & X & "%'" _
& "Union SELECT bkrq,bkdh, zcdw, bkje From bkjl WHERE (zcdw <> '' AND zcdw IS NOT NULL) AND datePart(month, bkrq) <=" & hz!bkyf & " AND zcdw LIKE '" & X & "%') DERIVEDTBL", db, adOpenStatic '***********本月累计
原来是自己不仔细,还被系统提示忽悠了!
这里很奇怪
SQL中没见过DERIVEDTBL这个参数,而如果是你的hz的参数 语句则修改如下X & "%')", DERIVEDTBL