2

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.

Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • 2
    What are `dtExternal` and `dtEmployed`? What types do they have for the `emp_num` columns? – Jon Skeet Jan 05 '12 at 07:20
  • `DataTable dtExternal = Instructor.GetAllAssignedTeachersForImport(int.Parse(ddl_batch.SelectedValue), int.Parse(Session["empnum"].ToString())); DataTable dtEmployed = Instructor.GetAllNominatedTeachersForImport(int.Parse(ddl_department.SelectedValue), int.Parse(ddl_study.SelectedValue));` – Anyname Donotcare Jan 05 '12 at 07:23
  • 1
    And what is the type of `emp_num` in each of the resulting data tables? (Just showing us that they're coming from method calls doesn't really give us much more information.) – Jon Skeet Jan 05 '12 at 07:27

2 Answers2

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);
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 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 . – Anyname Donotcare Jan 05 '12 at 07:29
  • There is no magic you can apply. You can't "fix it programmatically" such that the value 103930 fits in a 16-bit integer. – Mr Lister Jan 05 '12 at 07:34
  • Can i create my own datatable programatically and put the datatype to int32 instead ? – Anyname Donotcare Jan 05 '12 at 07:36
  • @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`. – Jon Skeet Jan 05 '12 at 07:41
  • i just call`dt.Load(command.ExecuteReader());` to return datatable – Anyname Donotcare Jan 05 '12 at 07:45
  • 1
    @just_name: But that suggests you've already created the `DataTable` - so give it the schema you want before you load it. – Jon Skeet Jan 05 '12 at 07:46
  • `dtInstTotal.CopyToDataTable(tableWithCorrectSchema, LoadOption.PreserveChanges);` return type is void !!! – Anyname Donotcare Jan 05 '12 at 07:52
  • I get it sorry .`tableWithCorrectSchema` is the result . – Anyname Donotcare Jan 05 '12 at 07:57
1

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

Nighil
  • 4,099
  • 7
  • 30
  • 56