可以在存储过程中建临时表,然后在ADO中调用呀。当然在ADO中也是可以建的。
解决方案 »
- 大家好,我想请教一下,我用VB6.0连接SQL2000数据库,如何实现数据库中的数据在每天的23:59:59秒时就被从24点开始的数据覆盖?先谢谢大家了
- 关于Byte数组的问题!
- SCRIPTCONTROL如何对程序中对象进行操作
- 用VB拦截某窗口的消息?
- mciSendString 函式来播放一个avi 档案
- 有关软件界面设计的问题
- 请问:如何用VB做AUTOCAD二次开发
- 困惑已久的问题:VB中的文本框和数据绑定以后,只可以新添加记录,或者修改记录,但是我想把已经赋值的记录置空就不行,各位有经验,帮我
- 关于IE的下载接口,哪位有VB例程
- 请教一个数据处理的问题,高手们帮帮忙
- 用VB如何实现定时提醒关机?
- 有谁知道怎么样把Activex文档发布到服务器呀?
create pro...
....
create #temptable
--use temptable
drop #temptable
...
Set cn = New ADODB.Connection
cn.Open strcnn
strSQL = "select stshop_shopid as shopid from site_shop where stshop_shopid like " & _
"'" & ShopID & "%'"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly, adCmdText
n = rs.RecordCount
ReDim Shop(1 To n)
For i = 1 To n
Shop(i) = rs!ShopID
rs.MoveNext
Next i
rs.Close
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = cn
strSQL = "create table [#Temp]([style] char(10) "
For i = 1 To (n + 1) * 2
strSQL = strSQL & "," & "[" & i & "] int default 0"
Next i
strSQL = strSQL & ")"
Cmd.CommandText = strSQL
Cmd.CommandType = adCmdText
Cmd.Execute
For i = 1 To n
n_ShopID = Shop(i)
strSQL = "insert into #temp(style,[" & 2 * i - 1 & "],[" & 2 * i & "])" & _
" select invstock_style,invstock_PreviousQuantity,invstock_Quantity" & _
" from inv_stock where invstock_shopid=" & "'" & n_ShopID & "'" & _
" and invstock_year=" & "'" & strYear & "'" & _
" and invstock_month=" & "'" & strMonth & "'" & _
" and (invstock_PreviousQuantity<>0 or invstock_Quantity<>0)"
Cmd.CommandText = strSQL
Cmd.Execute
Next i
sumCol1_qty = "[1]"
sumcol2_amount = "[2]"
For i = 2 To n
sumCol1_qty = sumCol1_qty & "+" & "[" & 2 * i - 1 & "]"
sumcol2_amount = sumcol2_amount & "+" & "[" & 2 * i & "]"
Next i
strSQL = "update #temp set " & "[" & (n + 1) * 2 - 1 & "]=" & sumCol1_qty & ",[" & _
(n + 1) * 2 & "]=" & sumcol2_amount & ""
Cmd.CommandText = strSQL
Cmd.Execute
你写的程序错误太多了点吧!
Dim strSql As String
Con.CursorLocation = adUseClient
Con.Open "Provider=SQLOLEDB;UID=sa;PWD=;Initial Catalog=Var2000;Data Source=Nipsan"strSql = " Create Table #tmpTest(PartCode varchar(30),PartRule varchar(300))"
Con.Execute strSql
strSql = " INSERT INTO #tmpTest Select PartCode ,PartRule From tblstock"
Con.Execute strSql
只要Con不斷開,在這條連線上,#tmpTest都是有效的。
Rs.Open " Select * From #tmpTest ", Con, adOpenStatic, adLockBatchOptimistic
SQLServer可以接收一组命令,其中就可以使用临时表(就像QueryAnalyzer),还可以使用事务(Begin Tran),不过这种方法效率没有存储过程高。
DIM RS AS ADO.RECORDSET
SET RS=NEW ADO.RECORDSET
RS.FIELDS.APPEND "ID",ADINTERGER,,ADFLDKEYCOLUMN
RS.FIELDS.APPEND "DESCRIPTION",ADVARCHAR,40
RS.OPEN LOCKTYPE:=ADLOCKBATCHOPTIMISTIC
RS.ADDNEW
RS.FIELDS(ID).VALUE=1
RS.FIELD(DESCRIPTION).VALUE="FIRST RECORD"
RS.UPDATE
创建临时表就是这么简单.