Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrow Retrieve columns with values

    I need some help with some logic and sql here.

    What I have to do is run a query and then retrieve the name of the columns that have a value that is not Null or 0, so in my gridview I can only display the fields that have a value. And I cant use a switch statement or anything like that in my C# code because the sql table layout will be changed from time to time so it wont always have the same fields.

    Any sql masters out there know of any ways I could accomplish this either through sql statements or in my C# code?

    Thanks

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Not sure about Sql, but in C# you can make an sql statement that will do it. First get all of your column names for that table, next loop through your data and add on your clause. So, in psuedo code:
    Code:
    select column names from your table.
    initialize your dynamic sql string to: "select * from table where ";
    
    for each column
    // here you add on the not null and != 0 part
    sql += column + " is not null and " + column + " != 0 AND "
    end loop
    I can write it up in actual syntax, but I think that gets the point accross.

    Something like:
    Code:
    private string generateSQL(string strTable)
    {
        string strSql = "select * from " + strTable + " where ";
        SqlConnection con = new SqlConnection(@"your con");
        SqlCommand command = new SqlCommand("select COLUMN_NAME From yourDB.Information_Schema.Columns where table_name = '" + strTable + "'", con);
        con.Open();
    
        SqlDataReader reader;
        reader = command.ExecuteReader();
        while(reader.Read())
        {
           strSql += reader.GetString(0) + " IS NOT NULL AND " + reader.GetString(0) + " != 0 AND ";
        }
        reader.Close();
        con.Close();
    
        return strSql.Substring(0, strSql.Length -4);
    }
    
    // usage
    string strTest = generateSQL("tbl");
    Watch out for datatype issues on the != 0 part.
    Good luck;
    Last edited by Brandoe85; 07-26-2006 at 11:20 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •