locked
Select Data with dynamic data table name RRS feed

  • Question

  • User-300120154 posted

    Hi,

    I am fetching data from msaccess table like

    Select * from @Table_name

    @Table_name will be dynamic table name

    But getting error

    Using C#.net

     

    Please help!!!!

     

    Monday, April 25, 2011 5:44 AM

Answers

  • User-1199946673 posted

    In general, it isn't really good practice to do a select statement retrieving a fields form a dynamic tablename, because you don't gave control over the dataset that is returned. If you want to retrieve a specied set of fields that are present in all tables, you can do something like:

     

    SELECT field1, field2, field3 FROM (
    SELECT field1, field2, field3, 'table1' as tablename FROM table1
    UNION
    SELECT field1, field2, field3, 'table2' as tablename FROM table2
    UNION
    SELECT field1, field2, field3, 'table3' as tablename FROM table3
    UNION
    SELECT field1, field2, field3, 'table4' as tablename FROM table4
    ) WHERE tablename = @tablename
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 26, 2011 7:01 AM

All replies

  • User-1199946673 posted

    @Table_name will be dynamic table name

    You cannot parameterize tablenames. So you need to write the SQL statement that you want to execute in C#. Be aware of SQL injections....

    Monday, April 25, 2011 3:31 PM
  • User1867929564 posted

    string strtblName="";

    string strSql="Select * from "+strtblName"";

    now execute strSql.
    After it start working,you can consider  preventing Sql Injection.

    Tuesday, April 26, 2011 4:17 AM
  • User-1199946673 posted

    In general, it isn't really good practice to do a select statement retrieving a fields form a dynamic tablename, because you don't gave control over the dataset that is returned. If you want to retrieve a specied set of fields that are present in all tables, you can do something like:

     

    SELECT field1, field2, field3 FROM (
    SELECT field1, field2, field3, 'table1' as tablename FROM table1
    UNION
    SELECT field1, field2, field3, 'table2' as tablename FROM table2
    UNION
    SELECT field1, field2, field3, 'table3' as tablename FROM table3
    UNION
    SELECT field1, field2, field3, 'table4' as tablename FROM table4
    ) WHERE tablename = @tablename
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 26, 2011 7:01 AM