DataReader.Read() Not Working

Question

_{Tuesday, March 16, 2010 4:36 AM}

I have used SqlDataReaders a million times. Someone, please tell me what is wrong with this code:

SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = string.Concat("SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '", table, "'");

                SqlDataReader reader = command.ExecuteReader();

//and this is where it just stops... the reader says it has no rows...!!! Running the same query in SSMS produces several rows... what is going on?

                while (reader.Read())

                {

                     etc.....

                }

 

Am I doing something stupid here? I have checked this against similar classes and it is exactly the same, but this one doesn't work...   :-S

All replies (4)

_{Tuesday, March 16, 2010 5:46 AM ✅Answered}

Do you use the same connection details to connect to your database with SSMS and in code?

---

_{Tuesday, March 16, 2010 5:40 AM}

try
            {
                connection.Open();
               *** SqlCommand command = new sqlcommand(connection)
                command.CommandType = CommandType.Text;
                command.CommandText = string.Concat("SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '", table, "'");

                SqlDataReader reader = command.ExecuteReader();

//and this is where it just stops... the reader says it has no rows...!!! Running the same query in SSMS produces several rows... what is going on?

                while (reader.Read())

                {

                     etc.....

                }

---

_{Wednesday, March 17, 2010 11:36 PM}

Hi mgordon,

I think the source code is fine. But are you sure the two queries are the same? Did you debug it and check the query? You can also use SQL Server Profiler to track the query in database.

Please show us the details if you still can't figure out the exact problem even you've debugged it.

Thanks. 

---

_{Thursday, March 18, 2010 5:25 AM}

Sorry for the late reply! Yes, it seems it was the Initial Catalog that was set to a database other than the one I wanted (it was set to "master" LOL). Never assume you've been passed the correct connection string!  :-D