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! :confused:

- CJS :eek2:

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.

Using a parameterized query would fix this. Oh, and you should turn Option Strict On.

Quote:

---

Originally Posted by ForumAccount

Using a parameterized query would fix this. Oh, and you should turn Option Strict On.

---

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. :confused:

Thanks for helping.

-CJS

If you read the article, then you will no longer be confused - it is well written.

Quote:

---

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 :thumb:

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.

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).

Quote:

---

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?

Quote:

---

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.

would it be:
.AddWithValue("@UNITID", Me.ToString("Institution_ID"))????

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.

ok i think i get it. so it would look like this:

.AddWithValue("@UNITID", row("UNITID"))

???

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.

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 :thumb: hopefully i can figure it out something tomorrow.

- CJS

Still don't have a solution . . .

Quote:

---

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?

Quote:

---

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.

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 :).

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

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.

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".

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.

Quote:

---

Originally Posted by Grimfort

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.

---

ok i have some screenshots. let me know if that is wat u needed to see.

This is the Error:
http://i45.tinypic.com/bhlgmg.jpg

Values:
http://i47.tinypic.com/21ab9kx.jpg

http://i49.tinypic.com/fc1xqh.jpg

try to remove the "i=" before you execute the query.

Quote:

---

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.

Quote:

---

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. :)

Quote:

---

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.

Check your insert statement and verify that the correct columns are mapping to the correct parameters.

Quote:

---

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

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.

Quote:

---

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.

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.

Quote:

---

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

---

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. :afrog: :D :wave: