I'm trying to set the results of a query to a dataset and it's telling me "Invalid Column Name"
The code is crazy:
Does anyone know why it's throwing this error?
- Matt
"If I must boast, I will boast of the things that show my weakness"
- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
The code is crazy:
Code:
Dim da As SqlDataAdapter
Dim conn As SqlConnection = New SqlConnection("Data Source=nhqsqlqa3102\sql2005;Initial Catalog=default_bsd_qa_db;Integrated Security=True")
Dim ds As New DataSet
Dim sSQL As String
ds.Clear()
sSQL = "WITH tmp_Table AS " & _
"(SELECT " & _
"REL_ID AS 'Release ID', " & _
"REL_NAME as 'Release Name', " & _
"REL_USER_08 as 'PM / PDM', " & _
"CONVERT (varchar(10), RCYC_START_DATE ,110) as 'Test Start Date', " & _
"CONVERT (varchar(10), RCYC_END_DATE + .99,110) as 'Test End Date', " & _
"CONVERT (varchar(10), CAST(REL_USER_02 AS DATETIME),110)AS 'Production Start Date', " & _
"ISNULL(round(CAST(cast(CYCLE_PASSED_COUNT.CNT as FLOAT) " & _
"/ " & _
"CYCLE_SCRIPT_COUNT.CNT as FLOAT)* 100,1),0) AS 'Actual %', " & _
"case when ISNULL(CYCLE_SCRIPT_COUNT.CNT,0) > 0 then " & _
"CAST(isnull(PROJECTED.POINT,Case When cast(RCYC_START_DATE as datetime) >= GETDATE() " & _
"Then 0 Else 100 End)AS INT) " & _
"else " & _
"0 " & _
"end as 'Projected %' , " & _
"case when ISNULL(CYCLE_SCRIPT_COUNT.CNT,0) > 0 then " & _
"CASE WHEN CAST(RCYC_START_DATE AS DATETIME) >= GETDATE() THEN 0 " & _
"WHEN ISNULL(CAST(cast(CYCLE_PASSED_COUNT.CNT as FLOAT)/CYCLE_SCRIPT_COUNT.CNT as FLOAT)* 100,0) = 100 THEN 0 " & _
"ELSE " & _
"round((Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE + .99)as FLOAT) * .01) " & _
"* (isnull(DAYS_SKEW.[ORDER],1) " & _
"- Round(Cast(DATEDIFF(hh, RCYC_START_DATE, GetDate() " & _
")as FLOAT) " & _
"/ Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE + .99)as FLOAT)*100,0)) " & _
"/ 24,1) " & _
"END " & _
"else " & _
"0 " & _
"end AS 'Days Ahead/Behind', " & _
"case when ISNULL(CYCLE_SCRIPT_COUNT.CNT,0) > 0 then " & _
"CASE WHEN CAST(RCYC_START_DATE AS DATETIME) >= GETDATE() THEN 0 " & _
"WHEN ISNULL(CAST(cast(CYCLE_PASSED_COUNT.CNT as FLOAT)/CYCLE_SCRIPT_COUNT.CNT as FLOAT)* 100,0) = 100 THEN 0 " & _
"ELSE " & _
"round(abs((Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE + .99)as FLOAT) * .01) " & _
"* (isnull(DAYS_SKEW.[ORDER],1) " & _
"- Round(Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE + .99 " & _
")as FLOAT) " & _
"/ Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE + .99)as FLOAT)*100,0)) " & _
"/ 24),1) " & _
"END " & _
"else " & _
"0 " & _
"end AS 'Effort Remaining', " & _
"ISNULL(CYCLE_PASSED_COUNT.CNT,0) AS 'Passed Scripts', " & _
"CASE WHEN CONVERT (varchar(10), CAST(RCYC_END_DATE AS DATETIME),110) < GetDate() THEN CYCLE_SCRIPT_COUNT.CNT Else " & _
"ISNULL(CAST(((PROJECTED.POINT * CYCLE_SCRIPT_COUNT.CNT) * .01)AS FLOAT),0) END " & _
"AS 'Projected Passed Scripts', " & _
"ISNULL(CYCLE_SCRIPT_COUNT.CNT,0) AS 'Total Scripts', " & _
"ISNULL(BUG.CNT,0) AS 'Active Defects', " & _
"ISNULL(BUGALL.CNT,0) as 'Total Defects', " & _
"ACTIVE_CRITICAL.ACT_CRIT_CNT as 'Critical Defects', " & _
"DAYS_SKEW.[ORDER] as 'Days Skew', " & _
"AUTO_REMAIN_COUNT.CNT as 'Automated Left' " & _
"FROM " & _
"RELEASES INNER JOIN RELEASE_CYCLES WITH (NOLOCK) " & _
"ON RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID " & _
"LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT " & _
"FROM TESTCYCL WITH (NOLOCK) " & _
"WHERE TC_STATUS <> 'N/A' " & _
"GROUP BY TC_ASSIGN_RCYC) AS CYCLE_SCRIPT_COUNT " & _
"ON RELEASE_CYCLES.RCYC_ID = CYCLE_SCRIPT_COUNT.TC_ASSIGN_RCYC " & _
"LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT " & _
"FROM TESTCYCL WITH (NOLOCK) " & _
"GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT " & _
"ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC " & _
"LEFT OUTER JOIN SQA_DASH_PROJECTED_CURVE AS PROJECTED " & _
"ON Round(Cast(DATEDIFF(hh, RCYC_START_DATE, GetDate())as FLOAT) " & _
"/ " & _
"Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE + .99)as FLOAT)*100,0) = PROJECTED.[ORDER] " & _
"LEFT OUTER JOIN SQA_DASH_PROJECTED_CURVE AS DAYS_SKEW " & _
"ON (SELECT TOP 1 POINT FROM SQA_DASH_PROJECTED_CURVE WITH (NOLOCK) WHERE POINT <= " & _
"(ROUND(CAST((CYCLE_PASSED_COUNT.CNT) as Float) " & _
"/ " & _
"(CYCLE_SCRIPT_COUNT.CNT)* 100,0)) ORDER BY POINT DESC) = DAYS_SKEW.POINT " & _
"LEFT OUTER JOIN (SELECT BG_DETECTED_IN_RCYC, COUNT(*) AS CNT FROM BUG WITH (NOLOCK) " & _
" WHERE (BUG.BG_STATUS <> 'Invalid' " & _
" AND BUG.BG_STATUS <> 'Closed' " & _
" AND BUG.BG_STATUS <> 'New' " & _
" AND BUG.BG_STATUS <> 'Deferred') " & _
" GROUP BY BG_DETECTED_IN_RCYC) AS BUG " & _
"ON BUG.BG_DETECTED_IN_RCYC = RCYC_ID " & _
"LEFT OUTER JOIN (SELECT BG_DETECTED_IN_RCYC, COUNT(*) AS CNT FROM BUG WITH (NOLOCK) " & _
" WHERE BUG.BG_STATUS <> 'Invalid' " & _
" GROUP BY BG_DETECTED_IN_RCYC) AS BUGALL " & _
"ON BUGALL.BG_DETECTED_IN_RCYC = RCYC_ID " & _
"LEFT OUTER JOIN (Select RCYC_ID AS AUTO_CYCLE, COUNT(*) AS CNT " & _
" FROM RELEASE_CYCLES, TESTCYCL, TEST " & _
" WHERE TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID " & _
" AND TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID " & _
" AND (TC_STATUS <> 'Passed' AND TC_STATUS <> 'N/A') " & _
" AND TS_TYPE = 'QUICKTEST_TEST' " & _
" GROUP BY RCYC_ID) AS AUTO_REMAIN_COUNT " & _
"ON RELEASE_CYCLES.RCYC_ID = AUTO_REMAIN_COUNT.AUTO_CYCLE " & _
"LEFT OUTER JOIN (Select BG_DETECTED_IN_RCYC, COUNT(BG_BUG_ID) AS ACT_CRIT_CNT from BUG " & _
" WHERE (BG_STATUS <> 'Closed' " & _
" AND BG_STATUS <> 'Invalid' " & _
" AND BG_STATUS <> 'New' " & _
" AND BG_STATUS <> 'Deferred') " & _
" AND BG_SEVERITY like '%Critical%' " & _
" GROUP BY BG_DETECTED_IN_RCYC) AS ACTIVE_CRITICAL " & _
"ON ACTIVE_CRITICAL.BG_DETECTED_IN_RCYC = RCYC_ID " & _
"WHERE RCYC_START_DATE IS NOT NULL " & _
"AND RCYC_END_DATE IS NOT NULL " & _
"AND REL_USER_02 IS NOT NULL) " & _
"SELECT [Release ID],[Release Name],[PM / PDM], " & _
" MIN([Test Start Date]) AS 'Test Start Date', " & _
" MAX([Test End Date]) AS 'Test End Date', " & _
" [Production Start Date], " & _
" (CASE WHEN COUNT(*) = 1 THEN " & _
" SUM([Actual %]) ELSE " & _
" Round(CAST(SUM([Passed Scripts])AS FLOAT)/case when sum([Total Scripts]) = 0 then 1 else sum([Total Scripts]) end *100,1) " & _
" END) AS 'Actual %', " & _
" cast(round((CASE " & _
" WHEN CAST(MIN([Test Start Date]) AS DATETIME) >= GETDATE() THEN 0 " & _
" WHEN CAST([Production Start Date] as DATETIME) < GETDATE() THEN 100 ELSE " & _
" (CASE " & _
" WHEN COUNT(*) = 1 THEN " & _
" SUM([Projected %]) ELSE " & _
" cast(CAST(SUM([Projected Passed Scripts]) AS FLOAT)/CAST(SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end) AS FLOAT) *100 AS FLOAT) " & _
" END) " & _
" END),0)as int) AS 'Projected %', " & _
" convert(varchar,round(MAX([Effort Remaining]),1)) AS 'Effort Remaining', " & _
" SUM([Active Defects]) AS 'Active Defects', " & _
"cast(ISNULL(SUM([Total Defects]),0) as FLOAT) " & _
"/ " & _
"cast(Case When ISNULL(SUM([Total Scripts]),0) = 0 then 1 else SUM([Total Scripts]) END as FLOAT) AS 'Defect Density-1', " & _
"isnull(sum([Critical Defects]),0) / case when (isnull(SUM([Active Defects]),0)) = 0 then 1 " & _
"else isnull(SUM([Active Defects]),1) END as 'Critical Defects-2', " & _
"case when (isnull(case when datediff(day,MAX([Test End Date]), GETDATE()) = 0 then 1 " & _
"else datediff(day,MAX([Test End Date]), GETDATE()) END,1)) < 0 then " & _
"abs(isnull(CASE WHEN CAST(MIN([Test Start Date]) AS DATETIME) >= GETDATE() THEN 0 " & _
"WHEN ISNULL(CAST(cast(SUM([Passed Scripts]) as FLOAT)/SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end) as FLOAT)* 100,0) = 100 THEN 0 " & _
"ELSE " & _
"round(abs((Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]))as FLOAT) * .01) " & _
"* (isnull(min([Days Skew]),1) " & _
"- Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]) " & _
")as FLOAT) " & _
"/ Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]))as FLOAT)*100) " & _
"/ 24),1) " & _
"END,0)) " & _
"+ " & _
"abs(isnull(case when datediff(day,MAX([Test End Date]), GETDATE()) = 0 then 1 " & _
"else datediff(day,MAX([Test End Date]), GETDATE()) END,1))+1 " & _
"ELSE " & _
"abs(isnull(CASE WHEN CAST(MIN([Test Start Date]) AS DATETIME) >= GETDATE() THEN 0 " & _
"WHEN ISNULL(CAST(cast(SUM([Passed Scripts]) as FLOAT)/SUM(case when isnull([Total Scripts],0) = 0 then 1 else [Total Scripts] end) as FLOAT)* 100,0) = 100 THEN 0 " & _
"ELSE " & _
"round(abs((Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]))as FLOAT) * .01) " & _
"* (isnull(min([Days Skew]),1) " & _
"- Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]) " & _
")as FLOAT) " & _
"/ case when (Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]))as FLOAT)) = 0 THEN 1 " & _
"ELSE (Cast(DATEDIFF(hh, MIN([Test Start Date]), MAX([Test End Date]))as FLOAT)) END *100) " & _
"/ 24),1) " & _
"END,0)) " & _
"END " & _
"/ " & _
"abs(isnull(case when datediff(day,MAX([Test End Date]), GETDATE()) = 0 then 1 " & _
"else datediff(day,MAX([Test End Date]), GETDATE()) END,1)) as 'Effort/Days Left-3', " & _
"CASE WHEN SUM([Passed Scripts]) = SUM([Total Scripts]) THEN 0 ELSE ( " & _
"ISNULL(CAST(SUM([Passed Scripts])/SUM(case when isnull([Total Scripts],0) = 0 then 1 else [Total Scripts] end) as FLOAT)* 100,1) " & _
"/ " & _
"case when (CAST(isnull((CASE " & _
" WHEN CAST(MIN([Test Start Date]) AS DATETIME) >= GETDATE() THEN 1 " & _
" WHEN CAST(MAX([Production Start Date]) as DATETIME) < GETDATE() THEN 100 ELSE " & _
" (CASE " & _
" WHEN COUNT(*) = 1 THEN " & _
" SUM([Projected %]) ELSE " & _
" cast(SUM([Projected Passed Scripts])/SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end) *100 AS FLOAT) " & _
" END) " & _
" END),Case When cast(MIN([Test Start Date]) as datetime) >= GETDATE() Then 1 " & _
"Else 100 End)AS FLOAT)) = 0 then 1 " & _
"Else " & _
"CAST(isnull((CASE " & _
" WHEN CAST(MIN([Test Start Date]) AS DATETIME) >= GETDATE() THEN 1 " & _
" WHEN CAST(MAX([Production Start Date]) as DATETIME) < GETDATE() THEN 100 ELSE " & _
" (CASE " & _
" WHEN COUNT(*) = 1 THEN " & _
" SUM([Projected %]) ELSE " & _
" cast(SUM([Projected Passed Scripts])/SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end) *100 AS FLOAT) " & _
" END) " & _
" END),Case When cast(MIN([Test Start Date]) as datetime) >= GETDATE() Then 1 " & _
"Else 100 End)AS FLOAT) END) " & _
"END " & _
"as 'Actual/Projected-4', " & _
"isnull(sum([Automated Left]),0) " & _
"/ " & _
"case when ((ISNULL(SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end),1) - ISNULL(SUM([Passed Scripts]),0))) = 0 THEN 1 ELSE " & _
"(ISNULL(SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end),1) - ISNULL(SUM([Passed Scripts]),0)) END As 'Automated/Left-5', " & _
" (isnull(sum(AVERAGE_TIME.[AVERAGE]),0)*isnull(SUM([Active Defects]),0)) " & _
"/ " & _
" case when MIN([Test End Date]) > GETDATE() THEN 1 ELSE " & _
"abs(isnull(case when datediff(day,MIN([Test End Date]), GETDATE()) = 0 then 1 " & _
"else datediff(day,MIN([Test End Date]), GETDATE()) END,1))+1 " & _
" END " & _
"as 'Average-6', " & _
" sum([Total Scripts]) as 'Total Scripts', " & _
"'1' as 'Health' -- For presentation " & _
"FROM tmp_table WITH (NOLOCK) " & _
"LEFT OUTER JOIN (SELECT cast(avg(datediff(hour,[BG_DETECTION_DATE],isnull([BG_CLOSING_DATE],getdate()))/24)as FLOAT) as 'Average' " & _
", BG_DETECTED_IN_REL " & _
"FROM [BUG] " & _
"GROUP BY BG_DETECTED_IN_REL) AS AVERAGE_TIME " & _
"ON AVERAGE_TIME.BG_DETECTED_IN_REL = [Release ID] " & _
"GROUP BY [Release ID],[Release Name],[PM / PDM],[Production Start Date] " & _
"HAVING ([Production Start Date] >= DATEADD(DAY, -3, GetDate()) " & _
" OR " & _
" (CASE WHEN COUNT(*) = 1 THEN " & _
" SUM([Actual %]) ELSE " & _
" Round(CAST(SUM([Passed Scripts])AS FLOAT)/CAST(SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end) AS FLOAT)*100,1) " & _
" END)< '100') " & _
" AND cast([Production Start Date] as datetime) > '10/1/2010' " & _
"ORDER BY cast([Production Start Date] as DateTime) "
da = New SqlClient.SqlDataAdapter(sSQL, conn)
conn.Open()
da.Fill(ds, "default_bsd_qa_db")
conn.Close()
gvProjects.DataSource = ds
gvProjects.DataBind()- Matt
"If I must boast, I will boast of the things that show my weakness"
- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008