Value was either too large or too small for an Int16 ,when copy to data table

Question

Q:

The following code:

        var dtInstTotal = dtExternal.AsEnumerable()
                    .Union(dtEmployed.AsEnumerable())
                    .OrderBy(d => d.Field<string>("emp_name"));

        dtInst = dtInstTotal.CopyToDataTable();//exception

throw an exception:

Value was either too large or too small for an Int16.Couldn't store <103930> in emp_num Column. Expected type is Int16. ---> System.OverflowException: Value was either too large or too small for an Int16.

Solution 1

I suspect that dtExternal has a short type for emp_num, whereas dtEmployed has some other type (int, long or maybe just ushort) - or maybe vice versa. CopyToDataTable just uses the types from the first table containing the first row it sees, and then it's having problems when it comes across a value for a column with the same name from a different table. From the docs:

The schema of the destination table is based on the schema of the first DataRow row in the source sequence. The table metadata is extracted from the DataRow metadata and the table values from the column values of the DataRow.

Basically: make sure your two original tables have the same schema.

EDIT: We don't know what your methods to populate the two original DataTables look like - but you may find that by creating the DataTable first, explicitly setting the type of emp_num, and then filling the table, that will be okay.

You could even leave your original methods alone, and build a new DataTable with the right schema, then call

dtInstTotal.CopyToDataTable(tableWithCorrectSchema, LoadOption.PreserveChanges);

Solution 2

i think the datatable structure in dtInst or dtInstTotal for emp_num is int16 change it to int32

Comments

[Anyname Donotcare]

yeah this right dtEmployed emp_num is small integer but the DB admin told me he can't change the data types .so please how to fix this programatically .

[Mr Lister]

There is no magic you can apply. You can't "fix it programmatically" such that the value 103930 fits in a 16-bit integer.

[Anyname Donotcare]

Can i create my own datatable programatically and put the datatype to int32 instead ?

[Jon Skeet]

@just_name: Well we still don't know the details of how your two methods are getting the data from the database. You've told us that one is using a simple query and one is using a stored procedure, but there's still the code within the methods to fetch that data which we're missing... you may be able to just change the method to explicitly specify the type of emp_num in the DataTable.

[Anyname Donotcare]

i just calldt.Load(command.ExecuteReader()); to return datatable

[Jon Skeet]

@just_name: But that suggests you've already created the DataTable - so give it the schema you want before you load it.

[Anyname Donotcare]

dtInstTotal.CopyToDataTable(tableWithCorrectSchema, LoadOption.PreserveChanges); return type is void !!!

[Anyname Donotcare]

I get it sorry .tableWithCorrectSchema is the result .