problem about ' charachter when saving record.

  • hi.

    i have problem about a query with dinamic insert.

    i have a few table that i set their column names same. becaause their structure is same and i want to write one nsert,pdate query for this same structured tables.

    my query is this:

    proc name...

    @baslik nvarchar(max)=null,

    @icerik nvarchar(max)=null

    AS

    BEGIN

    declare @sqlcum nvarchar(max)

    set @sqlcum ='insert into makale (baslik,icerik) values(N'''+@baslik+ ''',N'''+@icerik+ ''')'

    exec(@sqlcum)

    ................

    this works ok. problem is that ' character. i use fckeditor. If i use this dinamic insert query ' character gives error. how can i solve this problem?

    Thanks..

  • sa.ordekci (6/5/2011)


    this works ok. problem is that ' character. i use fckeditor. If i use this dinamic insert query ' character gives error. how can i solve this problem?

    I can not understand this,can you give some more Detail.

  • proc name...

    @baslik nvarchar(max)=null,

    @icerik nvarchar(max)=null

    AS

    BEGIN

    declare @sqlcum nvarchar(max)

    set @sqlcum ='insert into makale (baslik,icerik) values(N'''+@baslik+ ''',N'''+@icerik+ ''')'

    exec(@sqlcum)

    ................

    my dinamic query is this. i use this on a web site project. i use textbox, fckeditor or saving datas. for example when i wrote a writing like this:

    Izmir'in en güzel yerleri..." s you see, i use ' character after Izmir. if i use this character, dinamic query gives error:

    "Incorrect syntax near the keyword 'in'.

    Unclosed quotation mark after the character string ')'. "

  • Yes,Cause ' character is used to start and end String.

    when you want to insert that value in sql sever,you must provide 'Izmir''in en güzel yerleri' to sql server.

    instead of 'Izmir'in en güzel yerleri'.see the difference in both string 'Izmir' and 'Izmir''.

  • Run following code it will execute successfully

    declare @baslik nvarchar(max)=null

    SET @baslik='Izmir''in en güzel yerleri'

    Select @baslik

    while when you run following code you will get error

    declare @baslik nvarchar(max)=null

    SET @baslik='Izmir'in en güzel yerleri'

    Select @baslik

  • but i enter baslik from textbox. that is @baslik value can change. also if i use as below:

    @baslik nvarchar(max)=null,

    @icerik nvarchar(max)=null

    AS

    BEGIN

    insert into makale values(@baslik,@icerik)

    END

    normal proc not dinamic.

    it runs and accept ' character.

  • You might want to look at the Quotename() function in BOL.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • another option is to use replace as in:

    declare @a1 varchar(50)

    declare @sql varchar(255)

    select @a1 = 'Izmir''in en güzel yerleri'

    -- char(39) = ' this means it replaces a single quote with 2 single quotes

    select @sql = 'Select ' + CHAR(39) + Replace(@a1, CHAR(39),CHAR(39)+CHAR(39)) + CHAR(39)

    exec(@sql)

    -- output from exec is: Izmir'in en güzel yerleri

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply