REPLACE() Function within a NTEXT column in SQL Server?

Thanks, I've tried the following already within INSERT query, but it didn't work for some reason:

'" & cast(replace(cast(oldMessage as varchar(MAX)),"'", "''") as ntext) &  "'

Not sure what's wrong with it, any idea?

Since you are using SQL 2005, change your column from ntext to nvarchar(MAX).  Once you do that, you can use the REPLACE function.  ntext was grandfathered in to SQL 2005, but isn't recommended.

I have very important data in this column, about 1000 recordset. Will I loose the data if the ntext is changed to nvarchar(MAX) directly on SQL 2005?

Thanks.

No, you will not lose any data.  To be safe, copy your data into a new table with an ntext field and try the conversion there.

You don't need to cast it back to ntext.  You can insert nvarchar(max) data into an ntext column.

BUT, if you have any existing code that deals with the ntext column (ie. updatetext), it will begin to fail immediately if you change your data type.  

I prefer not to change the column data type. So, back to the same problem, how can add this to INSERT query:

'" & cast(replace(cast(Message as varchar(MAX)),"'", "''") as ntext) &  "'

I have tried the following, but it didn't work:

dbConn.Execute("Insert Into Files (Title, SectionId, UserId, Description, Comments, CheckOut, MajorVersion, MinorVersion, URL, DLCId, CreateDate, Message, IsDeleted, BaseFileId, PreviousFileId, CheckOutDate, CurrentFile, CreatorId, LastPopBy, intLinks, extLinks, iLinkCount, eLinkCount) values ('" & Replace(objRecordSet("Title"), "'", "''") & "'," & objRecordSet("SectionId") & "," &  uId & ",'" & objRecordSet("Description") & "','" & cmt & "',1," & ver & ",0,'-', 1 ,GetDate(),'" & cast(replace(cast(Message as varchar(MAX)),"'", "''") as ntext) & "',0," & bFileId & "," & objRecordSet("FileId") & ",GetDate(), 1," & objRecordSet("CreatorId") & "," & objRecordSet("LastPopBy") & ",'" &  replace(objRecordSet("intLinks"), "'", "") & "','" & replace(objRecordSet("extLinks") , "'", "") & "'," & objRecordSet("iLinkCount") & "," & objRecordSet("eLinkCount") & ")")

Try this:

I put the replace inside of the string you are executing, so that it uses the SQL replace, and not the vbs.

But I would STRONGLY encourage you to consider using a stored procedure with parameterized in put for your insert and not inline SQL.

Because it could be bad if someone entered the following into your field where cmtis being pulled from:

';drop table files;--

That's a SQL injection attack at it's simplest form.

dbConn.Execute
("Insert Into Files (Title, SectionId, UserId, Description, Comments, CheckOut, MajorVersion, MinorVersion, URL, DLCId, CreateDate, Message, IsDeleted, BaseFileId, PreviousFileId, CheckOutDate, CurrentFile, CreatorId, LastPopBy, intLinks, extLinks, iLinkCount, eLinkCount) values ('" 
& Replace(objRecordSet("Title"), "'", "''") 
& "'," 
& objRecordSet("SectionId") 
& "," 
&  uId 
& ",'" 
& objRecordSet("Description") 
& "','" 
& cmt 
& "',1," 
& ver 
& ",0,'-', 1 ,GetDate(),'
& replace(cast(" &Message & "as varchar(MAX)),""'"", ""''"") 
& "',0," 
& bFileId 
& "," 
& objRecordSet("FileId") 
& ",GetDate(), 1," 
& objRecordSet("CreatorId") 
& "," 
& objRecordSet("LastPopBy") 
& ",'" 
&  replace(objRecordSet("intLinks"), "'", "") 
& "','" 
& replace(objRecordSet("extLinks") , "'", "") 
& "'," 
& objRecordSet("iLinkCount") 
& "," 
& objRecordSet("eLinkCount") 
& ")")

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

No it doesn't work.
I've also tried the following but without any luck:

dim oldMessage, newMessage
oldMessage = objRecordSet("Message")
newMessage = replace(cast(" &oldMessage & "as varchar(MAX)),""'"", ""''"")

dbConn.Execute("Insert Into Files (Title, SectionId, UserId, Description, Comments, CheckOut, MajorVersion, MinorVersion, URL, DLCId, CreateDate, Message, IsDeleted, BaseFileId, PreviousFileId, CheckOutDate, CurrentFile, CreatorId, LastPopBy, intLinks, extLinks, iLinkCount, eLinkCount) values ('" & Replace(objRecordSet("Title"), "'", "''") & "'," & objRecordSet("SectionId") & "," &  uId & ",'" & objRecordSet("Description") & "','" & cmt & "',1," & ver & ",0,'-', 1 ,GetDate()," & newMessage & ",0," & bFileId & "," & objRecordSet("FileId") & ",GetDate(), 1," & objRecordSet("CreatorId") & "," & objRecordSet("LastPopBy") & ",'" &  replace(objRecordSet("intLinks"), "'", "") & "','" & replace(objRecordSet("extLinks") , "'", "") & "'," & objRecordSet("iLinkCount") & "," & objRecordSet("eLinkCount") & ")")

Make a backup of your database and change the column to nvarchar(max).  That is the best overall solution.

Here's what you need to determine.  Is it a problem with the SQL replace, or the asp code.

To test, change your insert into files, to insert JUST the nTEXT value (with the replacements) into two different columns in a DUMMY test table.  Just create a table with ntext and nvarchar(max).  Try changing your execute to insert into that table.  

Or, just do a response.write and try writing the unmodified value out to your page somewhere to see if the value is being retrieved correctly.

All of these steps will help you to identify what ISN'T working about this.

I've changed database column datatype to nvarchar(max) and tried the following:

Replace(objRecordSet("Message"), "'", "''")

Replace(cast(objRecordSet("Message")),"'", "''")

and even this:

dim oldMessage, newMessage
oldMessage = objRecordSet("Message")
newMessage = replace(cast(" &oldMessage & "as varchar(MAX)),""'"", ""''"")


dbConn.Execute("Insert Into Files (Title, SectionId, UserId, Description, Comments, CheckOut, MajorVersion, MinorVersion, URL, DLCId, CreateDate, Message, IsDeleted, BaseFileId, PreviousFileId, CheckOutDate, CurrentFile, CreatorId, LastPopBy, intLinks, extLinks, iLinkCount, eLinkCount) values ('" & Replace(objRecordSet("Title"), "'", "''") & "'," & objRecordSet("SectionId") & "," &  uId & ",'" & objRecordSet("Description") & "','" & cmt & "',1," & ver & ",0,'-', 1 ,GetDate()," & newMessage & ",0," & bFileId & "," & objRecordSet("FileId") & ",GetDate(), 1," & objRecordSet("CreatorId") & "," & objRecordSet("LastPopBy") & ",'" &  replace(objRecordSet("intLinks"), "'", "") & "','" & replace(objRecordSet("extLinks") , "'", "") & "'," & objRecordSet("iLinkCount") & "," & objRecordSet("eLinkCount") & ")")

But nothing, any other ideas?

Thanks.

But nothing, any other ideas?

Try what I suggested in ID:22456682.

I did try your suggestion!

First I've created dummy table called CheckFiles with the following recordsets Message (nvarchar(MAX)) and MessageOld (ntext). Then I tried to retrieve their values as follows:

If Recordset.EOF Then
Response.Write("No records returned.")
Else
Do While NOT Recordset.Eof  
Response.write Recordset("Message")
Response.write "<br>--------------<br>"
Response.write Recordset("MessageOld")
Response.write "<br>"  
Recordset.MoveNext    
Loop
End If

The recordset Message should have displayed the same contents as MessageOld but it didn't. This was the result:


-------------
jhjjghghg


Second, I've tried to test this query

dbConn.Execute("Insert Into CheckFiles (Message, MessageOld) values ('" & Replace(objRecordSet("Message"), "'", "''") & "','" & Replace(objRecordSet("MessageOld"), "'", "''") & "')")

But nothing happen????? Really confused now...

are you using sqloledb or sql native client for your connection provider?

SQL Native Client

Conn.Open "Driver={SQL Native Client};....."

Just released that I didn't mentioned that both of my recordsets (Message and MessageOld) values contain character string, sorry!!!!!

Also I've just tried the following tests:


'----------------------1---------------------------
dbConn.Execute("Insert Into CheckFiles (Message, MessageOld) values ('" & Replace(objRecordSet("Message"), "'", "blablabla") & "','" & Replace(objRecordSet("MessageOld"), "'", "blablabla") & "')")

and received this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][SQL Native Client][SQL Server]Unclosed quotation mark after the character string ''.


'----------------------2----------------------------
dim oldMessage, newMessage
newMessage = objRecordSet("Message")
oldMessage = objRecordSet("MessageOld")
      
dbConn.Execute("Insert Into CheckFiles (Message, MessageOld) values ('" & Replace(objRecordSet("Message"), "newMessage", "blablabla") & "','" & Replace(objRecordSet("MessageOld"), "oldMessage", "blablabla") & "')")

and received this error:
Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'Replace'
'------------------------------------------------

Ok I have some results when I've changed the following connection:

dbConn.Open "Driver={SQL Native Client};Server=servername;Database=databasename;Uid=username;Pwd=password;"

to:

dbConn.Open "Provider=SQLNCLI;Server=servername;Database=databasename;Uid=username;Pwd=password;"


I was able to retreive the Message nvarchar(max) value:

If Recordset.EOF Then
Response.Write("No records returned.")
Else
Do While NOT Recordset.Eof  
Response.write Recordset("Message")
Response.write "<br>--------------<br>"
Response.write Recordset("MessageOld")
Response.write "<br>"  
Recordset.MoveNext    
Loop
End If


Now we are back to the same problem I've had from the begining:

dbConn.Execute("Insert Into CheckFiles (Message, MessageOld) values ('" & Replace(objRecordSet("Message"), "'", "''") & "','" & Replace(objRecordSet("MessageOld"), "'", "''") & "')")

Any Ideas?

Yes.  Use a parameterized SQL Stored procedure, and not inline SQL.  This will eliminate the need to do replacements of double quotes.