SqlDataAdapter.Update Error

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Benniit
    New Member
    • May 2008
    • 54
    #1

    SqlDataAdapter.Update Error

    Am using Vb.NET 2008 and SQL 2008. I have a problem, when the execution reaches daAdapter.Updat e(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("Lan ds", "Connection ", "DataL1")
    strPassword = GetSetting("Lan ds", "Connection ", "DataL2")
    strServerName = GetSetting("Lan ds", "Connection ", "DataL3")
    strInitialCatal og = GetSetting("Lan ds", "Connection ", "DataL4")
    DataL1 = strUsername & strPassword & strServerName & strInitialCatal og
    dTable = New DataSet
    dtTable = New DataTable
    SQLCom = New SqlCommand
    SQLCom1 = New SqlCommand

    SQLCon = New SqlConnection
    SQLCon.Connecti onString = DataL1
    Try
    SqlQuery = "Insert FileRegister values ('@Prefix','@Fi le','@Plot','@B lock','@Street' ,'@Section','@L ocation','@town ','@District',' @Purpose','@App licant','@Addre ss','@Remarks', '@Title','@Size ','@Term')"
    SqlQuery1 = "select * from FileRegister"
    daAdapter = New SqlDataAdapter
    SQLCom.Connecti on = SQLCon
    SQLCom1.Connect ion = SQLCon
    SQLCom.CommandT ype = 1
    SQLCom1.Command Type = 1

    SQLCom.CommandT ext = SqlQuery
    SQLCom1.Command Text = SqlQuery1
    daAdapter.Inser tCommand = SQLCom
    daAdapter.Selec tCommand = SQLCom1
    SQLCon.Open()
    daAdapter.Fill( dTable, "FileRegist er")
    dtTable = dTable.Tables(" FileRegister")


    Dim r As DataRow = dtTable.NewRow
    r(0) = Me.cboPrefix.Te xt.Trim()
    r(1) = Convert.ToDoubl e(Me.txtFileNo. Text.Trim)
    r(2) = Me.txtPlotNo.Te xt.Trim
    r(3) = Me.txtBlockNo.T ext.Trim
    r(4) = Me.txtStreetNam e.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.T ext.Trim
    r(10) = Me.txtApplicant .Text.Trim
    r(11) = Me.txtAddress.T ext.Trim
    r(12) = Me.txtRemarks.T ext.Trim
    r(13) = Me.txtTitleNo.T ext.Trim
    r(14) = Me.txtSize.Text .Trim
    r(15) = Me.txtTerm.Text .Trim

    dtTable.Rows.Ad d(r)
    daAdapter.Inser tCommand.Parame ters.Add("@Pref ix", SqlDbType.Char, 3, "Prefix")
    daAdapter.Inser tCommand.Parame ters.Add("@File ", SqlDbType.VarCh ar, 30, "file_No")
    daAdapter.Inser tCommand.Parame ters.Add("@Plot ", SqlDbType.VarCh ar, 120, "plot_no")
    daAdapter.Inser tCommand.Parame ters.Add("@Bloc k", SqlDbType.VarCh ar, 10, "Block_No")
    daAdapter.Inser tCommand.Parame ters.Add("@Stre et", SqlDbType.VarCh ar, 60, "Street_Nam e")
    daAdapter.Inser tCommand.Parame ters.Add("@Sect ion", SqlDbType.VarCh ar, 30, "Section_No ")
    daAdapter.Inser tCommand.Parame ters.Add("@Loca tion", SqlDbType.VarCh ar, 40, "Location")
    daAdapter.Inser tCommand.Parame ters.Add("@Town ", SqlDbType.VarCh ar, 150, "Town")
    daAdapter.Inser tCommand.Parame ters.Add("@Dist rict", SqlDbType.VarCh ar, 150, "District")
    daAdapter.Inser tCommand.Parame ters.Add("@Purp ose", SqlDbType.VarCh ar, 150, "Purpose")
    daAdapter.Inser tCommand.Parame ters.Add("@Appl icant", SqlDbType.VarCh ar, 50, "Applicant" )
    daAdapter.Inser tCommand.Parame ters.Add("@Addr ess", SqlDbType.VarCh ar, 160, "Address")
    daAdapter.Inser tCommand.Parame ters.Add("@Rema rks", SqlDbType.VarCh ar, 600, "Remarks")
    daAdapter.Inser tCommand.Parame ters.Add("@Titl e", SqlDbType.VarCh ar, 30, "Title")
    daAdapter.Inser tCommand.Parame ters.Add("@Size ", SqlDbType.VarCh ar, 50, "Size")
    daAdapter.Inser tCommand.Parame ters.Add("@Term ", SqlDbType.VarCh ar, 15, "Term")


    daAdapter.Updat e(dtTable)
    Catch
    MsgBox Err.Description
    End Try

    End Sub
    Tags: None
    • MrMancunian
      Recognized Expert Contributor
      • Jul 2008
      • 569
      #2
      I think you're trying to insert something that is longer than the maximum field size in your database. Did you check the length of what you're inserting?

      Steven

        Comment

        • Plater
          Recognized Expert Expert
          • Apr 2007
          • 7872
          #3
          if one of your database fields is say a "varchar(50 )", then its max length is 50, if you try to pass in more then 50 characters, you would get that error i believe

            Comment

            • iam_clint
              Recognized Expert Top Contributor
              • Jul 2006
              • 1207
              #4
              Exactly right

              A text with a size of 50 chars, when you insert something into that column with say 60 chars it will truncate the last 10 chars off the string you tried to put in it.

                Comment

                • Benniit
                  New Member
                  • May 2008
                  • 54
                  #5
                  Correct fields don't get updated

                  Originally posted by Plater
                  if one of your database fields is say a "varchar(50 )", then its max length is 50, if you try to pass in more then 50 characters, you would get that error i believe
                  In fact, values from the text boxes are not updated in the database, but rather the insert statement as follows @Prefix','@File ','@Plot and so on. What could possibly be the problem?
                  Ben
                  [code=vbnet]
                  SqlQuery = "Insert FileRegister values ('@Prefix','@Fi le','@Plot','@B lock','@Street' ,'@Se ction','@Locati on','@town','@D istrict','@Purp ose', '@Applicant','@ Address','@Rema rks','@Title',' @Size ','@Term')"






                  Am using Vb.NET 2008 and SQL 2008. I have a problem, when the execution reaches daAdapter.Updat e(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("Lan ds", "Connection ", "DataL1")
                  strPassword = GetSetting("Lan ds", "Connection ", "DataL2")
                  strServerName = GetSetting("Lan ds", "Connection ", "DataL3")
                  strInitialCatal og = GetSetting("Lan ds", "Connection ", "DataL4")
                  DataL1 = strUsername & strPassword & strServerName & strInitialCatal og
                  dTable = New DataSet
                  dtTable = New DataTable
                  SQLCom = New SqlCommand
                  SQLCom1 = New SqlCommand

                  SQLCon = New SqlConnection
                  SQLCon.Connecti onString = DataL1
                  Try
                  SqlQuery = "Insert FileRegister values ('@Prefix','@Fi le','@Plot','@B lock','@Street' ,'@Se ction','@Locati on','@town','@D istrict','@Purp ose', '@Applicant','@ Address','@Rema rks','@Title',' @Size ','@Term')"
                  SqlQuery1 = "select * from FileRegister"
                  daAdapter = New SqlDataAdapter
                  SQLCom.Connecti on = SQLCon
                  SQLCom1.Connect ion = SQLCon
                  SQLCom.CommandT ype = 1
                  SQLCom1.Command Type = 1

                  SQLCom.CommandT ext = SqlQuery
                  SQLCom1.Command Text = SqlQuery1
                  daAdapter.Inser tCommand = SQLCom
                  daAdapter.Selec tCommand = SQLCom1
                  SQLCon.Open()
                  daAdapter.Fill( dTable, "FileRegist er")
                  dtTable = dTable.Tables(" FileRegister")


                  Dim r As DataRow = dtTable.NewRow
                  r(0) = Me.cboPrefix.Te xt.Trim()
                  r(1) = Convert.ToDoubl e(Me.txtFileNo. Text.Trim)
                  r(2) = Me.txtPlotNo.Te xt.Trim
                  r(3) = Me.txtBlockNo.T ext.Trim
                  r(4) = Me.txtStreetNam e.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.T ext.Trim
                  r(10) = Me.txtApplicant .Text.Trim
                  r(11) = Me.txtAddress.T ext.Trim
                  r(12) = Me.txtRemarks.T ext.Trim
                  r(13) = Me.txtTitleNo.T ext.Trim
                  r(14) = Me.txtSize.Text .Trim
                  r(15) = Me.txtTerm.Text .Trim

                  dtTable.Rows.Ad d(r)
                  daAdapter.Inser tCommand.Parame ters.Add("@Pref ix", SqlDbType.Char, 3, "Prefix")
                  daAdapter.Inser tCommand.Parame ters.Add("@File ", SqlDbType.VarCh ar, 30, "file_No")
                  daAdapter.Inser tCommand.Parame ters.Add("@Plot ", SqlDbType.VarCh ar, 120, "plot_no")
                  daAdapter.Inser tCommand.Parame ters.Add("@Bloc k", SqlDbType.VarCh ar, 10, "Block_No")
                  daAdapter.Inser tCommand.Parame ters.Add("@Stre et", SqlDbType.VarCh ar, 60, "Street_Nam e")
                  daAdapter.Inser tCommand.Parame ters.Add("@Sect ion", SqlDbType.VarCh ar, 30, "Section_No ")
                  daAdapter.Inser tCommand.Parame ters.Add("@Loca tion" , SqlDbType.VarCh ar, 40, "Location")
                  daAdapter.Inser tCommand.Parame ters.Add("@Town ", SqlDbType.VarCh ar, 150, "Town")
                  daAdapter.Inser tCommand.Parame ters.Add("@Dist rict" , SqlDbType.VarCh ar, 150, "District")
                  daAdapter.Inser tCommand.Parame ters.Add("@Purp ose", SqlDbType.VarCh ar, 150, "Purpose")
                  daAdapter.Inser tCommand.Parame ters.Add("@Appl icant ", SqlDbType.VarCh ar, 50, "Applicant" )
                  daAdapter.Inser tCommand.Parame ters.Add("@Addr ess", SqlDbType.VarCh ar, 160, "Address")
                  daAdapter.Inser tCommand.Parame ters.Add("@Rema rks", SqlDbType.VarCh ar, 600, "Remarks")
                  daAdapter.Inser tCommand.Parame ters.Add("@Titl e", SqlDbType.VarCh ar, 30, "Title")
                  daAdapter.Inser tCommand.Parame ters.Add("@Size ", SqlDbType.VarCh ar, 50, "Size")
                  daAdapter.Inser tCommand.Parame ters.Add("@Term ", SqlDbType.VarCh ar, 15, "Term")


                  daAdapter.Updat e(dtTable)
                  Catch
                  MsgBox Err.Description
                  End Try

                  End Sub
                  [/code]
                  Reply

                    Comment

                    • Plater
                      Recognized Expert Expert
                      • Apr 2007
                      • 7872
                      #6
                      The same thing we just said?
                      For example, this line:
                      daAdapter.Inser tCommand.Parame ters.Add("@Loca tion" , SqlDbType.VarCh ar, 40, "Location")

                      If the value that gets added to that parameter is longer then 40 characters (or in the database the column is actually LESS then 40 characters) you could see that error.

                        Comment

                        • Benniit
                          New Member
                          • May 2008
                          • 54
                          #7
                          Field size error has now been solved.
                          But In fact, values from the text boxes are not updated in the database, but rather the insert statement as follows @Prefix','@File ','@Plot get saved.
                          Ben
                          [code=vbnet]
                          SqlQuery = "Insert FileRegister values ('@Prefix','@Fi le','@Plot','@B lock','@Street' ,'@Se ction','@Locati on','@town','@D istrict','@Purp ose', '@Applicant','@ Address','@Rema rks','@Title',' @Size ','@Term')"


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

                          strUsername = GetSetting("Lan ds", "Connection ", "DataL1")
                          strPassword = GetSetting("Lan ds", "Connection ", "DataL2")
                          strServerName = GetSetting("Lan ds", "Connection ", "DataL3")
                          strInitialCatal og = GetSetting("Lan ds", "Connection ", "DataL4")
                          DataL1 = strUsername & strPassword & strServerName & strInitialCatal og
                          dTable = New DataSet
                          dtTable = New DataTable
                          SQLCom = New SqlCommand
                          SQLCom1 = New SqlCommand

                          SQLCon = New SqlConnection
                          SQLCon.Connecti onString = DataL1
                          Try
                          SqlQuery = "Insert FileRegister values ('@Prefix','@Fi le','@Plot','@B lock','@Street' ,'@Se ction','@Locati on','@town','@D istrict','@Purp ose', '@Applicant','@ Address','@Rema rks','@Title',' @Size ','@Term')"
                          SqlQuery1 = "select * from FileRegister"
                          daAdapter = New SqlDataAdapter
                          SQLCom.Connecti on = SQLCon
                          SQLCom1.Connect ion = SQLCon
                          SQLCom.CommandT ype = 1
                          SQLCom1.Command Type = 1

                          SQLCom.CommandT ext = SqlQuery
                          SQLCom1.Command Text = SqlQuery1
                          daAdapter.Inser tCommand = SQLCom
                          daAdapter.Selec tCommand = SQLCom1
                          SQLCon.Open()
                          daAdapter.Fill( dTable, "FileRegist er")
                          dtTable = dTable.Tables(" FileRegister")


                          Dim r As DataRow = dtTable.NewRow
                          r(0) = Me.cboPrefix.Te xt.Trim()
                          r(1) = Convert.ToDoubl e(Me.txtFileNo. Text.Trim)
                          r(2) = Me.txtPlotNo.Te xt.Trim
                          r(3) = Me.txtBlockNo.T ext.Trim
                          r(4) = Me.txtStreetNam e.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.T ext.Trim
                          r(10) = Me.txtApplicant .Text.Trim
                          r(11) = Me.txtAddress.T ext.Trim
                          r(12) = Me.txtRemarks.T ext.Trim
                          r(13) = Me.txtTitleNo.T ext.Trim
                          r(14) = Me.txtSize.Text .Trim
                          r(15) = Me.txtTerm.Text .Trim

                          dtTable.Rows.Ad d(r)
                          daAdapter.Inser tCommand.Parame ters.Add("@Pref ix", SqlDbType.Char, 3, "Prefix")
                          daAdapter.Inser tCommand.Parame ters.Add("@File ", SqlDbType.VarCh ar, 30, "file_No")
                          daAdapter.Inser tCommand.Parame ters.Add("@Plot ", SqlDbType.VarCh ar, 120, "plot_no")
                          daAdapter.Inser tCommand.Parame ters.Add("@Bloc k", SqlDbType.VarCh ar, 10, "Block_No")
                          daAdapter.Inser tCommand.Parame ters.Add("@Stre et", SqlDbType.VarCh ar, 60, "Street_Nam e")
                          daAdapter.Inser tCommand.Parame ters.Add("@Sect ion", SqlDbType.VarCh ar, 30, "Section_No ")
                          daAdapter.Inser tCommand.Parame ters.Add("@Loca tion" , SqlDbType.VarCh ar, 40, "Location")
                          daAdapter.Inser tCommand.Parame ters.Add("@Town ", SqlDbType.VarCh ar, 150, "Town")
                          daAdapter.Inser tCommand.Parame ters.Add("@Dist rict" , SqlDbType.VarCh ar, 150, "District")
                          daAdapter.Inser tCommand.Parame ters.Add("@Purp ose", SqlDbType.VarCh ar, 150, "Purpose")
                          daAdapter.Inser tCommand.Parame ters.Add("@Appl icant ", SqlDbType.VarCh ar, 50, "Applicant" )
                          daAdapter.Inser tCommand.Parame ters.Add("@Addr ess", SqlDbType.VarCh ar, 160, "Address")
                          daAdapter.Inser tCommand.Parame ters.Add("@Rema rks", SqlDbType.VarCh ar, 600, "Remarks")
                          daAdapter.Inser tCommand.Parame ters.Add("@Titl e", SqlDbType.VarCh ar, 30, "Title")
                          daAdapter.Inser tCommand.Parame ters.Add("@Size ", SqlDbType.VarCh ar, 50, "Size")
                          daAdapter.Inser tCommand.Parame ters.Add("@Term ", SqlDbType.VarCh ar, 15, "Term")


                          daAdapter.Updat e(dtTable)
                          Catch
                          MsgBox Err.Description
                          End Try

                          End Sub
                          [/code]

                            Comment

                            • Plater
                              Recognized Expert Expert
                              • Apr 2007
                              • 7872
                              #8
                              Ooo sorry I must have gotten the replies mixed up.

                              Ok I have found that when you do things like this:
                              r(0) = Me.cboPrefix.Te xt.Trim()
                              The values don't actually make their way into the correct named columns.
                              You would need to something like:
                              r("SomeColumnNa me") = Me.cboPrefix.Te xt.Trim()

                              To get the values in there

                                Comment

                                • Benniit
                                  New Member
                                  • May 2008
                                  • 54
                                  #9
                                  Thanx

                                  Please, I would to tell u that it worked!

                                    Comment

                                    Previous template Next
                                    Working...