Dim CreateTBProjectAndPosition As String
CreateTBProjectAndPosition = "create table #ProjectAndPosition(wprojectid nvarchar(50),positionName nvarchar(50),num int)"
'填充臨時表 時間部門 為查詢條件 當前部門 當前周次
Dim InsertTBa As String
InsertTBa = "insert into #ProjectAndPosition select * from (select a.*,count(positionName) as num from(select isnull(Ftext,'未在專案') as wprojectid,positionName from (select a.*,b.Ftext from (select a.*,b.positionName,b.positionCode from(select * from wproject where departmentNamelike like '%" + DepFlg + "%' and( (InprojectDate<'" + Week + "' and EndprojectDate>'" + Week + "') or (BeInprojectDate<'" + Week + "' and EndprojectDate>'" + Week + "')) )a inner join v_Report_EmployeeDetailInfo b on a.workerID=b.code) a left join t_bos200000008 b on a.wprojectid=b.fbillno ) a ) a group by a.wprojectid,a.positionName) a"
'查詢
Dim selectListW As String
selectListW = "declare @sql varchar(8000) set @sql='select isnull(wprojectid,''總計'') as '+'專案名' select @sql=@sql+',max(case positionName when '''+ positionName+''' then num else 0 end)[' + positionname + ']' from (select distinct positionName from (select * from ProjectAndPosition) a ) as a set @sql=@sql+' ,sum(num) 總計 from (select * from ProjectAndPosition) a group by a.wprojectid with rollup' exec(@sql)"
Set rs = InsertSql_Tb(CreateTBProjectAndPosition, InsertTBa, selectListW)
Public Function InsertSql_Tb(sql1 As String, sql2 As String, sql3 As String) As ADODB.Recordset
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection
'查詢陳述句
conn.ConnectionString = connectString '這是連接SQL資料庫的陳述句
conn.Open
conn.Execute sql1
conn.Execute sql2
rs.CursorLocation = adUseClient
rs.Open sql3, conn, adOpenDynamic, adLockBatchOptimistic
Set InsertSql_Tb = rs
End Function
上面3個是下面方法的引數,用SqlServer profiler能抓取到這些sql的執行,但是在判斷recordset的時候 說已經關閉了 什么原因 。
另外很久沒碰VB了 求大神給你詳細的VB連接資料庫方法的實體,帶判斷rs是否存在 是否為空 conn 關閉 rs關閉的實體。
uj5u.com熱心網友回復：
sql陳述句是沒問題的,我在profiler中都抓取到了uj5u.com熱心網友回復：
第三個sqL是個動態sql陳述句轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/126316.html
下一篇：我想求一個VB科學計算器代碼，只要實作數字0-9 四則運算 平方以及平方根 sin cos 退格 復制 粘貼 清除 倒數 求和 平均值的就行，可以幫我么