locked
vb 2008 error: String or binary data would be truncated. The statement has been terminated.

    Question

  • This gets data from Excel and puts it into a table.  The first 2 rows of data are 
    placed into the table.  All the fields columns are the same type and nothing is 
    larger than it should be     name is varchar(30)     team is varchar(3)   pos is varchar(2)
    rest are int or double. I place an i in front of integers s in front of string and so on.
    The 3rd row crashes with the following message at Command.ExecuteNonQuery()
    String or binary data would be truncated.  The statement has been terminated.
    I can't find the solution on the internet.  If I can get this fixed, it would make 
    entering satas of the internet easy and also the plays and game stat enter
    easier. Can anyone.  Also I checked and the 3rd row data, is in the variables.
    JereTheBear
    Option Strict Off
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Public Class Form1
      Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=C:\ExcelData1.xls;" & _
                     "Extended Properties=""Excel 8.0;IMEX=1;HDR=NO"""
      Private m_sAction As String
      Dim s_team, s_Pos, s_Name As String
      Dim d_ave, d_P_era, d_p_ip, d_fldg As Double
      Dim i_Position, i_gm, i_pa, i_ab, i_r, i_h, i_db As Integer
      Dim i_tp, i_hr, i_rbi, i_bb, i_so, i_sb, i_cs, i_gdp, i_hbp As Integer
      Dim i_sh, i_sf, i_ibb, i_p_w, i_P_l, i_p_gs, i_p_sv, i_p_r As Integer
      Dim i_p_er, i_p_bb, i_p_so, i_p_go, i_p_wp, i_f_tc As Integer
      Dim i_f_po, i_f_a, i_f_e As Integer
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As & _
                         System. EventArgs) Handles Button1.Click
        Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
        conn1.Open()
        Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From & _
                                                                   [EmployeeData$]", conn1)
        Dim rdr As OleDbDataReader = cmd1.ExecuteReader
        Debug.WriteLine(vbCrLf & "EmployeeData" & vbCrLf & "=============")
        Dim Count As Integer = 1
        Do While rdr.Read()
          Try
            If Count < 26 Then
              If rdr.GetString(0) <> "Team" Then
                s_team = rdr.GetString(0)
                s_Pos = rdr.GetString(1)
                i_Position = rdr.GetString(2)
                s_Name = rdr.GetString(3)
                i_gm = rdr.GetString(4)
                i_pa = rdr.GetString(5)
                i_ab = rdr.GetString(6)
                i_r = rdr.GetString(7)
                i_h = rdr.GetString(8)
                i_db = rdr.GetString(9)
                i_tp = rdr.GetString(10)
                i_hr = rdr.GetString(11)
                i_rbi = rdr.GetString(12)
                i_bb = rdr.GetString(13)
                i_so = rdr.GetString(14)
                i_sb = rdr.GetString(15)
                i_cs = rdr.GetString(16)
                d_ave = rdr.GetString(17)
                i_gdp = rdr.GetString(18)
                i_hbp = rdr.GetString(19)
                i_sh = rdr.GetString(20)
                i_sf = rdr.GetString(21)
                i_ibb = rdr.GetString(22)
                i_p_w = rdr.GetString(23)
                i_P_l = rdr.GetString(24)
                d_P_era = rdr.GetString(25)
                i_p_gs = rdr.GetString(26)
                i_p_sv = rdr.GetString(27)
                d_p_ip = rdr.GetString(28)
                i_p_r = rdr.GetString(29)
                i_p_er = rdr.GetString(30)
                i_p_bb = rdr.GetString(31)
                i_p_so = rdr.GetString(32)
                i_p_go = rdr.GetString(33)
                i_p_wp = rdr.GetString(34)
                i_f_tc = rdr.GetString(35)
                i_f_po = rdr.GetString(36)
                i_f_a = rdr.GetString(37)
                i_f_e = rdr.GetString(38)
                d_fldg = rdr.GetString(39)
                EnterDataIntoDB()
                Count = Count + 1
              End If
            Else
              Exit Sub
            End If
          Catch ex As OleDbException
            MessageBox.Show(ex.Message)
          End Try
        Loop
        rdr.Close()
        conn1.Close()
      End Sub
      Private Sub EnterDataIntoDB()
        Using Connect As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS; & _
    AttachDbFilename  =C:\Users\JERE\Documents\Bases Loaded Baseball\Bigger Leaguer  & _
    Baseballer\Baseball2012.mdf;Integrated  Security=True;Connect Timeout=30;User Instance= & _
    True")
          Dim strPlayers As String = "INSERT INTO EntirePlayerList( " & _
                 "Team, Pos, Position, Name, G, PA, " & _
                  "AB, R, H, DB, TP, HR, " & _
                  "RBI, BB, SO, SB, CS, BA, " & _
                  "GDP, HBP, SH, SF, IBB, PW, " & _
                  "PL, PERA, PGS, PSV, PIP, PR, " & _
                  "PER, PBB, PK, PGO, PWP, FTC, " & _
                  "FPO, FA, FE, FFLDG) " & _
         "VALUES('" & s_team & "', '" & _
          s_Pos & "', '" & i_Position & "', '" & _
          s_Name & "', '" & i_gm & "', '" & _
          i_pa & "', '" & i_ab & "', '" & _
          i_r & "', '" & i_h & " ', '" & _
          i_db & "', '" & i_tp & "', '" & _
          i_hr & "', '" & i_rbi & "', '" & _
          i_bb & "', '" & i_so & "', '" & _
          i_sb & "', '" & i_cs & "', '" & _
          d_ave & "', '" & i_gdp & "', '" & _
          i_hbp & "', '" & i_sh & "', '" & _
          i_sf & "', '" & i_ibb & "', '" & _
          i_p_w & "', '" & i_P_l & "', '" & _
          d_P_era & "', '" & i_p_gs & "', '" & _
          i_p_sv & "', '" & d_p_ip & "', '" & _
          i_p_r & "', '" & i_p_er & "', '" & _
          i_p_bb & "', '" & i_p_so & "', '" & _
          i_p_go & "', '" & i_p_wp & "', '" & _
          i_f_tc & "', '" & i_f_po & "', '" & _
          i_f_a & "', '" & i_f_e & "', '" & _
          d_fldg & "');"
          Dim Command As SqlCommand = New SqlCommand(strPlayers, Connect)
          Connect.Open()
          Command.ExecuteNonQuery()
          Connect.Close()
        End Using
      End Sub
    End Class


    Jere Small

    Thursday, March 08, 2012 10:39 PM

Answers

  • Sorry guys, I found it. One varchar wasn't long enough.  After several hours of work.

    JereTheBear


    Jere Small

    • Marked as answer by JereTheBear Thursday, March 08, 2012 10:44 PM
    Thursday, March 08, 2012 10:44 PM

All replies

  • Sorry guys, I found it. One varchar wasn't long enough.  After several hours of work.

    JereTheBear


    Jere Small

    • Marked as answer by JereTheBear Thursday, March 08, 2012 10:44 PM
    Thursday, March 08, 2012 10:44 PM
  • Hi JereTheBear,

    Welcome to the MSDN forum.

    I’m glad to hear that you found a good idea to resolve your question. Thank you for your sharing the solution with us. It will be very beneficial for other community members who have similar questions.

    Have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Monday, March 12, 2012 8:17 AM