Am using Vb.NET 2008 and SQL 2008. I have a problem, when the execution reaches daAdapter.Update(dtTable) then I receive this error ""String or binary data would be truncated.
The statement has been terminated"" Then the record fails to update. What could possibly be the error? Below is the complete code. Thanx in advance.

Dim SQLCon As SqlConnection
Dim SqlQuery, SqlQuery1 As String
Dim daAdapter As SqlDataAdapter
Dim SQLCom As SqlCommand
Dim SQLCom1 As SqlCommand

strUsername = GetSetting("Lands", "Connection", "DataL1")
strPassword = GetSetting("Lands", "Connection", "DataL2")
strServerName = GetSetting("Lands", "Connection", "DataL3")
strInitialCatalog = GetSetting("Lands", "Connection", "DataL4")
DataL1 = strUsername & strPassword & strServerName & strInitialCatalog
dTable = New DataSet
dtTable = New DataTable
SQLCom = New SqlCommand
SQLCom1 = New SqlCommand

SQLCon = New SqlConnection
SQLCon.ConnectionString = DataL1
SqlQuery = "Insert FileRegister values ('@Prefix','@File','@Plot','@Block','@Street','@Section','@Location','@town','@District','@Purpose','@Applicant','@Address','@Remarks','@Title','@Size','@Term')"
SqlQuery1 = "select * from FileRegister"
daAdapter = New SqlDataAdapter
SQLCom.Connection = SQLCon
SQLCom1.Connection = SQLCon
SQLCom.CommandType = 1
SQLCom1.CommandType = 1

SQLCom.CommandText = SqlQuery
SQLCom1.CommandText = SqlQuery1
daAdapter.InsertCommand = SQLCom
daAdapter.SelectCommand = SQLCom1
daAdapter.Fill(dTable, "FileRegister")
dtTable = dTable.Tables("FileRegister")

Dim r As DataRow = dtTable.NewRow
r(0) = Me.cboPrefix.Text.Trim()
r(1) = Convert.ToDouble(Me.txtFileNo.Text.Trim)
r(2) = Me.txtPlotNo.Text.Trim
r(3) = Me.txtBlockNo.Text.Trim
r(4) = Me.txtStreetName.Text.Trim
r(5) = Me.txtSectionNo.Text.Trim
r(6) = Me.txtLocation.Text.Trim
r(7) = Me.cboTown.Text.Trim
r(8) = Me.cboDistrict.Text.Trim
r(9) = Me.cboPurpose.Text.Trim
r(10) = Me.txtApplicant.Text.Trim
r(11) = Me.txtAddress.Text.Trim
r(12) = Me.txtRemarks.Text.Trim
r(13) = Me.txtTitleNo.Text.Trim
r(14) = Me.txtSize.Text.Trim
r(15) = Me.txtTerm.Text.Trim

daAdapter.InsertCommand.Parameters.Add("@Prefix", SqlDbType.Char, 3, "Prefix")
daAdapter.InsertCommand.Parameters.Add("@File", SqlDbType.VarChar, 30, "file_No")
daAdapter.InsertCommand.Parameters.Add("@Plot", SqlDbType.VarChar, 120, "plot_no")
daAdapter.InsertCommand.Parameters.Add("@Block", SqlDbType.VarChar, 10, "Block_No")
daAdapter.InsertCommand.Parameters.Add("@Street", SqlDbType.VarChar, 60, "Street_Name")
daAdapter.InsertCommand.Parameters.Add("@Section", SqlDbType.VarChar, 30, "Section_No")
daAdapter.InsertCommand.Parameters.Add("@Location", SqlDbType.VarChar, 40, "Location")
daAdapter.InsertCommand.Parameters.Add("@Town", SqlDbType.VarChar, 150, "Town")
daAdapter.InsertCommand.Parameters.Add("@District", SqlDbType.VarChar, 150, "District")
daAdapter.InsertCommand.Parameters.Add("@Purpose", SqlDbType.VarChar, 150, "Purpose")
daAdapter.InsertCommand.Parameters.Add("@Applicant", SqlDbType.VarChar, 50, "Applicant")
daAdapter.InsertCommand.Parameters.Add("@Address", SqlDbType.VarChar, 160, "Address")
daAdapter.InsertCommand.Parameters.Add("@Remarks", SqlDbType.VarChar, 600, "Remarks")
daAdapter.InsertCommand.Parameters.Add("@Title", SqlDbType.VarChar, 30, "Title")
daAdapter.InsertCommand.Parameters.Add("@Size", SqlDbType.VarChar, 50, "Size")
daAdapter.InsertCommand.Parameters.Add("@Term", SqlDbType.VarChar, 15, "Term")

MsgBox Err.Description
End Try

End Sub

I receive this error ""String or binary data would be truncated.
The statement has been terminated""

You have, for example, a field of type Varchar with size 100 and you're trying to insert a string value which length is longer than 100.

For text boxes you can set the Maxlength property to match the lengths in your DB. Over all you should validate user input and give a warning if the user is trying to enter data that does not match with your DB.

This article has been dead for over six months. Start a new discussion instead.