knowing if a string will be truncated when updating database

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 d开发者_开发知识库ata 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

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.

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'

What you need is the column metadata.

MSDN: SqlConnection.GetSchema Method

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)