-
Jul 5th, 2012, 10:41 AM
#1
Thread Starter
Junior Member
Conversion failed when converting the varchar value ' ' to data type int.
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.
i broke my code into 2 parts to see where the error is coming from. Here is part 1:
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
This parts works just fine. basically it is taking the values from the .csv file and adding them into a DataTable.
Here is the 2nd part of the code:
Code:
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() <<< and this is where my error comes in.
Really need help with this. i am still kinda new to VB and ASP.NET
Thankz in advance!
- CJS
-
Jul 5th, 2012, 10:47 AM
#2
Re: Conversion failed when converting the varchar value ' ' to data type int.
The answer is in your title. You have a column which is a number, however you are not supplying one. One of your row("xxx") is most likely empty.
-
Jul 5th, 2012, 10:47 AM
#3
Re: Conversion failed when converting the varchar value ' ' to data type int.
-
Jul 5th, 2012, 11:06 AM
#4
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by ForumAccount
so if i make the Parameters how would they look I am looking at the link and i am getting confused trying to apply it to my code.
Thanks for helping.
-CJS
-
Jul 5th, 2012, 11:17 AM
#5
Re: Conversion failed when converting the varchar value ' ' to data type int.
If you read the article, then you will no longer be confused - it is well written.
-
Jul 5th, 2012, 02:50 PM
#6
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by ForumAccount
If you read the article, then you will no longer be confused - it is well written.
Ok i read the artical and it was good
i went and made the Parameters here is the code:
Code:
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 (@UNITID, @INSTNM, @ADDR, @CITY, @STABBR, @ZIP, @FIPS, @OBEREG, @CHFNM, @CHFTITLE, @GENTELE, @ENI, @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)"
With INSERT_INST_COMMAND.Parameters
.Add("@UNITID", Data.SqlDbType.Int, 0, "Institution_ID")
.Add("@INSTNM", Data.SqlDbType.NVarChar, 1000, "Institution_Name")
.Add("@ADDR", Data.SqlDbType.NVarChar, 1000, "Address_1")
.Add("@CITY", Data.SqlDbType.NVarChar, 1000, "City")
.Add("@STABBR", Data.SqlDbType.NVarChar, 1000, "State_Code")
.Add("@ZIP", Data.SqlDbType.NVarChar, 1000, "Zip")
.Add("FIPS", Data.SqlDbType.Int, 0, "FIPS")
.Add("@OBEREG", Data.SqlDbType.Int, 0, "OBE_Region_ID")
.Add("@CHFNM", Data.SqlDbType.VarChar, 1000, "Chief_Admin_Name_ID")
.Add("@CHFTITLE", Data.SqlDbType.VarChar, 1000, "Chief_Admin_Title_ID")
.Add("@GENTELE", Data.SqlDbType.VarChar, 50, "Institution_Phone")
.Add("@ENI", Data.SqlDbType.NVarChar, 1000, "ENI")
.Add("@OPEID", Data.SqlDbType.NVarChar, 1000, "OPE_Number")
.Add("@OPEFLAG", Data.SqlDbType.Int, 0, "OPEFLAG_ID")
.Add("@WEBADDR", Data.SqlDbType.VarChar, 255, "Institution_Web")
.Add("@ADMINURL", Data.SqlDbType.VarChar, 255, "Admission_Web")
.Add("@FAIDURL", Data.SqlDbType.VarChar, 255, "Financial_Web")
.Add("@APPLURL", Data.SqlDbType.VarChar, 255, "Application_Web")
.Add("@ICLEVEL", Data.SqlDbType.Int, 0, "Type_Of_Institution")
.Add("@CONTROL", Data.SqlDbType.Int, 0, "Level_Of_Institution")
.Add("@HLOFFER", Data.SqlDbType.Int, 0, "HLOFFER_ID")
.Add("@UGOFFER", Data.SqlDbType.Int, 0, "UGOFFER_ID")
.Add("@GROFFER", Data.SqlDbType.Int, 0, "GROFFER_ID")
.Add("@HDEGOFR1", Data.SqlDbType.Int, 0, "HDEGOFR1")
.Add("@DEGGRANT", Data.SqlDbType.Int, 0, "HDEGOFR1_ID")
.Add("@HBCU", Data.SqlDbType.Int, 0, "Historic_Black_College_ID")
.Add("@HOSPITAL", Data.SqlDbType.Int, 0, "Hospital_Available_ID")
.Add("MEDICAL", Data.SqlDbType.Int, 0, "Medical_Degree_ID")
.Add("@TRIBAL", Data.SqlDbType.Int, 0, "Tribal_College_ID")
.Add("@LOCALE", Data.SqlDbType.Int, 0, "Location_ID")
.Add("@OPENPUBL", Data.SqlDbType.Bit, 0, "Open_To_Public")
.Add("@ACT", Data.SqlDbType.NVarChar, 255, "ACT_ID")
.Add("@NEWID", Data.SqlDbType.VarChar, 50, "NEW_ID")
.Add("@DEATHYR", Data.SqlDbType.VarChar, 50, "Deleted_Year")
.Add("@CLOSEDAT", Data.SqlDbType.VarChar, 50, "Closed_Date")
.Add("@CYACTIVE", Data.SqlDbType.Int, 0, "CYACTIVE_ID")
.Add("@POSTSEC", Data.SqlDbType.Int, 0, "POSTEC_ID")
.Add("@PSEFLAG", Data.SqlDbType.Int, 0, "PSEFLAG_ID")
.Add("@PSET4FLG", Data.SqlDbType.Int, 0, "PSET4FLG_ID")
.Add("@RPTMTH", Data.SqlDbType.Int, 0, "RPTMTH_ID")
.Add("@INSTCAT", Data.SqlDbType.Int, 0, "INSTCAT_ID")
.Add("@IALIAS", Data.SqlDbType.VarChar, 1000, "ALIAS")
.Add("@CCBASIC", Data.SqlDbType.Int, 0, "CCBASIC_ID")
.Add("@CCIPUG", Data.SqlDbType.Int, 0, "CCIPUG_ID")
.Add("@CCIPGRAD", Data.SqlDbType.Int, 0, "CCIPGRAD_ID")
.Add("@CCUGPROF", Data.SqlDbType.Int, 0, "CCUGPROF_ID")
.Add("@CCENRPRF", Data.SqlDbType.Int, 0, "CCENPRF_ID")
.Add("@CCSIZSET", Data.SqlDbType.Int, 0, "CCSIZSET_ID")
.Add("@CARNEGIE", Data.SqlDbType.Int, 0, "CARNEGIE_ID")
.Add("@TENURSYS", Data.SqlDbType.Int, 0, "TENURSYS_ID")
.Add("@LANDGRNT", Data.SqlDbType.Int, 0, "Land_Grant_Institution_ID")
.Add("@INSTSIZE", Data.SqlDbType.Int, 0, "Size_Of_Institution_ID")
.Add("@CBSA", Data.SqlDbType.Int, 0, "CBSA_ID")
.Add("@CBSATYPE", Data.SqlDbType.Int, 0, "CBSATYPE_ID")
.Add("@CSA", Data.SqlDbType.Int, 0, "CSA_ID")
.Add("@NECTA", Data.SqlDbType.Int, 0, "NECTA_ID")
.Add("@F1SYSTYP", Data.SqlDbType.Int, 0, "F1SYSTYP_ID")
.Add("@F1SYSNAM", Data.SqlDbType.VarChar, 1000, "F1SYSNAM")
.Add("@FAXTELE", Data.SqlDbType.VarChar, 50, "Institution_Fax")
End With
i = INSERT_INST_COMMAND.ExecuteNonQuery()
Next
End Using
End If
and i have this error here:
i = INSERT_INST_COMMAND.ExecuteNonQuery()
The parameterized query '(@UNITID int,@INSTNM nvarchar(1000),@ADDR nvarchar(1000),@CITY n' expects the parameter '@UNITID', which was not supplied.
-
Jul 5th, 2012, 02:53 PM
#7
Re: Conversion failed when converting the varchar value ' ' to data type int.
You are close, do not use the .Add method, use the .AddWithValue method (used in the article). The value you specify should come from your data row that you were using earlier. You haven't actually specified any values the way you have it (notice how the article specifies the value).
-
Jul 5th, 2012, 03:14 PM
#8
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by ForumAccount
You are close, do not use the .Add method, use the .AddWithValue method (used in the article). The value you specify should come from your data row that you were using earlier. You haven't actually specified any values the way you have it (notice how the article specifies the value).
ok i see which one you are talking about to use.
im trying to figure it out but i have a question. in the code .AddWithValue("@UNITID", Me. what do i put after that? i see in the article they have Me.firstNameField.tex) where would that come from?
-
Jul 5th, 2012, 03:23 PM
#9
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by Train_Wreck
ok i see which one you are talking about to use.
im trying to figure it out but i have a question. in the code .AddWithValue("@UNITID", Me. what do i put after that? i see in the article they have Me.firstNameField.tex) where would that come from?
The next parameter is the value, what value do you want? Where does it come from? Fill it in there.
-
Jul 5th, 2012, 03:23 PM
#10
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
would it be:
.AddWithValue("@UNITID", Me.ToString("Institution_ID"))????
-
Jul 5th, 2012, 03:25 PM
#11
Re: Conversion failed when converting the varchar value ' ' to data type int.
In the first post you are accessing the values from the row. That is how you should still be doing it. Specify the value from the row (index with your column names) as the Value for your AddWithValue call.
-
Jul 5th, 2012, 03:32 PM
#12
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
ok i think i get it. so it would look like this:
.AddWithValue("@UNITID", row("UNITID"))
???
-
Jul 5th, 2012, 03:36 PM
#13
Re: Conversion failed when converting the varchar value ' ' to data type int.
Did you try it ...
Go on, dare you !
I would point out that this will still likely give you the same error. The value still needs to be numeric. Using the params is defo the way to code tho.
-
Jul 5th, 2012, 04:11 PM
#14
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
hey im bout to get off from work. i am just going to try some ways tomorrow Thankz ForumAccount & Grimfort for all y'all help i really do appreciate it hopefully i can figure it out something tomorrow.
- CJS
-
Jul 9th, 2012, 10:00 AM
#15
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Still don't have a solution . . .
-
Jul 9th, 2012, 11:05 AM
#16
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by Grimfort
I would point out that this will still likely give you the same error. The value still needs to be numeric. Using the params is defo the way to code tho.
Possibly you missed this comment ...
As Moss would say "What is the nature of the problem?" ie, what is the error you are getting, still the same one?
-
Jul 9th, 2012, 11:34 AM
#17
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by Grimfort
Possibly you missed this comment ...
As Moss would say "What is the nature of the problem?" ie, what is the error you are getting, still the same one?
yea i saw it tryed it and got tha same error message. like you said i would get. just can't figure it out.
-
Jul 9th, 2012, 11:57 AM
#18
Re: Conversion failed when converting the varchar value ' ' to data type int.
I will repeat what I put above in a different way. You have a field in your database which has been defined as a number. Something in your data, I can't tell you which column, has NO data in it, empty, blank, zip, nada ! It needs to be something, even a 0 (zero). The good news is when your exception has been thrown, you can hover your mouse over all the variables you are using, ie row("UNITID"), and it will tell you what is in there. Find the one that is empty, make it not empty .
-
Jul 9th, 2012, 02:33 PM
#19
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
ok i made everything that was empty "Not Empty". also hovered over row("UNITED") and it says "Dim row as Object" and im getting "Conversion failed when converting the nvarchar value 'CCAF' to data type int." again
-
Jul 9th, 2012, 03:14 PM
#20
Re: Conversion failed when converting the varchar value ' ' to data type int.
I assume you are not putting the actual text "Not Empty" into the value.... ?
Are you also saying your actual data contains the text "Dim row as Object"? If you select the begining and end of the row("UNITED") for example, it should say "1234" or "452", that is a number.
-
Jul 9th, 2012, 03:22 PM
#21
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
where the data was blank i put a "0" zero and some of the data are numbers and some are words
Example of the data:
UNITID: 100636
INSTNM: Community College of the Air Force
ADDR: 100 S Turner Blvd
CITY: Montgomery
STABBR: AL
ZIP: 36114-001
FIPS: 1
OBEREG: 0
CHFNM: Timothy W. Albrecht
and so on. and when i hover over row("UNITID") is says "Dim row as Object".
-
Jul 9th, 2012, 04:47 PM
#22
Re: Conversion failed when converting the varchar value ' ' to data type int.
If you select just those characters, when you hover you see the value. Or you can hover over the declaration of the row variable itself, and then + to view the values. We cannot see your data, something is blank. Is this error happening on the very first loop? Screenshot all the values if you cannot work it out, or even just try inserting 1 field at a time.
Last edited by Grimfort; Jul 9th, 2012 at 04:55 PM.
-
Jul 10th, 2012, 10:06 AM
#23
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
-
Jul 10th, 2012, 10:21 AM
#24
Hyperactive Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
try to remove the "i=" before you execute the query.
-
Jul 10th, 2012, 10:26 AM
#25
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by marniel647
try to remove the "i=" before you execute the query.
This won't affect anything.
Go to where you are adding the value for 'CCAF', make sure that you are indexing the row with that column name and not just passing that column name (sneaking suspicion). Which value index of the row is CCAF supposed to be? The error is quite clear, you are passing in the value 'CCAF' when it is supposed to be a number.
-
Jul 10th, 2012, 10:41 AM
#26
Hyperactive Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by ForumAccount
This won't affect anything.
Go to where you are adding the value for 'CCAF', make sure that you are indexing the row with that column name and not just passing that column name (sneaking suspicion). Which value index of the row is CCAF supposed to be? The error is quite clear, you are passing in the value 'CCAF' when it is supposed to be a number.
yeah my bad thanks for correcting.
-
Jul 10th, 2012, 11:07 AM
#27
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by ForumAccount
This won't affect anything.
Go to where you are adding the value for 'CCAF', make sure that you are indexing the row with that column name and not just passing that column name (sneaking suspicion). Which value index of the row is CCAF supposed to be? The error is quite clear, you are passing in the value 'CCAF' when it is supposed to be a number.
CCAF goes under the column name ALIAS its a varchar in my database, and its not supposed to be a number.
-
Jul 10th, 2012, 11:08 AM
#28
Re: Conversion failed when converting the varchar value ' ' to data type int.
Check your insert statement and verify that the correct columns are mapping to the correct parameters.
-
Jul 10th, 2012, 01:36 PM
#29
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by ForumAccount
Check your insert statement and verify that the correct columns are mapping to the correct parameters.
yea the correct columns are mapped to tha correct parameters
-
Jul 10th, 2012, 01:48 PM
#30
Re: Conversion failed when converting the varchar value ' ' to data type int.
So you can see the values, now of course you need to check that the values are coming up into the correct colum names . You have 60 columns here, as you have seen a different error this time (before it was '', now it is "CCAF"), can you not see which data is trying to be converted and failing? 1 (or more) out of the 60 values is either the wrong value, or the wrong type, you just have to find it.
-
Jul 10th, 2012, 03:03 PM
#31
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by Grimfort
So you can see the values, now of course you need to check that the values are coming up into the correct colum names . You have 60 columns here, as you have seen a different error this time (before it was '', now it is "CCAF"), can you not see which data is trying to be converted and failing? 1 (or more) out of the 60 values is either the wrong value, or the wrong type, you just have to find it.
ok i fixed that error found out that 2 fields didnt match up in my database with my code now i am getting this error: The variable name '@UNITID' has already been declared. Variable names must be unique within a query batch or stored procedure.
-
Jul 10th, 2012, 03:09 PM
#32
Re: Conversion failed when converting the varchar value ' ' to data type int.
The most obvious answer is to look for where you have used that word, and ensure that you have not used it twice, and/or where you have used it, it has the same case, ie @UnitID or @UNITID.
-
Jul 10th, 2012, 03:30 PM
#33
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Originally Posted by Grimfort
The most obvious answer is to look for where you have used that word, and ensure that you have not used it twice, and/or where you have used it, it has the same case, ie @UnitID or @UNITID.
the places i have it is in my command text and my parameters which i need them their right????????
Code:
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, ALIAS, INSTCAT_ID, 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 (@UNITID, @INSTNM, @ADDR, @CITY, @STABBR, @ZIP, @FIPS, @OBEREG, @CHFNM, @CHFTITLE, @GENTELE, @ENI, @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)"
With INSERT_INST_COMMAND.Parameters
.AddWithValue("@UNITID", row("UNITID"))
.AddWithValue("@INSTNM", row("INSTNM"))
.AddWithValue("@ADDR", row("ADDR"))
.AddWithValue("@CITY", row("CITY"))
.AddWithValue("@STABBR", row("STABBR"))
.AddWithValue("@ZIP", row("ZIP"))
.AddWithValue("@FIPS", row("FIPS"))
.AddWithValue("@OBEREG", row("OBEREG"))
.AddWithValue("@CHFNM", row("CHFNM"))
.AddWithValue("@CHFTITLE", row("CHFTITLE"))
.AddWithValue("@GENTELE", row("GENTELE"))
.AddWithValue("@ENI", row("ENI"))
.AddWithValue("@OPEID", row("OPEID"))
.AddWithValue("@OPEFLAG", row("OPEFLAG"))
.AddWithValue("@WEBADDR", row("WEBADDR"))
.AddWithValue("@ADMINURL", row("ADMINURL"))
.AddWithValue("@FAIDURL", row("FAIDURL"))
.AddWithValue("@APPLURL", row("APPLURL"))
.AddWithValue("@ICLEVEL", row("ICLEVEL"))
.AddWithValue("@CONTROL", row("CONTROL"))
.AddWithValue("@HLOFFER", row("HLOFFER"))
.AddWithValue("@UGOFFER", row("UGOFFER"))
.AddWithValue("@GROFFER", row("GROFFER"))
.AddWithValue("@HDEGOFR1", row("HDEGOFR1"))
.AddWithValue("@DEGGRANT", row("DEGGRANT"))
.AddWithValue("@HBCU", row("HBCU"))
.AddWithValue("@HOSPITAL", row("HOSPITAL"))
.AddWithValue("@MEDICAL", row("MEDICAL"))
.AddWithValue("@TRIBAL", row("TRIBAL"))
.AddWithValue("@LOCALE", row("LOCALE"))
.AddWithValue("@OPENPUBL", row("OPENPUBL"))
.AddWithValue("@ACT", row("ACT"))
.AddWithValue("@NEWID", row("NEWID"))
.AddWithValue("@DEATHYR", row("DEATHYR"))
.AddWithValue("@CLOSEDAT", row("CLOSEDAT"))
.AddWithValue("@CYACTIVE", row("CYACTIVE"))
.AddWithValue("@POSTSEC", row("POSTSEC"))
.AddWithValue("@PSEFLAG", row("PSEFLAG"))
.AddWithValue("@PSET4FLG", row("PSET4FLG"))
.AddWithValue("@RPTMTH", row("RPTMTH"))
.AddWithValue("@IALIAS", row("IALIAS"))
.AddWithValue("@INSTCAT", row("INSTCAT"))
.AddWithValue("@CCBASIC", row("CCBASIC"))
.AddWithValue("@CCIPUG", row("CCIPUG"))
.AddWithValue("@CCIPGRAD", row("CCIPGRAD"))
.AddWithValue("@CCUGPROF", row("CCUGPROF"))
.AddWithValue("@CCENRPRF", row("CCENRPRF"))
.AddWithValue("@CCSIZSET", row("CCSIZSET"))
.AddWithValue("@CARNEGIE", row("CARNEGIE"))
.AddWithValue("@TENURSYS", row("TENURSYS"))
.AddWithValue("@LANDGRNT", row("LANDGRNT"))
.AddWithValue("@INSTSIZE", row("INSTSIZE"))
.AddWithValue("@CBSA", row("CBSA"))
.AddWithValue("@CBSATYPE", row("CBSATYPE"))
.AddWithValue("@CSA", row("CSA"))
.AddWithValue("@NECTA", row("NECTA"))
.AddWithValue("@F1SYSTYP", row("F1SYSTYP"))
.AddWithValue("@F1SYSNAM", row("F1SYSNAM"))
.AddWithValue("@FAXTELE", row("FAXTELE"))
End With
i = INSERT_INST_COMMAND.ExecuteNonQuery()
Next
Last edited by Train_Wreck; Jul 11th, 2012 at 02:12 PM.
-
Jul 12th, 2012, 02:11 PM
#34
Thread Starter
Junior Member
Re: Conversion failed when converting the varchar value ' ' to data type int.
Hey found out how to fix this error. i had to add this code:
Code:
INSERT_INST_COMMAND.Parameters.Clear()
before:
Code:
With INSERT_INST_COMMAND.Parameters
.AddWithValue("@UNITID", row("UNITID"))
.AddWithValue("@INSTNM", row("INSTNM"))
Thankz to everyone for all the help i really do appreciate it.
Tags for this Thread
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
|