Ad:

MS SQL Discussion Thread
Marked Solved
Views: 858

Nov 18th, 2008

Problems with parameters and selectcommand

Expand Post »

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:

MS SQL Syntax (Toggle Plain Text)
SELECT * FROM (SELECT table_name 
FROM information_schema.TABLES 
WHERE table_name= @table_name)
select * from (Select table_name 
from information_schema.tables 
where table_name= @table_name)

I know it probably looks lame, but the simple

MS SQL Syntax (Toggle Plain Text)

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!

Similar Threads

need help in VB.NET

konczuras

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

59 posts
since Oct 2008

Permalink

Nov 18th, 2008

Re: Problems with parameters and selectcommand

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Nov 19th, 2008

Re: Problems with parameters and selectcommand

change your command into:

MS SQL Syntax (Toggle Plain Text)
SELECT * 
  FROM (SELECT table_name 
             FROM information_schema.TABLES 
             WHERE table_name= @table_name
          ) AliasName
select * 
  from (Select table_name 
             from information_schema.tables 
             where table_name= @table_name
          ) AliasName

change your second command into:

MS SQL Syntax (Toggle Plain Text)
declare @table_name varchar(255)
SELECT @table_name = 'sysdatabases'
exec ('select * from ' + @table_name)
declare @table_name varchar(255)
select @table_name = 'sysdatabases'
exec ('select * from ' + @table_name)

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 19th, 2008

Re: Problems with parameters and selectcommand

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

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

59 posts
since Oct 2008

Permalink

Nov 19th, 2008

Re: Problems with parameters and selectcommand

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

konczuras

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

59 posts
since Oct 2008

Permalink

Apr 11th, 2011

Re: Problems with parameters and selectcommand

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

MS SQL Syntax (Toggle Plain Text)
<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>
<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!!

vectorsoftware

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Apr 2011

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: linked tables with sql 2008

Next Thread in MS SQL Forum Timeline: desired results from ORDER BY

DaniWeb Message

Cancel Changes

User Name
Password