knowing if a string will be truncated when updating database

Question

I'm working on a software that takes a csv file and put the data in a sqlserver. i'm testing it with bad data now and when i make a data string to long (in a line) to be imported in the database i got the error : String or binary data would be truncated the statement has been terminate. that's normal and that's what i should expect. Now i wanna detecte those error before the update to the database. Is there any clever way to detecte this?

The way my software work is that i importe every line in a dataset then show the user the data that will be imported. Then he can click a button to do the actual update. i then do a dataAdapter.Update( Dataset, "something" ) to make the update to the database.

The probleme is that the error row terminate all the update and report the error. So i want to detect the error before i do the update to the server so the other rows will be inserted.

thanks

Answer 1

You will have to check the columns of each row. See if one exceeds the maximum specified in the database, and if yes, exclude it from being inserted.

A different solution would be to explicitly truncate the data and insert the truncated content, which could be done by using SubString.

Answer 2

The only way that I know of is to pre-check the information schema for the character limit:

Select
    Column_Name,
    Character_Maximum_Length
From
    Information_Schema.Columns
Where
    Table_Name = 'YourTableName'

Answer 3

What you need is the column metadata.

MSDN: SqlConnection.GetSchema Method

Answer 4

Or, if you have opened a recordset on your database, another solution would be to browse the field object to use its length to truncate the string. For example, with ADO recordset/VB code, you could have some code like this one:

myRecordset.fields(myField) = left(myString, myRecordset.fields(myField).DefinedSize)