locked
Can I use REPLACE on INSERT? RRS feed

  • Question

  • User-1644933537 posted

    I have the following in my InsertCommand of my SqlDataSource with an <InsertParameter> called NAME which is bound.

    <asp:TextBox ID="TextBox_NAME" runat="server" Text='<%# Bind("NAME") %>' />
    

    CHR(96) = ` (Grave accent, i call it a backtick)
    CHR(39) = ' (single quote)

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    	ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    	ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
    	SelectCommand="SELECT REPLACE(NAME, CHR(96), CHR(39)) AS NAME FROM SOME_TABLE"
    	InsertCommand="INSERT INTO SOME_TABLE (NAME) VALUES(REPLACE(LTRIM(RTRIM(:NAME)), CHR(39), CHR(96)))">
    	<InsertParameters>
    		<asp:Parameter Name="NAME" />
    	</InsertParameters>
    </asp:SqlDataSource>

    When I run it from the web, nothing happened so I ran it in SQL Developer and this is the error I received.

    Error report:
    SQL Error: ORA-00911: invalid character
    00911. 00000 -  "invalid character"
    *Cause:    identifiers may not start with any ASCII character other than
               letters and numbers.  $#_ are also allowed after the first
               character.  Identifiers enclosed by doublequotes may contain
               any character other than a doublequote.  Alternative quotes
               (q'#...#') cannot use spaces, tabs, or carriage returns as
               delimiters.  For all other contexts, consult the SQL Language
               Reference Manual.

    It appears to have a problem with the REPLACE function as when that is removed, it INSERTs the record with no issue.

    Is REPLACE not intended to work on INSERT. If it works in SELECT, then I would think it would work on INSERT.

    Wednesday, July 23, 2014 2:18 PM

All replies

  • User1508394307 posted

    You cannot use replace as well as ltrim and rtrim in insert like that. Instead you can use replace, etc in select and combine it with insert

    example:

    INSERT INTO SOME_TABLE (NAME) SELECT REPLACE(LTRIM(RTRIM(:NAME)), CHR(39), CHR(96)) FROM DUAL

    Makes sense to test it directly against database and read

    http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-select.html 

    Wednesday, July 23, 2014 3:34 PM