String or binary data would be truncated VB.Net application

Question

I'm developping an application using VB.Net and this is the main form of my application :

https://i.sstatic.net/a4KfQ.png

I want when I click on the button Ajouter to add all informations from controls into a table in my database.

this is the table structure :

Create table Etudiant (
   num_insc varchar(20) primary key,
   CIN varchar(8),
   sexe varchar(1),
   nom_prenom varchar(30),
   date_naiss date,
   adresse varchar(150),
   Code_niveau varchar(5)
)

and this is the code I tried :

Dim ds As New DataSet
Dim row As DataRow
Dim builder As SqlCommandBuilder
Dim adapter As SqlDataAdapter



Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    code_niveau.Items.Add("BTS1")
    code_niveau.Items.Add("BTS2")
    adapter = New SqlDataAdapter("SELECT * FROM Etudiant", cn)
    adapter.Fill(ds, "Etudiant")

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    row = ds.Tables("Etudiant").NewRow
    row(0) = num_insc.Text
    row(1) = CIN.Text
    If (sexe_f.Checked) Then
        row(2) = sexe_f.Name
    Else
        row(2) = sexe_m.Name
    End If
    row(3) = nom_prenom.Text
    row(4) = date_naiss.Value
    row(5) = adresse.Text
    row(6) = code_niveau.Text
    ds.Tables("Etudiant").Rows.Add(row)
    builder = New SqlCommandBuilder(adapter)
    adapter.InsertCommand = builder.GetInsertCommand
    adapter.Update(ds, "Etudiant")
End Sub

But when I click on the button Ajouter I get this error message :

String or binary data would be truncated.

The statement has been terminated

.

Ken White · Accepted Answer · 2013-04-10 17:14:07Z

1

This is caused by trying to write too much data to one of your table columns.

Set a breakpoint at row(0) = nun_insc.Text, and single-step through your code from there to figure out which column it is and why there's too much text being written, and then figure out how you need to fix it. (The solution will be to either shorten the text you're trying to write or to widen the column in the table to store more data.)

To make it more clear, step through each single line of the code as it runs. For each line:

Look at the column it's going to write to when that statement is run, and see how many characters it will allow.
Look at the actual value that's about to be written to that column, and see if it's equal to or less than the number you got in the first step above.

When you figure out which column it is, you can decide whether you need to limit the amount of text that the user can input, or make the column bigger to hold more characters, based on the needs of your application.

edited Apr 10, 2013 at 17:14

answered Apr 10, 2013 at 16:56

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Spoon Yukinatte Over a year ago

I tried that, but I cant figure out where is the problem, the error message that I got is in this line : adapter.Update(ds, "Etudiant")

Ken White Over a year ago

You have to look at the data that's being assigned to each row as the code runs, and see which one of them is too large for the column. We can't do that for you, because we don't know what's being assigned to the column in your code. (I can't see what adresse.Text is when your code runs; only you can.)

Spoon Yukinatte Over a year ago

Okey thanks for your help, I just increased the length of data to my columns in sql server and it worked

Steve Over a year ago

Not a wise move... Better set the MaxLength property of your textboxes to the max length of your database varchar fields.

Ken White Over a year ago

@Steve: You can't say that's the better solution, because we don't know the needs of the application. If the issue was the nom_prenom column, for instance, perhaps there is a legitimate need for more than 30 characters of text. (See the last paragraph of my answer, where I said it first.) :-)

Collectives™ on Stack Overflow

String or binary data would be truncated VB.Net application

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related