RadGrid calling from temp table in stored procedure

14 posts, 0 answers
  1. Joseph
    Joseph avatar
    29 posts
    Member since:
    Sep 2007

    Posted 19 Feb 2009 Link to this post

    Hello,

    I am using Rad Controls for ASP.NET Q2 2008 in MS Visual Studio 2008. Database is MS SQL 2005 Developer. I have a stored procedure that runs some subqueries and puts all of my results into a temporary table (#tmpEmpList).

    I then have a SqlDataSource that has the stored procedure as my SELECT. I initially did not have a temp table, and everything worked as expected. I now have updated my sp to put the data in a temp table first, and then pull what I need out of the temp table as the last statement of the sp. The stored procedure runs properly in MS SQL Management studio.

    When I went to refresh my schema for the RadGrid, I keep getting a message that says

    " Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand properties are valid.
    Invalid object name 'tmpEmpList'."

    Can a RadGrid accept data from a temp table of a stored procedure?

    Thanks,
    Joe

  2. Joseph
    Joseph avatar
    29 posts
    Member since:
    Sep 2007

    Posted 19 Feb 2009 Link to this post

    Hi again,

    Ok, I figured it out. My temp table had a # (pound sign) on it. When I removed the # from the table name, it works perfectly again.

    Thanks,
    Joe
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Joseph
    Joseph avatar
    29 posts
    Member since:
    Sep 2007

    Posted 20 Feb 2009 Link to this post

    And, I am back again. When the # is taken off the temp table it is no longer a temp table. So back to my original question ... Can the Telerik RadGrid accept values from a temp table generated in a stored procedure?

    Thanks,
    Joe
  5. Yavor
    Admin
    Yavor avatar
    11 posts

    Posted 24 Feb 2009 Link to this post

    Hello Joseph,

    I tested the setup locally, with a definition like this:

    .cs
    protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)  
        {  
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("~/App_Data/Nwind.mdb"));  
            OleDbDataAdapter adapter = new OleDbDataAdapter();  
            adapter.SelectCommand = new OleDbCommand("SELECT * FROM Customers", conn);  
            DataTable myDataTable = new DataTable("#tmpEmpList");  
            conn.Open();  
            try  
            {  
                adapter.Fill(myDataTable);  
            }  
            finally  
            {  
                conn.Close();  
            }  
            RadGrid1.DataSource = myDataTable;  
     
        } 

    Let me know if there are any differences in your scenario!

    Sincerely yours,
    Yavor
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  6. Joseph
    Joseph avatar
    29 posts
    Member since:
    Sep 2007

    Posted 24 Feb 2009 Link to this post

    Hi,

    Thanks for the post. I am doing something a little different. My stored procedure is being called in from my SqlDataSource in my .aspx page. When I refresh my data source is when I get the message - "Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand properties are valid. Invalid object name 'tmpEmpList'." 

     Here is my SqlDataSource code:
     
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"   
     
    ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>"   
     
    SelectCommand="spNC_GetActiveEmployees"   
     
    SelectCommandType="StoredProcedure">  
     
    <SelectParameters> 
     
    <asp:QueryStringParameter DefaultValue="%" Name="LastName"   
     
    QueryStringField="LastName" Type="String" /> 
     
    <asp:ControlParameter ControlID="licenseComboBox" Name="License"   
     
    PropertyName="SelectedValue" Type="String" /> 
     
    <asp:ControlParameter ControlID="shiftComboBox" DefaultValue="%"   
     
    Name="ShiftPref" PropertyName="SelectedValue" Type="String" /> 
     
    <asp:ControlParameter ControlID="facilityComboBox" DefaultValue="%"   
     
    Name="FacilityPref" PropertyName="SelectedValue" Type="String" /> 
     
    <asp:ControlParameter ControlID="regionComboBox" DefaultValue="%"   
     
    Name="RegionPref" PropertyName="SelectedValue" Type="String" /> 
     
    <asp:ControlParameter ControlID="specialtyComboBox" DefaultValue="%"   
     
    Name="SpecialtyPref" PropertyName="SelectedValue" Type="String" /> 
     
    </SelectParameters> 
     
    </asp:SqlDataSource> 
     
     


    Thanks,
    Joe
  7. Yavor
    Admin
    Yavor avatar
    11 posts

    Posted 27 Feb 2009 Link to this post

    Hello Joseph,

    At this point, to further progress with the investigation, it will be best if you open a formal support ticket, and send us a small working project, demonstrating your setup, and the unwanted exception.
    We will review it locally, and get back to you with additional information.

    Kind regards,
    Yavor
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  8. Mike
    Mike avatar
    7 posts
    Member since:
    Mar 2006

    Posted 02 May 2012 Link to this post

    I know this post is very old but I see this all the time and it frustrates me. People ask for help and the final post is a support person saying "create a support ticket". Then the rest of us have the issue, find the thread and the answer is no where to be found because no one ever came back and posted the answer. So I'm going to fix that for this thread.

    Instead of creating a temp table in a SQL stored procedure like this:
    CREATE TABLE #tmpTableName(
       Column1 int,
       Column2 int
    )

    Use a SQL table variable in your stored procedure like this:
    DECLARE @tmpTableName TABLE(Column1 int,Column2 int)

    You can do the same things with a table variable as you can with a temp table and now your SQLDataSource and RadGrid will work too.

  9. John S.
    John S. avatar
    90 posts
    Member since:
    Aug 2004

    Posted 26 Feb 2013 Link to this post

    Mike,

    Thanks for posting this. It helped me out.

    John S.
  10. AndyRutter
    AndyRutter avatar
    32 posts
    Member since:
    Mar 2009

    Posted 01 Oct 2013 Link to this post

    Hi,
    4 years on and this is still an issue, surely the grid can just fire the SP and get the column names, the datasource can do it no problem so why not the grid..?
    This answer may help some people but what about those of us who have no control over the SP's themselves..?
    You are saying the solution is to spend hours sitting there manually creating all my columns..?
  11. Angel Petrov
    Admin
    Angel Petrov avatar
    1006 posts

    Posted 04 Oct 2013 Link to this post

    Hi Brian,

    I see no problems binding the grid to a stored procedure. Have you tried simulating such a setup? I did a small experiment with a grid bound to an SqlDataSource which executed a stored procedure and all worked fine on my end as you can see from this video. Are there any differences in your scenario?

    Additionally in attachments you can find the sample page and stored procedure which I have used for testing.

    Regards,
    Angel Petrov
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  12. AndyRutter
    AndyRutter avatar
    32 posts
    Member since:
    Mar 2009

    Posted 04 Oct 2013 Link to this post

    Hi,
    We use this kind of setup all the time however we often do not have the luxury of creating the temporary tables using create table.
    A majority of the SP's are dynamic in nature and contain many temporary tables which are created on the fly.
    It is these ones that return the error described.

    Some of these SP's are on linked servers and we have no access to change them if we wanted to.
    The ones we do have access to can contain a huge amount of columns and can be over 1000 lines long so it would be almost as much work to go through them adding the table definitions as it is to create the columns manually on the front end..

    Cheers
  13. Angel Petrov
    Admin
    Angel Petrov avatar
    1006 posts

    Posted 09 Oct 2013 Link to this post

    Hi Brian,

    In order to provide an exact answer on why are you experiencing such behavior I would advice you to open a formal support ticket and attach a project which replicates the problem there. That way we will be able to inspect it locally and try to find what is causing this issue. Once we resolve the problem I would suggest sharing your findings with the community so others can benefit from them.

    Regards,
    Angel Petrov
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  14. Lisa
    Lisa avatar
    16 posts
    Member since:
    Nov 2013

    Posted 19 Nov 2013 Link to this post

    Thanks Mike. You're solution was perfect. I know it's been a long time, but I like to give credit where credit is due!
  15. András
    András avatar
    1 posts
    Member since:
    Jun 2015

    Posted 18 Mar Link to this post

    I got the same error message when I called another stored procedure with exec command from inside the stored procedure.

    To work around, I temporarily commented out the lines with exec, refreshed my grid schema, then removed the comment.

Back to Top
UI for ASP.NET Ajax is Ready for VS 2017