Strange error when adding row to datatable

Question

I'm hoping you guys can help me figure out why this is happening. I've been tearing my hair out trying to figure this out.

Here's an example directly from my code (with the boring bits cut out)

...(Set up the connection and command, initialize a datatable "dataTable")...

using (SqlDataReader reader = cmd.ExecuteReader())
{
   //Query storage object
   object[] buffer = new object[reader.FieldCount];

   //Set the datatable schema to the schema from the query
   dataTable = reader.GetSchemaTable();


   //Read the query
   while (reader.Read())
   {  
    reader.GetValues(buffer);
    dataTable.Rows.Add(buffer);
   }
}

The error is

Input string was not in a correct format.Couldn't store in NumericScale Column. Expected type is Int16.

The specific column data types as returned by the schema are (ordered by column)

System.Data.SqlTypes.SqlInt32
System.Data.SqlTypes.SqlInt32
System.Data.SqlTypes.SqlByte
System.Data.SqlTypes.SqlMoney
System.Data.SqlTypes.SqlString
System.Data.SqlTypes.SqlGuid
System.Data.SqlTypes.SqlDateTime

It would appear that the data that should be in column #5 is actually appearing in column #3. But that is pure speculation.

What I know is that in order to use a dataTable "dynamically" with a query that can continue any number of different types of data the best route is to use GetSchemaTable() to retrieve it.

What I Saw In The Debugger

When I dropped into the debugger I took a look at dataTable's types built from the schema vs. the types returned to the object from reader.GetValues(). They are exactly the same.

It seems like dataTable.Rows.Add(buffer) is adding the columns a few columns off from where it should be. But this shouldn't be possible. Especially with the schema being directly built from the reader. I've played with options such as "CommandBehavior.KeyInfo" within ExecuteReader() and still had the same error occur.

Note: I need to run the query this way to enable the end-user to halt the query mid-read. Please do not suggest I scrap this and use an SqlDataAdapter or DataTable.Load() solution.

I'd really appreciate any help. Thank you!

Surely the #TYPE# isn't sensitive information is it? The more information you can give us, the better. — Jon Skeet, Jun 29 '12 at 06:05
Added more specifics. Figured that much specification was unnecessary. — David W, Jun 29 '12 at 06:16

score 2 · Answer 1 · answered Jun 29 '12 at 06:15

2

Method DbDataReader.GetSchemaTable() return metadata table containing column names and types. It is not an empty table that one could expect. For more details see MSDN

answered Jun 29 '12 at 06:15

alexm

6,854
20
24

score 1 · Accepted Answer · answered Jun 29 '12 at 06:17

1

I'm sorry but GetSchemaTable retrieves schema for a given table and GetValues retrieves the actual row data. E.g. your dataTable will contain columns like column name, column type, etc. (see MSDN reference) while your buffer will contain the actual data whose representation will differ from what the dataTable contains.

answered Jun 29 '12 at 06:17

volpav

5,090
19
27

Figured it out now. You need two datatable and then you references columns/row pairs that contain the stuff you're looking for. Thanks. – David W Jun 29 '12 at 06:53

score 0 · Answer 3 · answered Jun 29 '12 at 06:14

0

Why don't you just use:

dataTable.Fill();

Why are you manually loading it one row at a time?

answered Jun 29 '12 at 06:14

JK.

21,477
35
135
214

score 0 · Answer 4 · answered Nov 06 '15 at 09:48

0

Please check what you are inserting into the column row which gas GUID data type. I got the same error and found that while inserting records after reading records from csv file, there were couple of empty spaces in csv file.

answered Nov 06 '15 at 09:48

kishor

1

Strange error when adding row to datatable

4 Answers4