Results 1 to 34 of 34

Thread: Conversion failed when converting the varchar value ' ' to data type int.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    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

  2. #2
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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.

  3. #3
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    Re: Conversion failed when converting the varchar value ' ' to data type int.

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

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by ForumAccount View Post
    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.

    Thanks for helping.

    -CJS

  5. #5
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    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.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by ForumAccount View Post
    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.

  7. #7
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

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

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by ForumAccount View Post
    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?

  9. #9
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by Train_Wreck View Post
    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.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

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

  11. #11
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    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.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

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

    ???

  13. #13
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    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

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Still don't have a solution . . .

  16. #16
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by Grimfort View Post
    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?

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by Grimfort View Post
    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.

  18. #18
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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 .

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    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

  20. #20
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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.

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

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

  22. #22
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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.

  23. #23

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by Grimfort View Post
    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:


    Values:



  24. #24
    Hyperactive Member marniel647's Avatar
    Join Date
    Aug 2010
    Location
    MSDN Library
    Posts
    259

    Re: Conversion failed when converting the varchar value ' ' to data type int.

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

  25. #25
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by marniel647 View Post
    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.

  26. #26
    Hyperactive Member marniel647's Avatar
    Join Date
    Aug 2010
    Location
    MSDN Library
    Posts
    259

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by ForumAccount View Post
    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.

  27. #27

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by ForumAccount View Post
    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.

  28. #28
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    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.

  29. #29

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by ForumAccount View Post
    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

  30. #30
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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.

  31. #31

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by Grimfort View Post
    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.

  32. #32
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    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.

  33. #33

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    Re: Conversion failed when converting the varchar value ' ' to data type int.

    Quote Originally Posted by Grimfort View Post
    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.

  34. #34

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    24

    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
  •  



Click Here to Expand Forum to Full Width