Conversion from string " " to type 'Integer' is not valid.-VBForums
Results 1 to 3 of 3

Thread: Conversion from string " " to type 'Integer' is not valid.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Conversion from string " " to type 'Integer' is not valid.

    Hey Im having trouble with my code i keep getting this error: Conversion from string "UNITID, INSTNM, ADDR, CITY, STAB" to type 'Integer' is not valid. im trying to upload a .csv file to tables in my SQL Database. i really need help! here is my code:
    vb.net Code:
    1. Dim WFSDINSTITUTION As String = ""
    2.             Dim WFSDSTRING As New System.Data.SqlClient.SqlConnection
    3.             WFSDSTRING.ConnectionString = "Data Source=LED-SQL;Initial Catalog=WorkforceSD_DEV;Integrated Security=True"
    4.             WFSDSTRING.Open()
    5.             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;"
    6.             Using INSTITUTIONCONNECTION As New System.Data.OleDb.OleDbConnection(Institution)
    7.                 Dim INST_TABLE As New Data.DataTable()
    8.                 INST_TABLE.Columns.Add("UNITID")
    9.                 INST_TABLE.Columns.Add("INSTNM")
    10.                 INST_TABLE.Columns.Add("ADDR")
    11.                 INST_TABLE.Columns.Add("CITY")
    12.                 INST_TABLE.Columns.Add("STABBR")
    13.                 INST_TABLE.Columns.Add("ZIP")
    14.                 INST_TABLE.Columns.Add("FIPS")
    15.                 INST_TABLE.Columns.Add("OBEREG")
    16.                 INST_TABLE.Columns.Add("CHFNM")
    17.                 INST_TABLE.Columns.Add("CHFTITLE")
    18.                 INST_TABLE.Columns.Add("GENTELE")
    19.                 INST_TABLE.Columns.Add("ENI")
    20.                 INST_TABLE.Columns.Add("OPEID")
    21.                 INST_TABLE.Columns.Add("OPEFLAG")
    22.                 INST_TABLE.Columns.Add("WEBADDR")
    23.                 INST_TABLE.Columns.Add("ADMINURL")
    24.                 INST_TABLE.Columns.Add("FAIDURL")
    25.                 INST_TABLE.Columns.Add("APPLURL")
    26.                 INST_TABLE.Columns.Add("ICLEVEL")
    27.                 INST_TABLE.Columns.Add("CONTROL")
    28.                 INST_TABLE.Columns.Add("HLOFFER")
    29.                 INST_TABLE.Columns.Add("UGOFFER")
    30.                 INST_TABLE.Columns.Add("GROFFER")
    31.                 INST_TABLE.Columns.Add("HDEGOFR1")
    32.                 INST_TABLE.Columns.Add("DEGGRANT")
    33.                 INST_TABLE.Columns.Add("HBCU")
    34.                 INST_TABLE.Columns.Add("HOSPITAL")
    35.                 INST_TABLE.Columns.Add("MEDICAL")
    36.                 INST_TABLE.Columns.Add("TRIBAL")
    37.                 INST_TABLE.Columns.Add("LOCALE")
    38.                 INST_TABLE.Columns.Add("OPENPUBL")
    39.                 INST_TABLE.Columns.Add("ACT")
    40.                 INST_TABLE.Columns.Add("NEWID")
    41.                 INST_TABLE.Columns.Add("DEATHYR")
    42.                 INST_TABLE.Columns.Add("CLOSEDAT")
    43.                 INST_TABLE.Columns.Add("CYACTIVE")
    44.                 INST_TABLE.Columns.Add("POSTSEC")
    45.                 INST_TABLE.Columns.Add("PSEFLAG")
    46.                 INST_TABLE.Columns.Add("PSET4FLG")
    47.                 INST_TABLE.Columns.Add("RPTMTH")
    48.                 INST_TABLE.Columns.Add("IALIAS")
    49.                 INST_TABLE.Columns.Add("INSTCAT")
    50.                 INST_TABLE.Columns.Add("CCBASIC")
    51.                 INST_TABLE.Columns.Add("CCIPUG")
    52.                 INST_TABLE.Columns.Add("CCIPGRAD")
    53.                 INST_TABLE.Columns.Add("CCUGPROF")
    54.                 INST_TABLE.Columns.Add("CCENRPRF")
    55.                 INST_TABLE.Columns.Add("CCSIZSET")
    56.                 INST_TABLE.Columns.Add("CARNEGIE")
    57.                 INST_TABLE.Columns.Add("TENURSYS")
    58.                 INST_TABLE.Columns.Add("LANDGRNT")
    59.                 INST_TABLE.Columns.Add("INSTSIZE")
    60.                 INST_TABLE.Columns.Add("CBSA")
    61.                 INST_TABLE.Columns.Add("CBSATYPE")
    62.                 INST_TABLE.Columns.Add("CSA")
    63.                 INST_TABLE.Columns.Add("NECTA")
    64.                 INST_TABLE.Columns.Add("F1SYSTYP")
    65.                 INST_TABLE.Columns.Add("F1SYSNAM")
    66.                 INST_TABLE.Columns.Add("FAXTELE")
    67.                 Dim INST_TEST2010 As New FileIO.TextFieldParser("C:\Documents and Settings\CSchexnaydre\Desktop\TEST SHEETS\Institution\INST_TEST2010.csv")
    68.                 INST_TEST2010.Delimiters = New String() {","}
    69.                 INST_TEST2010.HasFieldsEnclosedInQuotes = True
    70.                 INST_TEST2010.TrimWhiteSpace = True
    71.                 INST_TEST2010.ReadLine()
    72.                 Do Until INST_TEST2010.EndOfData = True
    73.                     INST_TABLE.Rows.Add(INST_TEST2010.ReadFields("UNITID, INSTNM, ADDR, CITY, STABBR, ZIP, FIPS, OBEREG, CHFNM, CHFTITLE, GENTELE, EIN, OPEID, OPEFLAG, WEBADDR, ADMINURL, FAIDURL, APPLURL, ICLEVEL, CONTROL, HLOFFER, UGOFFER, GROFFER, HDEGOFR1, DEGGRANT, HBCU, HOSPITAL, MEDICAL, TRIBAL, LOCALE, OPENPUBL, ACT, NEWID, DEATHYR, CLOSEDAT, CYACTIVE, POSTSEC, PSEFLAG, PSET4FLG, RPTMTH, IALIAS, INSTCAT, CCBASIC, CCIPUG, CCIPGRAD, CCUGPROF, CCENRPRF, CCSIZSET, CARNEGIE, TENURSYS, LANDGRNT, INSTSIZE, CBSA, CBSATYPE, CSA, NECTA, F1SYSTYP, F1SYSNAM, FAXTELE"))
    74.                 Loop
    75.                 Dim i As Integer
    76.                 Dim INSERT_INST_COMMAND = New System.Data.SqlClient.SqlCommand
    77.                 INSERT_INST_COMMAND = WFSDSTRING.CreateCommand()
    78.                 For Each row In INST_TABLE.Rows
    79.                     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 (" _
    80.                         & "'" & row("UNITID") & "," _
    81.                         & "'" & row("INSTNM") & "," _
    82.                         & "'" & row("ADDR") & "," _
    83.                         & "'" & row("CITY") & "," _
    84.                         & "'" & row("STABBR") & "," _
    85.                         & "'" & row("ZIP") & "," _
    86.                         & "'" & row("FIPS") & "," _
    87.                         & "'" & row("OBEREG") & "," _
    88.                         & "'" & row("CHFNM") & "," _
    89.                         & "'" & row("CHFTITLE") & "," _
    90.                         & "'" & row("GENTELE") & "," _
    91.                         & "'" & row("ENI") & "," _
    92.                         & "'" & row("OPEID") & "," _
    93.                         & "'" & row("OPEFLAG") & "," _
    94.                         & "'" & row("WEBADDR") & "," _
    95.                         & "'" & row("ADMINURL") & "," _
    96.                         & "'" & row("FAIDURL") & "," _
    97.                         & "'" & row("APPLURL") & "," _
    98.                         & "'" & row("ICLEVEL") & "," _
    99.                         & "'" & row("CONTROL") & "," _
    100.                         & "'" & row("HLOFFER") & "," _
    101.                         & "'" & row("UGOFFER") & "," _
    102.                         & "'" & row("GROFFER") & "," _
    103.                         & "'" & row("HDEGOFR1") & "," _
    104.                         & "'" & row("DEGGRANT") & "," _
    105.                         & "'" & row("HBCU") & "," _
    106.                         & "'" & row("HOSPITAL") & "," _
    107.                         & "'" & row("MEDICAL") & "," _
    108.                         & "'" & row("TRIBAL") & "," _
    109.                         & "'" & row("LOCALE") & "," _
    110.                         & "'" & row("OPENPUBL") & "," _
    111.                         & "'" & row("ACT") & "," _
    112.                         & "'" & row("NEWID") & "," _
    113.                         & "'" & row("DEATHYR") & "," _
    114.                         & "'" & row("CLOSEDAT") & "," _
    115.                         & "'" & row("CYACTIVE") & "," _
    116.                         & "'" & row("POSTSEC") & "," _
    117.                         & "'" & row("PSEFLAG") & "," _
    118.                         & "'" & row("PSET4FLG") & "," _
    119.                         & "'" & row("RPTMTH") & "," _
    120.                         & "'" & row("IALIAS") & "," _
    121.                         & "'" & row("INSTCAT") & "," _
    122.                         & "'" & row("CCBASIC") & "," _
    123.                         & "'" & row("CCIPUG") & "," _
    124.                         & "'" & row("CCIPGRAD") & "," _
    125.                         & "'" & row("CCUGPROF") & "," _
    126.                         & "'" & row("CCENRPRF") & "," _
    127.                         & "'" & row("CCSIZSET") & "," _
    128.                         & "'" & row("CARNEGIE") & "," _
    129.                         & "'" & row("TENURSYS") & "," _
    130.                         & "'" & row("LANDGRNT") & "," _
    131.                         & "'" & row("INSTSIZE") & "," _
    132.                         & "'" & row("CBSA") & "," _
    133.                         & "'" & row("CBSATYPE") & "," _
    134.                         & "'" & row("CSA") & "," _
    135.                         & "'" & row("NECTA") & "," _
    136.                         & "'" & row("F1SYSTYP") & "," _
    137.                         & "'" & row("F1SYSNAM") & "," _
    138.                         & "'" & row("FAXTELE") & ")"
    139.                     i = INSERT_INST_COMMAND.ExecuteNonQuery()
    140.                 Next
    141.             End Using
    need help ASAP

    EDIT: This is where i am getting the error:

    Do Until INST_TEST2010.EndOfData = True
    INST_TABLE.Rows.Add(INST_TEST2010.ReadFields("UNITID, INSTNM, ADDR, CITY, STABBR, ZIP, FIPS, OBEREG, CHFNM, CHFTITLE, GENTELE, EIN, OPEID, OPEFLAG, WEBADDR, ADMINURL, FAIDURL, APPLURL, ICLEVEL, CONTROL, HLOFFER, UGOFFER, GROFFER, HDEGOFR1, DEGGRANT, HBCU, HOSPITAL, MEDICAL, TRIBAL, LOCALE, OPENPUBL, ACT, NEWID, DEATHYR, CLOSEDAT, CYACTIVE, POSTSEC, PSEFLAG, PSET4FLG, RPTMTH, IALIAS, INSTCAT, CCBASIC, CCIPUG, CCIPGRAD, CCUGPROF, CCENRPRF, CCSIZSET, CARNEGIE, TENURSYS, LANDGRNT, INSTSIZE, CBSA, CBSATYPE, CSA, NECTA, F1SYSTYP, F1SYSNAM, FAXTELE"))
    Loop

    (Sorry bout that)
    Last edited by Train_Wreck; Jun 26th, 2012 at 01:01 PM.

  2. #2
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    24,281

    Re: Conversion from string " " to type 'Integer' is not valid.

    Those don't look like random values, they look like row headers. Is that the case? Could you just be starting with the wrong line?
    My usual boring signature: Nothing

  3. #3
    PowerPoster
    Join Date
    May 2002
    Posts
    25,459

    Re: Conversion from string " " to type 'Integer' is not valid.

    take a close look at this:
    VB Code:
    1. & "'" & row("UNITID") & "," _
    2. & "'" & row("INSTNM") & "," _
    3. & "'" & row("ADDR") & "," _
    4. & "'" & row("CITY") & "," _

    you have opening tick marks for strings... but then never close them....

    that said... I've never seen ReadFields ever take a parameter before (a little surprised it compiles)...
    And I don't see anything in the documentation that suggests it does take a parameter.

    -tg


    edit - also, for future reference, if you're going to post code like that, it would be helpful to point out WHICH line the error happens on, that way we don't have to guess.
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.