CODEWITHSUNDEEP
sqlvb.net
Mike Jhy
Mike Jhy

Reputation: 31

String or binary data would be truncated. The statement has been terminated

I always got an error when adding a new data. the error says

String or binary data would be truncated. The statement has been terminated

As I've looked back on my backend or code. It looks like there's a conflict adding a TWO LABEL DATA in one column because I would like to join the (Year)-(StudentNumber)

Here's the code of my INSERT INTO Statement

  INSERT INTO
     [Student_Information] (StudentID, LastName, FirstName, MiddleName, Gender, 
                            ContactNumber, Citizenship, Religion, Birthday, Address) 
  VALUES 
     ( '" & lbl_cyear.Text - studid.Text & "','" + txt_lname.Text + "', '" + txt_fname.Text + "', '" + txt_mname.Text + "', '" + DDGender.Text + "', '" & txt_cnumber.Text & "', '" & txt_citizenship.Text & "' , '" + txt_religion.Text + "' , '" & txt_bday.Text & "', '" & txt_address.Text & "' )"

and here's the code how I generate the Year and the Student Number

Sub SNYear()
    Dim test As Date

    test = Convert.ToDateTime(Today)
    lbl_cyear.Text = test.Year
End Sub
Sub SNGenerate()
    'displaying Studentid
    Dim SN As Integer ' Student Number
    Dim SID As String 'Student ID Num as String

    Dim rdr As SqlDataReader

    cmd1.Connection = cn
    cmd1.Connection.Open()
    cmd1.CommandText = "Select Max (StudentID) as expr1 from [Student_Information]"
    rdr = cmd1.ExecuteReader

    If rdr.HasRows = True Then
        rdr.Read()
    End If

    If rdr.Item(0).ToString = Nothing Then
        SN = rdr.Item(0) + 1
        SID = Format(SN, "0000")
    ElseIf rdr.Item(0).ToString = 0 Then
        SN = rdr.Item(0) + 1
        SID = Format(SN, "0000")

    Else
        SN = rdr.Item(0) + 1
        SID = Format(SN, "0000")
    End If


    studid.Text = SID

    cmd1.Connection.Close()

End Sub

Can someone help me with the code? How to join 2 data in different label text and save it to one column in my table.

Upvotes: 3

Views: 27564

Answers (2)

Origin
Origin

Reputation: 2023

Y@atornblad has some very valid points about re-structuring your code. However, the specific problem you are asking about is likely because you are trying to insert data into a column that is longer than the column can accept.

E.g. - you are trying to insert "Lorem Ipsum" into a column that has a maximum length of 5 characters.

Edit You need to take another look at how your table is defined and make sure it is appropriate for the data you are storing. Also - make sure the data you are trying to store is in the correct format that the table was designed for. Don't assume it's in the format you want, actually step through the program in debug mode and look at what the variable is before it gets sent to the database.

Upvotes: 3

Anders Marzi Tornblad
Anders Marzi Tornblad

Reputation: 19305

Woah! Never ever write sql queries like that. It's subject to dangerous SQL injection, and code like that is actually used as worst-case scenarios in SQL injection lectures everywhere!

That being said, the error message String or binary data would be truncated. The statement has been terminated. actually spells out what is wrong. You are trying to insert too much data into a field that has a specific dimension.

You are trying to insert the following expression into the StudentID field:

lbl_cyear.Text - studid.Text

I'm not even sure what you want to do there. Since Visual Basic is loosely typed by default, It will probably handle the lbl_cyear.Text as a number and try to subtract the studid.Text (as a number) from it. You probably mean something like this:

lbl_cyear.Text & "-" & studid.Text

It seems you are trying to use the StudentID column for two different types of information. Don't do that. Decide on one format for the student ids and dimension the column for it.

Is this a homework assignment?

Upvotes: 8

Related Questions