current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a query that I try in Microsoft SQL Server Management Studio, and this query work fine:

DECLARE @tableVariable TABLE (fecha DATE, valor Float);
INSERT @tableVariable SELECT fecha, valor FROM table1;
SELECT * FROM @tableVariable

When I go to VisualStudio and paste this query in sqldatasource, not work.

Anybody can help me?

Thanks

SOLVED

Finally, the problem is solved remove the tag <SelectParameters> <asp:Parameter Name = "tableVariable" /> </SelectParameters> into sqldatasource.

Thanks to all!

share|improve this question
    
why don't you just paste SELECT fecha, valor FROM table1 in SqlDataSource? I don't think it supports multiple SQL statements. –  w0lf Jan 30 '13 at 8:22
    
The real query is very long for put here, and very complex. It is an abbreviated example. Must be some way to be able to run in asp.net. I use sqldatasource for show the result in a GridView. –  user2024475 Jan 30 '13 at 8:30
    
When I put this in my sqldatasource for it is asking me to Define Parameters, and @tableVariable is not a parameter in my application, but a temp table in sql statement. –  user2024475 Jan 30 '13 at 8:35
    
in this case, just encapsulate the complex query in a stored procedure and use that in the data source –  w0lf Jan 30 '13 at 8:35
    
How i can encapsulate the complex query? Any example? Thanks! –  user2024475 Jan 30 '13 at 8:41

3 Answers 3

up vote 2 down vote accepted

Try creating a stored procedure, like so:

CREATE PROCEDURE ComplexQuery
as
    DECLARE @tableVariable TABLE (fecha DATE, valor Float);
    INSERT @tableVariable SELECT fecha, valor FROM table1;

    -- more complex logic in here

    SELECT * FROM @tableVariable

go

Then, in your data source specify the name of the procedure.

Example:

        <asp:SqlDataSource
            id="SqlDataSource1"
            runat="server"
            ConnectionString="..."
            SelectCommandType="StoredProcedure"                
            SelectCommand="ComplexQuery">
        </asp:SqlDataSource>
share|improve this answer
    
Can I pass arguments (parameters) to a stored procedure? It is possible drop and insert rows dynamically? When I call to ComplexQuery, I need pass arguments o parameters. –  user2024475 Jan 30 '13 at 9:54
    
@user2024475 sure, see the stored procedure link I posted for details on how to add parameters to a stored proc. Then, specify the parameters in the data source as explained here: msdn.microsoft.com/en-us/library/z72eefad(v=vs.100).aspx –  w0lf Jan 30 '13 at 10:07
    
Oh Thanks you very much! This is the best answer for my problem. I haven't got reputation and I can't click to "answer useful". Thanks you very much again! –  user2024475 Jan 30 '13 at 10:10
up vote 1 down vote

Is it not worth just converting the the declared table to a temp table i.e.

CREATE TABLE #tableVariable (fecha DATE, valor Float)

You would need a DROP TABLE #tableVariable later to make sure you are cleaning memory but it will have the desired affect I believe.

share|improve this answer
    
This option is computationally heavier, no? –  user2024475 Jan 30 '13 at 8:59
up vote 0 down vote

I don't think you should be putting a long and complex query into SqlDataSource - that's really for binding a table or view to a datagrid without having to mess around in code behind. If you want to run a complex query you should either set up a stored procedure to do it and call that from SqlCommand, or put the text for the query into SqlCommand. Either way I don't think SqlDataSource is the way to go here.

share|improve this answer
    
I don't need a Stored Procedure. If I put the text of query into SqlCommand, I can use a GridView for show the result? –  user2024475 Jan 30 '13 at 9:04
    
All the SqlCommand is going to do is run a SQL statement against the database. If the statement is going to return some data (SELECT * FROM...) then you still need to get the results of that into something that you can bind to a GridView like a DataSet or DataTable. Maybe SqlDataAdapter is the way to go here - you can feed it some SQL and easily generate a DataSet from it. –  markp3rry Jan 30 '13 at 9:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.