How to create a temp table in a stored proc that gets called by C#

I need to select data from a database table that has a lot of data and rows. I want to create a stored proc that gets the data from the SQL database table puts that data in a temp table then writes the data to an web application gridview table using C# before dropping the temp table. Below is my SQL for the stored proc.
SQL:

ALTER PROCEDURE [dbo].[sp_Get_ASRDCandidates_Interviewed_Tracking]
      
AS
BEGIN

      SET NOCOUNT ON;
      SELECT      [Status],
                  [Identified Agent],
                  [Resume last sent],
                  [Feedback received],
                  [Candidate Name],
                  [Source],
                  [State],
                  [City],
                  [Phone],
                  [Email],
                  [Zip Code],
                  [Miles Willing to Travel],
                  [Date Entered],
                  [Best way to contact],
                  [Best time to contact],
                  [Licenses],
                  [Sales Exp?],
                  [Years of Sales Exp],
                  [Insurance Exp?],
                  [Yrs of Ins Exp],
                  [Expected Salary Range All In-BaseCommissions],
                  [Days available to work],
                  [Comfortable with cold calling?],
                  [Bilingual skills?],
                  [Microsoft Office proficiency Are you able to Learn new compute],
                  [Reason for interest in selling insurance?],
                  [Date Avail to Start],
                  [Looking for P/T or F/T or will consider both?],
                  [Not Licensed--Would study night and weekend?],
                  [Resume?],
                  [Avail# Interview times Day],
                  [Avail# Interview times Hour],
                  [Notes],
                  [Hire Select?],
                  [Date entered into LSP DB],
                  [Date sent to agent],
                  [Agent sent to],
                  [Agent Sales Leader],
                  [Outcome?],
                  [Date sent to agent1],
                  [Agent sent to1],
                  [Agent Sales Leader1],
                  [Outcome],
                  [Other Agents],
                  [Hired?],
                  [Hiring Agent],
                  [Month-Year]
      FROM ASRDCandidates_Interviewed_Tracking

      CREATE TABLE ##Temp3
(
      [Status] NVARCHAR(255),
      [Identified Agent] NVARCHAR(255),
      [Resume last sent] NVARCHAR(255),
      [Feedback received] NVARCHAR(255),
      [Candidate Name] NVARCHAR(255),
      [Source] NVARCHAR(255),
      [State] NVARCHAR(255),
      [City] NVARCHAR(255),
      [Phone] NVARCHAR(255),
      [Email] NVARCHAR(255),
      [Zip Code] FLOAT,
      [Miles Willing to Travel] NVARCHAR(255),
      [Date Entered] DATETIME,
      [Best way to contact] NVARCHAR(255),
      [Best time to contact] NVARCHAR(255),
      [Licenses] NVARCHAR(255),
      [Sales Exp?] NVARCHAR(255),
      [Years of Sales Exp] NVARCHAR(255),
      [Insurance Exp?] NVARCHAR(255),
      [Yrs of Ins Exp] NVARCHAR(255),
      [Expected Salary Range All In-BaseCommissions] NVARCHAR(255),
      [Days available to work] NVARCHAR(255),
      [Comfortable with cold calling?] NVARCHAR(255),
      [Bilingual skills?] NVARCHAR(255),
      [Microsoft Office proficiency Are you able to Learn new compute] NVARCHAR(255),
      [Reason for interest in selling insurance?] NVARCHAR(255),
      [Date Avail to Start] NVARCHAR(255),
      [Looking for P/T or F/T or will consider both?] NVARCHAR(255),
      [Not Licensed--Would study night and weekend?] NVARCHAR(255),
      [Resume?] NVARCHAR(255),
      [Avail# Interview times Day] NVARCHAR(255),
      [Avail# Interview times Hour] NVARCHAR(255),
      [Notes] NVARCHAR(255),
      [Hire Select?] NVARCHAR(255),
      [Date entered into LSP DB] DATETIME,
      [Date sent to agent] DATETIME,
      [Agent sent to] NVARCHAR(255),
      [Agent Sales Leader] NVARCHAR(255),
      [Outcome?] NVARCHAR(255),
      [Date sent to agent1] NVARCHAR(255),
      [Agent sent to1] NVARCHAR(255),
      [Agent Sales Leader1] NVARCHAR(255),
      [Outcome] NVARCHAR(255),
      [Other Agents] NVARCHAR(255),
      [Hired?] NVARCHAR(255),
      [Hiring Agent] NVARCHAR(255),
      [Month-Year] DATETIME
);

INSERT INTO ##Temp3 (
                  [Status],
                  [Identified Agent],
                  [Resume last sent],
                  [Feedback received],
                  [Candidate Name],
                  [Source],
                  [State],
                  [City],
                  [Phone],
                  [Email],
                  [Zip Code],
                  [Miles Willing to Travel],
                  [Date Entered],
                  [Best way to contact],
                  [Best time to contact],
                  [Licenses],
                  [Sales Exp?],
                  [Years of Sales Exp],
                  [Insurance Exp?],
                  [Yrs of Ins Exp],
                  [Expected Salary Range All In-BaseCommissions],
                  [Days available to work],
                  [Comfortable with cold calling?],
                  [Bilingual skills?],
                  [Microsoft Office proficiency Are you able to Learn new compute],
                  [Reason for interest in selling insurance?],
                  [Date Avail to Start],
                  [Looking for P/T or F/T or will consider both?],
                  [Not Licensed--Would study night and weekend?],
                  [Resume?],
                  [Avail# Interview times Day],
                  [Avail# Interview times Hour],
                  [Notes],
                  [Hire Select?],
                  [Date entered into LSP DB],
                  [Date sent to agent],
                  [Agent sent to],
                  [Agent Sales Leader],
                  [Outcome?],
                  [Date sent to agent1],
                  [Agent sent to1],
                  [Agent Sales Leader1],
                  [Outcome],
                  [Other Agents],
                  [Hired?],
                  [Hiring Agent],
                  [Month-Year]
                  )

SELECT      [Status],
                  [Identified Agent],
                  [Resume last sent],
                  [Feedback received],
                  [Candidate Name],
                  [Source],
                  [State],
                  [City],
                  [Phone],
                  [Email],
                  [Zip Code],
                  [Miles Willing to Travel],
                  [Date Entered],
                  [Best way to contact],
                  [Best time to contact],
                  [Licenses],
                  [Sales Exp?],
                  [Years of Sales Exp],
                  [Insurance Exp?],
                  [Yrs of Ins Exp],
                  [Expected Salary Range All In-BaseCommissions],
                  [Days available to work],
                  [Comfortable with cold calling?],
                  [Bilingual skills?],
                  [Microsoft Office proficiency Are you able to Learn new compute],
                  [Reason for interest in selling insurance?],
                  [Date Avail to Start],
                  [Looking for P/T or F/T or will consider both?],
                  [Not Licensed--Would study night and weekend?],
                  [Resume?],
                  [Avail# Interview times Day],
                  [Avail# Interview times Hour],
                  [Notes],
                  [Hire Select?],
                  [Date entered into LSP DB],
                  [Date sent to agent],
                  [Agent sent to],
                  [Agent Sales Leader],
                  [Outcome?],
                  [Date sent to agent1],
                  [Agent sent to1],
                  [Agent Sales Leader1],
                  [Outcome],
                  [Other Agents],
                  [Hired?],
                  [Hiring Agent],
                  [Month-Year]
FROM ##Temp3
drop table ##Temp3      

C# code:
  var _mconn = connectionString;

            //Get data from the registration table
            var dtCandidates_Interviewed_Tracking = new DataTable();
            //id = "1";
            //var ID = id;
            var _sqlCommand = "sp_Get_ASRDCandidates_Interviewed_Tracking";

            try
            {
                SqlConnection con = new SqlConnection();

                con.ConnectionString = _mconn;

                using (var cmd = new SqlCommand())
                {
                    //open the connection
                    con.Open();

                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = _sqlCommand;

                    using (var sda = new SqlDataAdapter(cmd))
                        sda.Fill(dtCandidates_Interviewed_Tracking); // states returned Count = 0
                }

                foreach (DataRow row in dtCandidates_Interviewed_Tracking.Select())
                {
                    _status = Convert.ToString(row["Status"]);
                    _identifiedAgent = Convert.ToString(row["Identified Agent"]);
                    _resumeLastSent = Convert.ToString(row["Resume last sent"]);
                    _feedbackReceived = Convert.ToString(row["Feedback received"]);
                    _candidateName = Convert.ToString(row["Candidate Name"]);
                    _source = Convert.ToString(row["Source"]);
                    _state = Convert.ToString(row["State"]);
                    _city = Convert.ToString(row["City"]);
                    _phone = Convert.ToString(row["Phone"]);
                    _email = Convert.ToString(row["Email"]);
                    _zipCode= Convert.ToString(row["Zip Code"]);
                    _milesWillingToTravel = Convert.ToString(row["Miles Willing to Travel"]);
                    _dateEntered = Convert.ToString(row["Date Entered"]);
                    _bestWayToContact = Convert.ToString(row["Best way to contact"]);
                    _bestTimeToContact = Convert.ToString(row["Best time to contact"]);
                    _licenses = Convert.ToString(row["Licenses"]);
                    _salesExp = Convert.ToString(row["Sales Exp?"]);
                    _insuranceExp = Convert.ToString(row["Insurance Exp?"]);
                    _yrsOfInsExp = Convert.ToString(row["Yrs of Ins Exp"]);
                    _expectedSalaryRange = Convert.ToString(row["Expected Salary Range All In-BaseCommissions"]);
                    _daysAvailableToWork = Convert.ToString(row["Days available to work"]);
                    _comfortableWithColdCalling = Convert.ToString(row["Comfortable with cold calling?"]);
                    _bilingualSkills = Convert.ToString(row["Bilingual skills?"]);
                    _microsoftOfficeProficiencyAreYouAbleToLearnNewCompute = Convert.ToString(row["Microsoft Office proficiency Are you able to Learn new compute"]);
                    _reasonForInterestInSellingInsurance = Convert.ToString(row["Reason for interest in selling insurance?"]);
                    _dateAvailToStart = Convert.ToString(row["Date Avail to Start"]);
                    _lookingForWork = Convert.ToString(row["Looking for P/T or F/T or will consider both?"]);
                    _notLicensed = Convert.ToString(row["Not Licensed--Would study night and weekend?"]);
                    _resume = Convert.ToString(row["Resume?"]);
                    _interviewTimesDay = Convert.ToString(row["Avail# Interview times Day"]);
                    _interviewTimesHour = Convert.ToString(row["Avail# Interview times Hour"]);
                    _notes = Convert.ToString(row["Notes"]);
                    _hireSelect = Convert.ToString(row["Hire Select?"]);
                    _dateEnteredIntoLSPDB = Convert.ToString(row["Date entered into LSP DB"]);
                    _dateSentToAgent = Convert.ToString(row["Date sent to agent"]);
                    _agentSentTo = Convert.ToString(row["Agent sent to"]);
                    _agentSalesLeader= Convert.ToString(row["Agent Sales Leader"]);
                    _outcome = Convert.ToString(row["Outcome?"]);
                    _dateSentToAgent1 = Convert.ToString(row["Date sent to agent1"]);
                    _agentSentTo1 = Convert.ToString(row["Agent sent to1"]);
                    _agentSalesLeader2 = Convert.ToString(row["Agent Sales Leader1"]);
                    _Outcome = Convert.ToString(row["Outcome"]);
                    _otherAgents = Convert.ToString(row["Other Agents"]);
                    _Hired = Convert.ToString(row["Hired?"]);
                    _hiringAgent = Convert.ToString(row["Hiring Agent"]);
                    _monthYear = Convert.ToString(row["Month-Year"]);
                }

                _status1 = _status;
                _identifiedAgent1 = _identifiedAgent;
                _resumeLastSent1 = _resumeLastSent;
                _feedbackReceived1 = _feedbackReceived;
                _candidateName1 = _candidateName;
                _source1 = _source;
                _state1 = _state;
                _city1 = _city;
                _phone1 = _phone;
                _email1 = _email;
                _zipCode1 = _zipCode;
                _milesWillingToTravel1 = _milesWillingToTravel;
                _dateEntered1 = _dateEntered;
                _bestWayToContact1 = _bestWayToContact;
                _bestTimeToContact1 = _bestTimeToContact;
                _licenses1 = _licenses;
                _salesExp1 = _salesExp;
                _insuranceExp1 = _insuranceExp;
                _outcome1 = _outcome;
                _agentSalesLeader1 = _agentSalesLeader;
                _yrsOfInsExp1 = _yrsOfInsExp;
                _expectedSalaryRange1 = _expectedSalaryRange;
                _daysAvailableToWork1 = _daysAvailableToWork;
                _comfortableWithColdCalling1 = _comfortableWithColdCalling;
                _bilingualSkills1 = _bilingualSkills;
                _microsoftOfficeProficiencyAreYouAbleToLearnNewCompute1 = _microsoftOfficeProficiencyAreYouAbleToLearnNewCompute;
                _reasonForInterestInSellingInsurance1 = _reasonForInterestInSellingInsurance;
                _dateAvailToStart1 = _dateAvailToStart;
                _lookingForWork1 = _lookingForWork;
                _notLicensed1 = _notLicensed;
                _resume1 = _resume;
                _interviewTimesDay1 = _interviewTimesDay;
                _interviewTimesHour1 = _interviewTimesHour;
                _notes1 = _notes;
                _hireSelect1 = _hireSelect;
                _dateEnteredIntoLSPDB1 = _dateEnteredIntoLSPDB;
                _dateSentToAgent1 = _dateSentToAgent;
                _agentSentTo1 = _agentSentTo;
                _agentSalesLeader22 = _agentSalesLeader2;
                _Outcome1 = _Outcome;
                _otherAgents1 = _otherAgents;
                _Hired1 = _Hired;
                _hiringAgent1 = _hiringAgent;
                _monthYear1 = _monthYear;

                con.Close();

                }
                catch (SqlException ex)
                {
                    lblError.Text = ex.InnerException.Message;
                }

                //store the datatable ViewState
                ViewState["CurrentTable"] = dtCandidates_Interviewed_Tracking;
            }
            catch (SqlException ex)
            {
                lblError.Text = ex.InnerException.Message;
            }
newjeep19Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
I don't see the need for a temp table. Why don't you just issue a SELECT statement in the stored procedure and load that data into the DataSet/DataTable? You can then just bind the grid to the dataset as a DataSource.
0
newjeep19Author Commented:
I need a temp table because it takes 10 minuets to load the data onto the gridview.
0
MlandaTCommented:
Where is the real bottleneck? Is it with the SQL or the displaying of the values? How long does the sp_Get_ASRDCandidates_Interviewed_Tracking stored procedure take to execute when run directly against the database (say from SQL Management Studio)?

How many rows are typically returned by the stored procedure? Can the query be optimised with indexes and such?

What is the purpose of the foreach (DataRow row in dtCandidates_Interviewed_Tracking.Select()). There are some assignments that are happening there. Are they necessary to do for each row? Is that not slowing things down as well?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

newjeep19Author Commented:
It is the displaying of the values in the gridview. When executing the stored proc in SQL it runs w/in a second. When I run the application calling the stored proc to run w/out the temp table it takes over 10 min and can time out. That is why I am trying to create the temp table in the stored proc to get the data and then return it to the gridview. However, I assume that the temp table is dropped before the data is loaded onto the gridview.
0
newjeep19Author Commented:
The  foreach (DataRow row in dtCandidates_Interviewed_Tracking.Select()).  is getting the data rows and then populating the gridview.
See below: ASP.NET gridview

<asp:GridView ID="gvCandidateInterviewedTracking" runat="server"
                                            BackColor="White" BorderColor="#999999"  
                                            BorderWidth="1px" DataKeyNames="Candidate Name"
                                            OnRowCancelingEdit="gvCandidateInterviewedTracking_RowCancelingEdit"
                                            OnRowDataBound="gvCandidateInterviewedTracking_RowDataBound"
                                            CellPadding="3" ForeColor="Black"
                                            GridLines="Vertical" ShowFooter="true"
                                            AutoGenerateColumns="false" OnRowDeleting="imgDeleteBtn"
                                            ShowHeaderWhenEmpty="true" CssClass="gridview"
                                            BorderStyle="Ridge" Font-Size="9pt"
                                            OnSelectedIndexChanged="gvCandidateInterviewedTracking_SelectedIndexChanged">
                                    <FooterStyle BackColor="#EBEBEB" />
                                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                                    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                                    <HeaderStyle BackColor="#4CAEE3" ForeColor="White" Width="100%" />
                                    <RowStyle BackColor="#EBEBEB" ForeColor="Black" />
                                    <AlternatingRowStyle BackColor="White" ForeColor="Black" />
                                        <Columns>
                                            <asp:TemplateField>
                                                <ItemTemplate>
                                                    <asp:ImageButton ID="imgBtnSave" runat="server" ImageUrl="~/images/save.png" Width="44px" OnClick="imgBtnSave_Click" Visible="true" ToolTip="Saves a new record." />
                                                </ItemTemplate>
                                                <FooterStyle HorizontalAlign="Right" />
                                                <FooterTemplate>
                                                    <asp:ImageButton ID="imBtnAddNewRow" runat="server" ImageUrl="~/images/AddARow.png" Width="44px" OnClick="imBtnAddNewRowd_Click" Visible="true" ToolTip="Adds a new row." />
                                                </FooterTemplate>
                                            </asp:TemplateField>
                                            <asp:TemplateField>
                                                <ItemTemplate>
                                                    <asp:ImageButton ID="imgUpdateBtn" runat="server" ImageUrl="~/images/update.png" Width="44px" OnClick="imgUpdateBtn_Click" ToolTip="Updates the agents information." />
                                                </ItemTemplate>
                                            </asp:TemplateField>
                                             <asp:TemplateField>
                                                <ItemTemplate>
                                                     <asp:ImageButton ID="imgDeleteBtn" runat="server" ImageUrl="~/images/remove_user.png" Width="44px" CausesValidation="False" CommandName="Delete"  OnClientClick="javascript : return confirm('Do you really want to \nDelete this agent from the email list !?!?'); " Visible="true" ToolTip="Delete the attendee from the course."/>
                                                </ItemTemplate>
                                            </asp:TemplateField>                                      
                                            <asp:TemplateField HeaderText="Status">
                                                 <ItemTemplate>
                                                    <asp:TextBox ID="txtStatus" runat="server" Text='<%# Bind("Status") %>' />
                                                     <asp:requiredfieldvalidator id="RequiredFieldValidator1" runat="server" controltovalidate="txtStatus" display="None" errormessage="Please enter the status.">
                                                     </asp:requiredfieldvalidator>
                                                </ItemTemplate>                                              
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Identified Agent">
                                                 <ItemTemplate>
                                                     <asp:TextBox ID="txtIdentifiedAgent" runat="server" Text='<%# Bind("[Identified Agent]") %>' />
                                                     <asp:requiredfieldvalidator id="RequiredFieldValidator2" runat="server" controltovalidate="txtIdentifiedAgent" display="None" errormessage="Please enter the indentified field agents.">
                                                     </asp:requiredfieldvalidator>
                                                 </ItemTemplate>
                                             </asp:TemplateField>
                                             <asp:TemplateField HeaderText="Resume last sent">
                                                 <ItemTemplate>
                                                     <asp:TextBox ID="txtResumelastsent" runat="server" Text='<%# Bind("[Resume last sent]") %>' />
                                                     <asp:requiredfieldvalidator id="RequiredFieldValidator3" runat="server" controltovalidate="txtResumelastsent" display="None" errormessage="Please enter the resume last sent.">
                                                     </asp:requiredfieldvalidator>
                                                 </ItemTemplate>
                                             </asp:TemplateField>
                                             <asp:TemplateField HeaderText="Feedback received">
                                                 <ItemTemplate>
                                                     <asp:TextBox ID="txtFeedbackreceived" runat="server" Text='<%# Bind("[Feedback received]") %>' />
                                                     <asp:requiredfieldvalidator id="RequiredFieldValidator4" runat="server" controltovalidate="txtFeedbackreceived" display="None" errormessage="Please enter Feedback received.">
                                                     </asp:requiredfieldvalidator>
                                                 </ItemTemplate>
                                             </asp:TemplateField>
                                             <asp:TemplateField HeaderText="Candidate Name">
                                                 <ItemTemplate>
                                                     <asp:TextBox ID="txtCandidateName" runat="server" Text='<%# Bind("[Candidate Name]") %>' />
                                                     <asp:requiredfieldvalidator id="RequiredFieldValidator5" runat="server" controltovalidate="txtCandidateName" display="None" errormessage="Please enter the candidate name.">
                                                     </asp:requiredfieldvalidator>
                                                 </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Source">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtSource" runat="server" Text='<%# Bind("Source") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator6" runat="server" controltovalidate="txtSource" display="None" errormessage="Please enter the Source.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>  
                                                 <asp:TemplateField HeaderText="State">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtState" runat="server" Text='<%# Bind("State") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator7" runat="server" controltovalidate="txtState" display="None" errormessage="Please enter the State.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="City">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtCity" runat="server" Text='<%# Bind("City") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator8" runat="server" controltovalidate="txtCity" display="None" errormessage="Please enter the City.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Phone">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtPhone" runat="server" Text='<%# Bind("Phone") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator9" runat="server" controltovalidate="txtPhone" display="None" errormessage="Please enter the Phone.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Email">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtEmail" runat="server" Text='<%# Bind("Email") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator10" runat="server" controltovalidate="txtEmail" display="None" errormessage="Please enter the Email.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Zip Code">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtZipCode" runat="server" Text='<%# Bind("[Zip Code]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator11" runat="server" controltovalidate="txtZipCode" display="None" errormessage="Please enter the Zip Code.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Miles Willing to Travel">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtMilesWillingtoTravel" runat="server" Text='<%# Bind("[Miles Willing to Travel]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator12" runat="server" controltovalidate="txtMilesWillingtoTravel" display="None" errormessage="Please enter the Miles Willing to Travel.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Date Entered">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtDateEntered" runat="server" Text='<%# Bind("[Date Entered]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator13" runat="server" controltovalidate="txtDateEntered" display="None" errormessage="Please enter the Date Entered.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Best way to contact">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtBestwaytocontact" runat="server" Text='<%# Bind("[Best way to contact]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator14" runat="server" controltovalidate="txtBestwaytocontact" display="None" errormessage="Please enter the best way to contact.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Best time to contact">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtBesttimetocontact" runat="server" Text='<%# Bind("[Best time to contact]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator15" runat="server" controltovalidate="txtBesttimetocontact" display="None" errormessage="Please enter the best time to contact.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Licenses">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtLicenses" runat="server" Text='<%# Bind("[Licenses]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator16" runat="server" controltovalidate="txtLicenses" display="None" errormessage="Please enter the best time to licenses.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Sales Expexperince">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtSalesExp" runat="server" Text='<%# Bind("[Sales Exp?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator17" runat="server" controltovalidate="txtSalesExp" display="None" errormessage="Please enter sales experence.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>  
                                                 <asp:TemplateField HeaderText="Insurance Expexperince">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtInsuranceExp" runat="server" Text='<%# Bind("[Insurance Exp?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator18" runat="server" controltovalidate="txtInsuranceExp" display="None" errormessage="Please enter insurance experence.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Yrs of Ins Exp">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtYrsofInsExp" runat="server" Text='<%# Bind("[Yrs of Ins Exp]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator19" runat="server" controltovalidate="txtYrsofInsExp" display="None" errormessage="Please enter the years of insurance experince.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Expected Salary Range (All In-Base+Commissions)">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtExpectedSalaryRange" runat="server" Text='<%# Bind("[Expected Salary Range All In-BaseCommissions]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator20" runat="server" controltovalidate="txtExpectedSalaryRange" display="None" errormessage="Please enter in the expected salary range (all in-base+commissions).">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>  
                                                 <asp:TemplateField HeaderText="Days available to work">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtDaysavailable" runat="server" Text='<%# Bind("[Days available to work]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator21" runat="server" controltovalidate="txtDaysavailable" display="None" errormessage="Please enter in the days available to work.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Comfortable with cold calling">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtComfortablcoldcalling" runat="server" Text='<%# Bind("[Comfortable with cold calling?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator22" runat="server" controltovalidate="txtComfortablcoldcalling" display="None" errormessage="Please enter in the comfortable with cold calling.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Bilingual skills">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtBilingual" runat="server" Text='<%# Bind("[Bilingual skills?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator23" runat="server" controltovalidate="txtBilingual" display="None" errormessage="Please enter in bilingual skills.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Microsoft Office proficiency /Are you able to Learn new computer programs">
                                                     <ItemTemplate>                                                                    
                                                         <asp:TextBox ID="txtMicrosoftOfficeProficiency" runat="server" Text='<%# Bind("[Microsoft Office proficiency Are you able to Learn new compute]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator24" runat="server" controltovalidate="txtMicrosoftOfficeProficiency" display="None" errormessage="Please enter in Microsoft Office Proficiency.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Reason for interest in selling insurance">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtInterestSellingInsurance" runat="server" Text='<%# Bind("[Reason for interest in selling insurance?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator25" runat="server" controltovalidate="txtInterestSellingInsurance" display="None" errormessage="Please enter in reason for interest in selling insurance.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Looking for P/T or F/T or will consider both">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtEmpStatus" runat="server" Text='<%# Bind("[Looking for P/T or F/T or will consider both?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator26" runat="server" controltovalidate="txtEmpStatus" display="None" errormessage="Please enter in the looking for P/T or F/T or will consider both info.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Not Licensed--Would study night and weekend">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtNotLicensed" runat="server" Text='<%# Bind("[Not Licensed--Would study night and weekend?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator27" runat="server" controltovalidate="txtNotLicensed" display="None" errormessage="Please enter in not licensed.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Resume">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtResume" runat="server" Text='<%# Bind("[Resume?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator28" runat="server" controltovalidate="txtResume" display="None" errormessage="Please enter in Resume.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Avail. Interview times Day">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtInterview" runat="server" Text='<%# Bind("[Avail# Interview times Day]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator29" runat="server" controltovalidate="txtInterview" display="None" errormessage="Please enter in Avail. Interview times Day.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Avail. Interview times Hour">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtInterviewTimes" runat="server" Text='<%# Bind("[Avail# Interview times Hour]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator30" runat="server" controltovalidate="txtInterviewTimes" display="None" errormessage="Please enter in Interview times.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Notes">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtNotes" runat="server" Text='<%# Bind("[Notes]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator31" runat="server" controltovalidate="txtNotes" display="None" errormessage="Please enter in Notes.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Hire Select">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtHireSelect" runat="server" Text='<%# Bind("[Hire Select?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator32" runat="server" controltovalidate="txtHireSelect" display="None" errormessage="Please enter for Hire Select.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Date entered into LSP DB">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtDateEnteredLSPDB" runat="server" Text='<%# Bind("[Date entered into LSP DB]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator33" runat="server" controltovalidate="txtDateEnteredLSPDB" display="None" errormessage="Please enter for Date entered into LSP DB.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Date sent to agent">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtDateSentToAgent2" runat="server" Text='<%# Bind("[Date sent to agent]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator34" runat="server" controltovalidate="txtDateSentToAgent2" display="None" errormessage="Please enter in Date sent to agent.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Agent sent to">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtAgentSentTo" runat="server" Text='<%# Bind("[Agent sent to]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator35" runat="server" controltovalidate="txtAgentSentTo" display="None" errormessage="Please enter in Agent Sent To.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Agent Sales Leader">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtAgentSalesLeader" runat="server" Text='<%# Bind("[Agent Sales Leader]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator36" runat="server" controltovalidate="txtAgentSalesLeader" display="None" errormessage="Please enter in Agent Sales Leader.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Outcome">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtOutcome" runat="server" Text='<%# Bind("[Outcome?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator37" runat="server" controltovalidate="txtOutcome" display="None" errormessage="Please enter in outcome.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Date sent to agent">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtDateSentToAgent" runat="server" Text='<%# Bind("[Date sent to agent]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator38" runat="server" controltovalidate="txtDateSentToAgent" display="None" errormessage="Please enter in Date sent to agent.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Agent sent to 2">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtDateSentToAgent3" runat="server" Text='<%# Bind("[Agent sent to]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator39" runat="server" controltovalidate="txtDateSentToAgent3" display="None" errormessage="Please enter in Date Sent To Agent.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Agent Sales Leader 2">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtAgentSalesLeader2" runat="server" Text='<%# Bind("[Agent Sales Leader]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator40" runat="server" controltovalidate="txtAgentSalesLeader2" display="None" errormessage="Please enter in Agent Sales Leader.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Outcome 2">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtOutcome2" runat="server" Text='<%# Bind("[Outcome]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator41" runat="server" controltovalidate="txtOutcome2" display="None" errormessage="Please enter in Outcome.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Other Agents">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtOtherAgents" runat="server" Text='<%# Bind("[Other Agents]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator42" runat="server" controltovalidate="txtOtherAgents" display="None" errormessage="Please enter in Other Agents.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Hired">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtHired" runat="server" Text='<%# Bind("[Hired?]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator43" runat="server" controltovalidate="txtHired" display="None" errormessage="Please enter in Hired.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Hiring Agent">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtHiringAgent" runat="server" Text='<%# Bind("[Hiring Agent]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator44" runat="server" controltovalidate="txtHiringAgent" display="None" errormessage="Please enter in Hiring Agent.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>
                                                 <asp:TemplateField HeaderText="Month-Year">
                                                     <ItemTemplate>
                                                         <asp:TextBox ID="txtMonthYear" runat="server" Text='<%# Bind("[Month-Year]") %>' />
                                                         <asp:requiredfieldvalidator id="RequiredFieldValidator45" runat="server" controltovalidate="txtMonthYear" display="None" errormessage="Please enter in Month-Year.">
                                                        </asp:requiredfieldvalidator>
                                                     </ItemTemplate>
                                                 </asp:TemplateField>                                                                                                                                          
                                             </Columns>
                                  <SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True" BackColor="#009999"></SelectedRowStyle>
                            </asp:GridView>
0
MlandaTCommented:
You did not give an indication of the number of rows we are looking at here:

When you bind data to a GridView, there is no need for a foreach loop. You can just bind directly to the GridView. Something like this:

I'm assuming a version of sp_Get_ASRDCandidates_Interviewed_Tracking which DOES NOT have anything to do with temp table. Pretty much just the SELECT statement.
SqlConnection con =  new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Get_ASRDCandidates_Interviewed_Tracking";
cmd.Connection = con;

try
{
    con.Open();

    //   Create a data adapter to store the inforamtion
    System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter();
    DataSet dset = new DataSet();
    {
        adp.SelectCommand = cmd;
        adp.Fill(dset, "Results");
    }

    gvCandidateInterviewedTracking.EmptyDataText = "No Records Found";
    gvCandidateInterviewedTracking.DataSource = dset.Tables[0];
    gvCandidateInterviewedTracking.DataBind(); 

}
catch (Exception ex)
{
    throw ex;
}
finally
{
    con.Close();
    con.Dispose();
}

Open in new window

Based on: http://www.aspsnippets.com/Articles/Bind-data-to-ASPNet-GridView-using-Stored-Procedure.aspx

i will be interesting to find out from you how long the 'logical steps' take here. The "adp.Fill" and the "gvCandidateInterviewedTracking.DataBind"
0
newjeep19Author Commented:
Still takes over 10 minuets to write the data to the gridview rows. I modified my code to what you sent.
0
MlandaTCommented:
How many rows are we talking about here? Is it one row, or ten, or hundred, a thousand, a million? Which step in particular takes longest?

You need to see what step is taking longest, getting the data into the dataset OR binding to the grid. Set breakpoints in your code and step through it. What is taking the time.

If you at least provide me with such information, I can also get a clearer picture of things. We want to identify the bottleneck here. Maybe displaying a million rows is going to take time anyways. So let's have some metrics.

Do you have things happening in an ItemDataBound or RowDataBound event of  sorts  here?
0
newjeep19Author Commented:
It looks to be bottle necking at the binding. the amount of rows is 1200 rows. Which should not take ten minuets to load and then bind and then display on the gridview.
0
MlandaTCommented:
Ok. Cool. So this means that we were correct to conclude here that the approach to build a temporary table would not have helped because the issue is not on that phase.

Is there a ItemDataBound event (or similar... ItemCreated, etc)? What happens if you comment the code and you run the data binding  without it? It is possible that something that is being done in there is taking long. These events typically execute for each datarow that is being bound.
0
newjeep19Author Commented:
Thanks for your suggestions. However, same issue as before. I don't know if we are communicating with each  other about what I think the issue is and what you think the issue is. I am sure that the solutions you have been sending me work for what you think the issue is. Basically, I am trying to return a large amount of data to an ASP.NET gridview  the gridview contains many columns and allows the end user to update, add and delete rows in the gridview.  The gridview times out when I am loading the data. The gridview lives on a web application.
0
MlandaTCommented:
Allows the end user to update, add and delete rows in the gridview
I think we are in the same page, except for this bit which I wasn't quite aware of. That said... We need to fix the application in the right place. There are two steps to displaying data to your users...

1 Get the data from the database into the servers memory (a DataSet) and...

2 Bind the data to a grid and send that result to the user.

Both of these can cause a timeout if you have too much data. A common technique is to avoid presenting the user with more data that they can work with in one go anyways (so like... Don't give me 1000 rows for editing in one go). Use paging and stuff. Anyway... I digress. In light of the 2 key stages above, we need to understand which bit is taking the most time. That is why I was asking you to Debug the application and monitor where the time is going. Writing data into a temporary table will not make Step 1 faster than reading the data from the source table. It might in fact make the database operations slower anyways (coz the are more steps happening in preparing the data before Step 1).

So... Help me to understand where the time is going. You said the data operation takes very little time. That means, the data binding and streaming to the user is taking long. So again, a temp table won't help, coz the issue will be volume of data and size of the resulting HTML and not the database operation.

Hope I'm making sense? Please send a screenshot of the timeout error message. That might also help me see where the timeout is happening.
0
MlandaTCommented:
If you look at my code sample... How much time is Line 16 adp.Fill(dset, "Results"); taking? And line 21 gvCandidateInterviewedTracking.DataBind(); ??
0
newjeep19Author Commented:
I am not getting an error message now. The application just spins.
0
MlandaTCommented:
What error message?

You need to "walk" with me here... I'm not sitting in front of your computer... so my hope will be that we I request certain information, you also give me that information. That way, we can conclude this sooner. I requested an indication of the amount of time the key steps are taking. You are my eyes on this one and that feedback will help me build more insights into what is (or is not) happening.

Do you know how to set a breakpoint in code?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.