Error when trying to insert a value that exceeds field length

[bernardmanning]

Hi,

I've got a win form, which connects to sql server using a dataadapter / dataset / datatable and everything works great.

I've noticed that if I try to update a field on a table in sql server with a value that exceeds the field length I get this error ;

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

I realise that this is because I'm trying to insert a value that is say 30 characters in length into a field on a table that can only contain 20 characters.

My question is this ;

Whats the best way to prevent this from happening?

I know I can specify a 'maxlength' property on the text box control on the form, but I really don't want this piece of 'validation' to be at the UI level.

Is there someplace else to do this?

I'm using the command builder on the data adapter in order to generate the insert/ update and delete commands. eg

BindingContext(drawingtable).EndCurrentEdit()

dataadapter.DeleteCommand = commandbuilder.GetDeleteCommand()
dataadapter.UpdateCommand = commandbuilder.GetUpdateCommand()
dataadapter.InsertCommand = commandbuilder.GetInsertCommand()

Dim RowsUpdated As Integer = dataadapter.Update(ds, ds.Tables(0).ToString())
Return RowsUpdated

Any ideas / help would be gratefully received !!!

Thanks, Bernard

 

[chrissie1]

And are you using databinding?

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[bernardmanning]

Hi, Christiaan

Yes, I'm using the binding manager to bind my controls to the fields on the dataset eg;

this kind of thing.

Me.txtsurname.DataBindings.Add("text", employeestable, "Surname")
Me.txtforename.DataBindings.Add("text", employeestable, "ForeName")

 

[RiverGuy]

Depending on what your database is used for, I would reconsider the UI validation. I think most users would want to know what they are putting in your database. If they realized that they would only be able to key in 20 characters, they may wish to change their input. They may wish to abbreviate something. What happens if you truncated the last word, and it was a very import word to the user?

 

[bernardmanning]

Hi, thanks for the reply,

I was sort of wondering about placing this kind of thing in a business object and placing the validation in there..

I see what you're saying RiverGuy, but I'm trying to move alot of this kind of validation out of the UI, as I can't guarantee that the data will always originate from a user typing it into a win form.

Thanks

 

[RiverGuy]

Then use stored procedures for the Insert/Update commands. Allow the procedure to accept a longer string, but only insert the first 20 characters into your database.

But if you can't guarantee that the data won't come in through a Windows Form....the user will receive errors or exceptions anyways. If you opened up query analyser, and tried to insert a string that is too long, you will receive an error.

I think the best idea is to use the stored procedures to insert....without truncating, and to test for the exception to be thrown in the UI and act accordingly.

The second best plan IMO is to use the stored procedure to truncate the field--BUT--allow the user to change his or her entry. Its not fun typing for nothing, thinking something is saved when its not.

 

[markdenby]

Hi,

One disadvantage to inserting and testing for the exeception to be thrown is that it does require a round trip to the server and back.

It seems better to catch the condition in a biz type object inform the user without having to go to the server and back.,.

I think they are many different ways to 'skin this cat'...

thanks again, you've given me a couple of options...

Cheers, Mark