RadGrid calling from temp table in stored procedure

[Joseph]

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

[Joseph]

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

[Joseph]

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

[Yavor]

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.

[Joseph]

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

[Yavor]

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.

[Mike]

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.

[John S.]

Mike,

Thanks for posting this. It helped me out.

John S.

[AndyRutter]

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..?

[Angel Petrov]

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

[AndyRutter]

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

[Angel Petrov]

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

[Lisa]

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!

[András]

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.