CreateTBProjectAndPosition="create table # ProjectAndPosition (wprojectid nvarchar (50), positionName nvarchar (50), num int)"
'populate the temporary table time department to query the current conditions of the current department weekly
Dim InsertTBa As String
InsertTBa="insert into # ProjectAndPosition select * from (select a. *, count (positionName) as num from (select isnull (Ftext, 'not in the project') as wprojectid, positionName from (select a. *, b. text from (select a. *, p. ositionName, p. ositionCode 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 aleem walji orkerID=biggest ode) a left join t_bos200000008 b on aleem walji projectid=b. billno) a) a group by aleem walji projectid, Amy polumbo ositionName) a "
'query
Dim selectListW As String
SelectListW="declare @ SQL varchar (8000) set @ SQL='select isnull (wprojectid,' total ') as a '+' project name 'select SQL + SQL=@ @', Max (case positionName when ' ' '+ positionName +' ' 'then num else 0 end) [' + positionName +'] 'the from (select distinct positionName from (select * from ProjectAndPosition) a) as a set @ SQL=@ + SQL', the sum (num) in the from (select * from ProjectAndPosition) a group by aleem walji projectid with rollup 'exec (SQL) @"
Set the rs=InsertSql_Tb (CreateTBProjectAndPosition InsertTBa, selectListW)
The Public Function InsertSql_Tb (sql1 As String, sql2 As String, sql3 As String) As the ADODB library you
Dim conn As ADODB. Connection
Dim the rs As the ADODB library you
Set the rs=New ADODB. You
Set the conn=New ADODB. Connection
'the query
Conn. The ConnectionString=connectString 'this is connected to the SQL database statement
Conn. Open
Conn. Execute sql1
Conn. Execute sql2
Rs. CursorLocation=adUseClient
Rs. The Open sql3, conn, adOpenDynamic adLockBatchOptimistic
The Set InsertSql_Tb=rs
End the Function
3 above is the following methods of parameter, using essentially to the execution of the SQL profiler can grab, but the judge said when you have shut down some reason,
Other for a long time didn't touch the VB beg god give you the detailed method of VB to connect to the database instance, with judge whether rs whether there is any empty conn close rs instance,
CodePudding user response:
SQL statement is no problem, I had to grab in the profilerCodePudding user response:
The third sqL is a dynamic sqL statement