0

i'm just trying to retrieve the column names of a table in a database. the SQL query i've written (which works in phpMyAdmin) is:

query = "SELECT column_name FROM information_schema.columns WHERE table_schema='hf_framework' AND table_name='elements'";

my code is as follows:

connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();

while (dataReader.Read()) {
    //stuff in there
}

dataReader.Read() always returns false and i never enter the while loop.

if, however, i change the query to:

query = "SELECT column_name FROM information_schema.columns";

...then i do enter into the while loop. what am i missing? thanks very much.

4mla1fn
  • 169
  • 1
  • 15
  • 1
    Do you _really_ have `hf_framework` schema and `elements` table inside of it? Double check them.. – Soner Gönül Mar 04 '16 at 07:28
  • I would switch this to use: connection.GetSchema("Columns", new string[] { "hf_framework", "Elements", null }); // you may need to check the placement of the restrictions variables – Dominic Cotton Mar 04 '16 at 10:03
  • @Soner Gönül: yes, the schema and table do definitely exist. to confirm i've copied and pasted that same query into phpMyAdmin many, many times and it always returns the correct results. i'd thought the problem was with the WHERE clause but this test query works correctly: query = "SELECT kid0 FROM test.a_table WHERE parent='ron' AND kid1='lily'"; i'm stumped. – 4mla1fn Mar 04 '16 at 14:22
  • groan. i found the problem. the uid and password specified in my connection_string were empty and evidently the built-in databases and tables that were created when i installed XAMPP could be accessed with an empty uid. however, the databases/tables i created didn't automatically allow an empty uid. just setting the uid in the connection_string to "root" was all i needed to do. problem resolved... – 4mla1fn Mar 04 '16 at 20:08

0 Answers0