troubleshooting Question

How do I create 2 SQL temp tables in ASP.NET? I want to join them and create a calculated column.

Avatar of WorkingLate
WorkingLate asked on
ASP.NET
16 Comments1 Solution1801 ViewsLast Modified:
This is my first question ever on a board (you only get to say that once)&

I have two long SQL statements that I need to place the results into separate temp tables.  I just cant get the ASP.NET code right.

In the example below I tried to use Select into a temp table which works fine in SMSE but not on my aspx page.

It doesnt fail in ASP.NET but it doesnt create the temp table either.  If I can get one temp table working I plan on creating additional and joining them then I will be on my way.

The code below is the top half of the page.

<%@ Page Language="VB" %>
 
<%@ Register Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
    Protected Sub btnSetGoal_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        GoalTitle.Text = "Reports exceeding " & goal.Text & " minutes"
    End Sub
    
   
 
    Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs) Handles MyBase.Load
        GoalTitle.Text = "Reports exceeding " & goal.Text & " minutes"
        
    End Sub
    
    
    Protected Sub Menu2_MenuItemClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.MenuEventArgs)
 
    End Sub
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Report TAT </title>
    <style type="text/css">
    html, body {
  margin: 0px;
  padding: 0px;
}
        #form1
        {
            font-family: Arial;
            text-align: left;
        }
        .style1
        {
            font-family: Arial;
            color: #0033CC;
            font-weight: bold;
        }
        
    </style>
</head>
<body>
    <asp:SqlDataSource ID="SqlDataSourceA" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
        
        SelectCommand="SELECT Facility, Day , Count  INTO [##tempExportTable1] FROM (SELECT 'AH 1 Total' AS Facility, CASE WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '1' THEN 'Sun' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '2' THEN 'Mon' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '3' THEN 'Tue' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '4' THEN 'Wed' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '5' THEN 'Thu' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '6' THEN 'Fri' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) = '7' THEN 'Sat' WHEN DATEPART(weekday, MEDICAL_ORDER.MOR_OBSERVATIONTIME) IS NULL THEN 'Total' END AS 'Day', COUNT(*) AS 'Count' FROM dbo.MEDICAL_ORDER INNER JOIN dbo.MRO_ORDER_NUMBER ON dbo.MEDICAL_ORDER.MOR_ID = dbo.MRO_ORDER_NUMBER.MOR_ID INNER JOIN dbo.REPORT_STATE_DATE ON dbo.MRO_ORDER_NUMBER.MRO_ID = dbo.REPORT_STATE_DATE.MRO_ID INNER JOIN dbo.MEDICAL_REPORT_OBJECT ON dbo.MRO_ORDER_NUMBER.MRO_ID = dbo.MEDICAL_REPORT_OBJECT.MRO_ID INNER JOIN dbo.DOCTOR_XCN ON dbo.DOCTOR_XCN.XCN_ID = dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID WHERE (dbo.REPORT_STATE_DATE.TRPS_ID = '12') AND (dbo.MEDICAL_ORDER.MOR_OBSERVATIONTIME BETWEEN '11/01/2008' AND '11/30/2008') AND (dbo.MEDICAL_ORDER.MOR_REQUESTTIME IS NOT NULL) AND (dbo.MEDICAL_ORDER.MOR_OBSERVATIONENDTIME IS NOT NULL) AND (dbo.MRO_ORDER_NUMBER.MON_MOR_OBSERVATIONENDTIME IS NOT NULL) AND (dbo.MEDICAL_ORDER.MOR_REQUESTTIME IS NOT NULL) AND (dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3182' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2711' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10570' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3038' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '7835' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3144' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2674' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10722' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10803' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2668' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2910' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3156' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10850' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2994' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3150' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2492' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10454' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10517' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2986' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3203' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3235' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10630' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '5658' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2685' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11479' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11304' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10568' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3227' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10588' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2847' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2850' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10633' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '4' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10450' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11558' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11557' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3316' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10622' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10378' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3405' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '4642' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11574' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11547' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3069' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '11617' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3145' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10705' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10449' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3426' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10628' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10380' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '2851' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '3669' OR dbo.MEDICAL_ORDER.MOR_ORDERER_XCN_ID = '10600') AND (CONVERT([char], dbo.MEDICAL_ORDER.MOR_OBSERVATIONTIME, 108) &gt;= '07:00:00') AND (CONVERT([char], dbo.MEDICAL_ORDER.MOR_OBSERVATIONTIME, 108) &lt; '15:00:00') AND (dbo.MEDICAL_ORDER.MOR_RECVING_FAC = 'AH') AND (DATEDIFF(mi, dbo.MEDICAL_ORDER.MOR_OBSERVATIONTIME, dbo.REPORT_STATE_DATE.RSD_DATE) &gt;= '60') GROUP BY DATEPART(weekday, dbo.MEDICAL_ORDER.MOR_OBSERVATIONTIME) WITH CUBE) AS derivedtbl_1 SELECT * FROM ##tempExportTable1">
        <SelectParameters>
            <asp:Parameter DefaultValue="AH" Name="facilityname" />
            <asp:ControlParameter ControlID="goal" DefaultValue="60" Name="targetbar" PropertyName="Text" />
        </SelectParameters>
        </asp:SqlDataSource>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 16 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros