sql to get column names only

Question

User-146799434 posted

wasn't sure if this is the right forum, because its an sql question, using sql for an access data base.

i'm trying to create a an asp:datalist and bind just the table column names and descriptions, no data.

it seems I need something other than 'select *' because that will return all the table data.

see below for what I've done in the past for table data. It seems I need something like select table_info from someTable,

and then in the bindings have something like

<asp:Label ID="colName" runat="server"  
            text='<%# DataBinder.Eval(Container.DataItem, "table_info.column_name",  ) %>'> </asp:Label>

<asp:Label ID="colName" runat="server"  
            text='<%# DataBinder.Eval(Container.DataItem, "table_info.description",  ) %>'> </asp:Label>

'create connection
        Dim objConn As OleDbConnection
        objConn = New OleDbConnection(volConn)
        objConn.Open()
 
 'set up command and execute
        Dim strSQL As String = "select * from orientationData"
        Dim objCmd As OleDbCommand
        objCmd = New OleDbCommand(strSQL, objConn)
        Dim objDataReader As OleDbDataReader
        objDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

        '4 Set the datalist datasource to the datareader and databind
        dataList1.DataSource = objDataReader
        dataList1.DataBind()
        'close reader and db at same time
        objDataReader.Close()

            'binding the data to the list
      <ItemTemplate>
        <asp:Label ID="orientationDate" runat="server"  
            text='<%# DataBinder.Eval(Container.DataItem, "orientationDate" ) %>'>
        </asp:Label>
      </ItemTemplate>

 

Answer 1

User-1199946673 posted

http://forums.asp.net/p/1529437/3698800.aspx#3698800
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309488

Answer 2

User826222609 posted

use like this

Dim strSQL As String = "select * from orientationData wehre 1 = 0"

Answer 3

User94749686 posted

hi.

ca you try this.

SELECT * FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'yourTanleName'

hope this will help you.

thanks.

Answer 4

User630150075 posted

Use this in the query

Dim strSQL As String = "select * from orientationData where 1 = 2"

NOTE: The wrong condition is given because it will not bring any data from the table. Use the column names instead of "*" like below.

Dim strSQL As String = "select Col1, Col2 from orientationData where 1 = 2"

Answer 5

User-678237837 posted

Use this string

Dim strSQL As String = "SELECT COLUMN_NAME,COLUMN_DESCRIPTION FROM information_schema.COLUMNS C WHERE table_name = 'orientationData'"
 

Answer 6

User-2080168305 posted

create this function in your dataaccess class like this:

public DataTable GetSchema(string tableName)
 using (DataTable dt = new DataTable())
                {
                    if (null == conn)
                    {
                        conn = new SqlConnection(connectionString);
                        conn.Open();
                    }

                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select * from " + tableName + " where 1 = 0";  
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt.Load(dr);
                            return dt;
                        }
                    }
                }
 finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
            }
        }

So by calling this fucntion with table name as a argument, u get table schema of that table, means all coulom names.

Main Query to get schema is, as u seen "select * from " + tableName + " where 1 = 0".

i hope u get better idea.

Answer 7

User-146799434 posted

Joshi I hope your solution works, I have been trying the GetOleDbSchemaTable(OleDbSchemaGuid.????? technique and so far can get the column names but have been all over this link,  http://forums.asp.net/p/1551130/3803381.aspx#3803381, and cannot find column-description anywhere there. that  whole approach (GetOleDbSchemaTable(OleDbSchemaGuid) is very difficult to follow for me, are there any other write ups on it? will report back soon using the sql approach.

Answer 8

User-146799434 posted

well this doesn't work, it returns   Could not find file 'C:\WINDOWS\system32\information_schema.mdb'.

Dim strSQL As String = "SELECT COLUMN_NAME,COLUMN_DESCRIPTION FROM information_schema.COLUMNS C WHERE table_name = 'orientationData'"

didn't think this would be that hard

Answer 9

User-678237837 posted

This query is not supported in MS-Access.

there is no such thing in access.

The closest thing I know of is the OleDbConnection.GetOleDbSchemaTable() method. if you connect to an access db and call this, it will give you metadata about all the objects. Note that this will work for any OleDb provider that supports the method, not only access providers.

http://support.microsoft.com/kb/309488

 

Answer 10

User-146799434 posted

well this works

Dim schemaTable = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                          New Object() {Nothing, Nothing, "myTableName", Nothing})

        Dim i
        For i = 0 To schemaTable.Rows.Count - 1
            Response.Write(schemaTable.Rows(i)!COLUMN_NAME.ToString & " IS " & schemaTable.Rows(i)!DESCRIPTION.ToString)
        Next i

the problem was to find the info available for the .columns, there is plenty of writeups, etc about using OleDbSchemaGuid but when using any of the methods, like column, just what is available, because you may have an idea but you have to know the specific name.

too bad this took 6 hours, I'm still not sure if there is a sql solution to this, right now it appears not so because of access, other db's seem to support the sql methodology