Skip to main content
A
Anonymous
December 1, 2006
Question

apostrophe error

  • December 1, 2006
  • 13 replies
  • 829 views
How can I fix this code, I keep getting this error
"[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '-'. " Thanks.

<cfhttp method="Get"
url=" http://192.168.14.51/matt/test.txt"
name="importCSV"
textqualifier=",">
<cfloop list = "#CFHTTP.FileContent#" INDEX="ThisEmail">
<CFQUERY NAME = "CheckEmail" DATASOURCE="T">
SELECT Email
FROM EmailContest
WHERE Email = '#ThisEmail#'
</CFQUERY>
<CFIF CheckEmail.RecordCount EQ 0>
<CFQUERY NAME="Insert" datasource="T">
INSERT INTO tr-sql.T.dbo.EmailContest
(Email)
VALUES ('#ThisEmail#')
</CFQUERY>
</CFIF>
</cfloop>
    This topic has been closed for replies.

    13 replies

    J
    j_stevenson
    Known Participant
    December 8, 2006
    I've never had much luck with "word"-versions on HTML-friendly code like "&apos;". Maybe try &#39;
    A
    Anonymous
    January 9, 2007
    I thought it worked, it only updates the first record (which is the column named Email) in the csv list, it doesnt loop thru each. The csv list has this. ->

    Email
    ed@hello.com
    jojo@aol.com
    friend@fiend.com

    Here is the CFM code again.

    <cfhttp method="Get"
    url=" http://192.168.14.51/matt/test.csv"
    name="importCSV"
    textqualifier=",">

    <cfloop list = "#CFHTTP.FileContent#" INDEX="ThisEmail">
    <cfset ThisEmail = listLast(cfhttp.filecontent, ' ') />
    <CFQUERY NAME = "CheckEmail" DATASOURCE="t">
    SELECT Email
    FROM Subscriber_Info_New
    WHERE Email = '#ThisEmail#'
    </CFQUERY>



    <CFSET ThisEmail = ReplaceNoCase(ThisEmail, "''", "", "ALL")>
    <CFIF CheckEmail.RecordCount EQ 0>
    <!---<cfdump var="#ThisEmail#">--->
    <CFQUERY NAME="Insert" datasource="t">
    INSERT INTO Subscriber_Info_New
    (Email)
    VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Trim(ThisEmail)#">)
    </CFQUERY>
    </CFIF>
    </cfloop>
    A
    Anonymous
    December 1, 2006
    yes, thank you
    A
    Anonymous
    December 1, 2006
    Thank you, I finally got it working.

    <cfhttp method="Get"
    url=" http://192.168.14.51/matt/test.csv"
    name="importCSV"
    textqualifier=",">

    <cfloop list = "#CFHTTP.FileContent#" INDEX="ThisEmail">
    <CFSET ThisEmail = Right(CFHTTP.FileContent, (Len(CFHTTP.FileContent)-5) )>
    <CFQUERY NAME = "CheckEmail" DATASOURCE="T">
    SELECT Email
    FROM EmailContest
    WHERE Email = '#ThisEmail#'
    </CFQUERY>

    <!---<cfdump var="#ThisEmail#">--->

    <CFSET ThisEmail = ReplaceNoCase(ThisEmail, "''", "", "ALL")>
    <CFIF CheckEmail.RecordCount EQ 0>

    <CFQUERY NAME="Insert" datasource="t">
    INSERT INTO EmailContest
    (Email)
    VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Trim(ThisEmail)#">)
    </CFQUERY>
    </CFIF>
    </cfloop>
    A
    Anonymous
    December 1, 2006
    quote:
    <CFSET ThisEmail = Right(CFHTTP.FileContent, (Len(CFHTTP.FileContent)-5) )>


    you don't think <cfset thisEmail = listLast(cfhttp.filecontent, ' ') /> is a little cleaner? :)
    A
    Anonymous
    December 1, 2006
    worst case scenario is use a listLast() function with a space as delimiter.
    A
    Anonymous
    December 1, 2006
    Ok I see part of the problem, CF is seeing the value of Email as
    "email applepie@aol.com", its not seeing it as "applepie@aol.com". So I need to figure out why its not seeing 'Email' as a column and "applepie@aol.com" as the value.
    A
    Anonymous
    December 1, 2006
    spaces? maybe a trim() function around it.
    A
    Anonymous
    December 1, 2006
    max value is 40, the email im testing with is "applepie@aol.com" which is under 40 so Im a bit confused.
    A
    Anonymous
    December 1, 2006
    what's the size of the field in the database? how many characters are you trying to enter?

    the error indicates that you're trying to insert a string that's longer than the maximum value that the column allows.
    A
    Anonymous
    December 1, 2006
    ok i updated the code and now i get "[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated."



    <cfhttp method="Get"
    url=" http://192.168.14.51/matt/test.txt"
    name="importCSV"
    textqualifier=",">

    <cfloop list = "#CFHTTP.FileContent#" INDEX="ThisEmail">

    <CFQUERY NAME = "CheckEmail" DATASOURCE="t">
    SELECT Email
    FROM EmailContest
    WHERE Email = '#ThisEmail#'
    </CFQUERY>
    <cfdump var="#ThisEmail#">
    <CFSET ThisEmail = ReplaceNoCase(ThisEmail, "''", "", "ALL")>
    <CFIF CheckEmail.RecordCount EQ 0>
    <CFQUERY NAME="Insert" datasource="t">
    INSERT INTO EmailContest
    (Email)
    VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisEmail#">)
    </CFQUERY>
    </CFIF>
    </cfloop>
    A
    Anonymous
    December 1, 2006
    ok i updated the code and now i get "[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated."



    <cfhttp method="Get"
    url=" http://192.168.14.51/matt/test.txt"
    name="importCSV"
    textqualifier=",">

    <cfloop list = "#CFHTTP.FileContent#" INDEX="ThisEmail">

    <CFQUERY NAME = "CheckEmail" DATASOURCE="T">
    SELECT Email
    FROM EmailContest
    WHERE Email = '#ThisEmail#'
    </CFQUERY>
    <cfdump var="#ThisEmail#">
    <CFSET ThisEmail = ReplaceNoCase(ThisEmail, "''", "", "ALL")>
    <CFIF CheckEmail.RecordCount EQ 0>
    <CFQUERY NAME="Insert" datasource="t">
    INSERT INTO EmailContest
    (Email)
    VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisEmail#">)
    </CFQUERY>
    </CFIF>
    </cfloop>
    Show more replies
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices