-
Jun 26th, 2012, 10:52 AM
#1
Thread Starter
Junior Member
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:
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("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("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
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
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.
-
Jun 26th, 2012, 11:03 AM
#2
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
-
Jun 26th, 2012, 11:54 AM
#3
Re: Conversion from string " " to type 'Integer' is not valid.
take a close look at this:
VB Code:
& "'" & row("UNITID") & "," _ & "'" & row("INSTNM") & "," _ & "'" & row("ADDR") & "," _ & "'" & 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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|