Problems with parameters and selectcommand

[konczuras]

Hello Everybody!

I have the following problem: I want to make an application, which need tables to show, edit, etc. You would be able to select the table, which you want to work on, by a dropdownlist (the whole project is in ASP.NET). I want to load the selected table's data to a gridview, and I desperately need a selectcommand for it.
I have succesfully retreived the name of the table, which is selected in the dropdownlist, with a sqlparameter, but can't use it in my selectcommand. The code is the following:

select * from (Select table_name 
from information_schema.tables 
where table_name= @table_name)

I know it probably looks lame, but the simple

select * from @table_name

refused to work too.

To the first one, I get the error message "incorrect syntax near ')'
For the second, it tells me that I must declare the table variable, but I have no idea, how to link it with the dropdownlist.

I thank you forwardly to your help!

[dickersonka]

is the first one in a stored procedure? where you pass @table_name in?

[huangzhi]

change your command into:

select * 
  from (Select table_name 
             from information_schema.tables 
             where table_name= @table_name
          ) AliasName

change your second command into:

declare @table_name varchar(255)
select @table_name = 'sysdatabases'
exec ('select * from ' + @table_name)

[konczuras]

Thank you for your help, but it still don't works. The first code ignores the 'select * from' from the beginning, and just displays the table name.
The second tells me that the table_name variable is duplicate, so it can't perform it's task.

Any idea, how could I solve this?

Thanks!

[konczuras]

I've figured it out, I used some coding in C#, and it works perfectly, thank you very much.

[vectorsoftware]

Solved!
I wanted TableName to come from the web.config:

<asp:SqlDataSource ID="dsMySource" runat="server" 
  SelectCommand="
    DECLARE @LocalTable as nvarchar(255)
    SET @LocalTable = @MyTable
    DECLARE @mySQL varchar(max)
    SET @mySQL = 'SELECT * from ' + @LocalTable
    EXEC (@mySQL) "
  ...........
    <SelectParameters>
      <asp:QueryStringParameter DefaultValue="<%$ appSettings:TableName%>" Name="MyTable" QueryStringField="HOrder" />
    </SelectParameters>
</asp:SqlDataSource>

!!BOOM!!