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

sqlvb.net

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.

Best Answer

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?

Related Topic