none
Conversion failed when converting the varchar value ' ' to data type int. RRS feed

  • Question

  • Hey I am trying to upload a .csv file into my data base and im geting this error: Conversion failed when converting the varchar value ' ' to data type int.

    Really don't know why or how I am geting this error.

    Here is my code:

     Dim WFSDINSTITUTION As String = ""
                Dim WFSDSTRING As New System.Data.SqlClient.SqlConnection
                WFSDSTRING.ConnectionString = "Data Source=LED-SQL;Initial Catalog=WorkforceSD_DEV;Integrated Security=True"
                WFSDSTRING.Open()
                Dim Institution As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\CSchexnaydre\Desktop\TEST SHEETS\Institution\INST_TEST2010.csv;HDR=YES;"
                Using INSTITUTIONCONNECTION As New System.Data.OleDb.OleDbConnection(Institution)
                    Dim INST_TABLE As New Data.DataTable()
                    INST_TABLE.Columns.Add("UNITID")
                    INST_TABLE.Columns.Add("INSTNM")
                    INST_TABLE.Columns.Add("ADDR")
                    INST_TABLE.Columns.Add("CITY")
                    INST_TABLE.Columns.Add("STABBR")
                    INST_TABLE.Columns.Add("ZIP")
                    INST_TABLE.Columns.Add("FIPS")
                    INST_TABLE.Columns.Add("OBEREG")
                    INST_TABLE.Columns.Add("CHFNM")
                    INST_TABLE.Columns.Add("CHFTITLE")
                    INST_TABLE.Columns.Add("GENTELE")
                    INST_TABLE.Columns.Add("ENI")
                    INST_TABLE.Columns.Add("OPEID")
                    INST_TABLE.Columns.Add("OPEFLAG")
                    INST_TABLE.Columns.Add("WEBADDR")
                    INST_TABLE.Columns.Add("ADMINURL")
                    INST_TABLE.Columns.Add("FAIDURL")
                    INST_TABLE.Columns.Add("APPLURL")
                    INST_TABLE.Columns.Add("SECTOR")
                    INST_TABLE.Columns.Add("ICLEVEL")
                    INST_TABLE.Columns.Add("CONTROL")
                    INST_TABLE.Columns.Add("HLOFFER")
                    INST_TABLE.Columns.Add("UGOFFER")
                    INST_TABLE.Columns.Add("GROFFER")
                    INST_TABLE.Columns.Add("HDEGOFR1")
                    INST_TABLE.Columns.Add("DEGGRANT")
                    INST_TABLE.Columns.Add("HBCU")
                    INST_TABLE.Columns.Add("HOSPITAL")
                    INST_TABLE.Columns.Add("MEDICAL")
                    INST_TABLE.Columns.Add("TRIBAL")
                    INST_TABLE.Columns.Add("LOCALE")
                    INST_TABLE.Columns.Add("OPENPUBL")
                    INST_TABLE.Columns.Add("ACT")
                    INST_TABLE.Columns.Add("NEWID")
                    INST_TABLE.Columns.Add("DEATHYR")
                    INST_TABLE.Columns.Add("CLOSEDAT")
                    INST_TABLE.Columns.Add("CYACTIVE")
                    INST_TABLE.Columns.Add("POSTSEC")
                    INST_TABLE.Columns.Add("PSEFLAG")
                    INST_TABLE.Columns.Add("PSET4FLG")
                    INST_TABLE.Columns.Add("RPTMTH")
                    INST_TABLE.Columns.Add("IALIAS")
                    INST_TABLE.Columns.Add("INSTCAT")
                    INST_TABLE.Columns.Add("CCBASIC")
                    INST_TABLE.Columns.Add("CCIPUG")
                    INST_TABLE.Columns.Add("CCIPGRAD")
                    INST_TABLE.Columns.Add("CCUGPROF")
                    INST_TABLE.Columns.Add("CCENRPRF")
                    INST_TABLE.Columns.Add("CCSIZSET")
                    INST_TABLE.Columns.Add("CARNEGIE")
                    INST_TABLE.Columns.Add("TENURSYS")
                    INST_TABLE.Columns.Add("LANDGRNT")
                    INST_TABLE.Columns.Add("INSTSIZE")
                    INST_TABLE.Columns.Add("CBSA")
                    INST_TABLE.Columns.Add("CBSATYPE")
                    INST_TABLE.Columns.Add("CSA")
                    INST_TABLE.Columns.Add("NECTA")
                    INST_TABLE.Columns.Add("F1SYSTYP")
                    INST_TABLE.Columns.Add("F1SYSNAM")
                    INST_TABLE.Columns.Add("FAXTELE")
                    Dim INST_TEST2010 As New FileIO.TextFieldParser("C:\Documents and Settings\CSchexnaydre\Desktop\TEST SHEETS\Institution\INST_TEST2010.csv")
                    INST_TEST2010.Delimiters = New String() {","}
                    INST_TEST2010.HasFieldsEnclosedInQuotes = True
                    INST_TEST2010.TrimWhiteSpace = True
                    INST_TEST2010.ReadLine()
                    Do Until INST_TEST2010.EndOfData = True
                        INST_TABLE.Rows.Add(INST_TEST2010.ReadFields())
                    Loop
                    Dim i As Integer
                    Dim INSERT_INST_COMMAND = New System.Data.SqlClient.SqlCommand
                    INSERT_INST_COMMAND = WFSDSTRING.CreateCommand()
                    For Each row In INST_TABLE.Rows
                        INSERT_INST_COMMAND.CommandText = "INSERT INTO Institution_UPLOAD (Institution_ID, Institution_Name, Address_1, City, State_Code, Zip, FIPS, OBE_Region_ID, Chief_Admin_Name_ID, Chief_Admin_Title_ID, Institution_Phone, ENI, OPE_Number, OPEFLAG_ID, Institution_Web, Admission_Web, Financial_Web, Application_Web, Type_Of_Institution_ID, Level_Of_Institution_ID, HLOFFER_ID, UGOFFER_ID, GROFFER_ID, HDEGOFR1_ID, DEGGRANT_ID, Historic_Black_College_ID, Hospital_Available_ID, Medical_Degree_ID, Tribal_College_ID, Location_ID, Open_To_Public, ACT_ID, New_ID, Deleted_Year, Closed_Date, CYACTIVE_ID, POSTEC_ID, PSEFLAG_ID, PSET4FLG_ID, RPTMTH_ID, INSTCAT_ID, ALIAS, CCBASIC_ID, CCIPUG_ID, CCIPGRAD_ID, CCUGPROF_ID, CCENPRF_ID, CCSIZSET_ID, CARNEGIE_ID, TENURSYS_ID, Land_Grant_Institution_ID, Size_Of_Institution_ID, CBSA_ID, CBSATYPE_ID, CSA_ID, NECTA_ID, F1SYSTYP_ID, F1SYSNAM, Institution_Fax) VALUES (" _
                            & "'" & row("UNITID") & "'," _
                            & "'" & row("INSTNM") & "'," _
                            & "'" & row("ADDR") & "'," _
                            & "'" & row("CITY") & "'," _
                            & "'" & row("STABBR") & "'," _
                            & "'" & row("ZIP") & "'," _
                            & "'" & row("FIPS") & "'," _
                            & "'" & row("OBEREG") & "'," _
                            & "'" & row("CHFNM") & "'," _
                            & "'" & row("CHFTITLE") & "'," _
                            & "'" & row("GENTELE") & "'," _
                            & "'" & row("ENI") & "'," _
                            & "'" & row("OPEID") & "'," _
                            & "'" & row("OPEFLAG") & "'," _
                            & "'" & row("WEBADDR") & "'," _
                            & "'" & row("ADMINURL") & "'," _
                            & "'" & row("FAIDURL") & "'," _
                            & "'" & row("APPLURL") & "'," _
                            & "'" & row("ICLEVEL") & "'," _
                            & "'" & row("CONTROL") & "'," _
                            & "'" & row("HLOFFER") & "'," _
                            & "'" & row("UGOFFER") & "'," _
                            & "'" & row("GROFFER") & "'," _
                            & "'" & row("HDEGOFR1") & "'," _
                            & "'" & row("DEGGRANT") & "'," _
                            & "'" & row("HBCU") & "'," _
                            & "'" & row("HOSPITAL") & "'," _
                            & "'" & row("MEDICAL") & "'," _
                            & "'" & row("TRIBAL") & "'," _
                            & "'" & row("LOCALE") & "'," _
                            & "'" & row("OPENPUBL") & "'," _
                            & "'" & row("ACT") & "'," _
                            & "'" & row("NEWID") & "'," _
                            & "'" & row("DEATHYR") & "'," _
                            & "'" & row("CLOSEDAT") & "'," _
                            & "'" & row("CYACTIVE") & "'," _
                            & "'" & row("POSTSEC") & "'," _
                            & "'" & row("PSEFLAG") & "'," _
                            & "'" & row("PSET4FLG") & "'," _
                            & "'" & row("RPTMTH") & "'," _
                            & "'" & row("IALIAS") & "'," _
                            & "'" & row("INSTCAT") & "'," _
                            & "'" & row("CCBASIC") & "'," _
                            & "'" & row("CCIPUG") & "'," _
                            & "'" & row("CCIPGRAD") & "'," _
                            & "'" & row("CCUGPROF") & "'," _
                            & "'" & row("CCENRPRF") & "'," _
                            & "'" & row("CCSIZSET") & "'," _
                            & "'" & row("CARNEGIE") & "'," _
                            & "'" & row("TENURSYS") & "'," _
                            & "'" & row("LANDGRNT") & "'," _
                            & "'" & row("INSTSIZE") & "'," _
                            & "'" & row("CBSA") & "'," _
                            & "'" & row("CBSATYPE") & "'," _
                            & "'" & row("CSA") & "'," _
                            & "'" & row("NECTA") & "'," _
                            & "'" & row("F1SYSTYP") & "'," _
                            & "'" & row("F1SYSNAM") & "'," _
                            & "'" & row("FAXTELE") & "')"
                        i = INSERT_INST_COMMAND.ExecuteNonQuery()
                    Next
                End Using
            End If

    i = INSERT_INST_COMMAND.ExecuteNonQuery() (this is where the error pops up at)

    Really need help with this one, still kinda new to ASP.NET and VB. Thanks in advance!



    CJS


    Thursday, July 5, 2012 2:27 PM

Answers

  • Do not concatenate SQL strings on your own. Instead make use of the SqlCommand's Parameters property. Your way is prone to errors.

    Armin

    Thursday, July 5, 2012 2:33 PM
  • You can't convert an empty string to a numeric value (one or more of your SQL Server table columns is numeric). If you expect one of the column values from the .csv file to be an empty string then you will need check the column value first and assign the corresponding numeric value (probably 0) before attempting to write to the database.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 5, 2012 3:12 PM

All replies

  • Do not concatenate SQL strings on your own. Instead make use of the SqlCommand's Parameters property. Your way is prone to errors.

    Armin

    Thursday, July 5, 2012 2:33 PM
  • You can't convert an empty string to a numeric value (one or more of your SQL Server table columns is numeric). If you expect one of the column values from the .csv file to be an empty string then you will need check the column value first and assign the corresponding numeric value (probably 0) before attempting to write to the database.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 5, 2012 3:12 PM