SQL Server nText insert / update

Many developers have asked how to insert data into an nText field in SQL Server with ADO. Most try to use standard string SQL statements and run into problems with special characters.

Many developers have asked how to insert data into an nText field in SQL Server with ADO. Most try to use standard string SQL statements and run into problems with special characters.

In order to do it properly, you'll really need to use the ADO command object. Here's a little sample of the syntax with a stored procedure name spPost and the nText column being @bodyText:

Dim lRecs
Dim moADOCon
Dim moADOCom

Set moADOCon = Server.CreateObject("ADODB.Connection")
Set moADOCom = Server.CreateObject("ADODB.Command")

moADOCon.Open "your connection string"

With moADOCom
.ActiveConnection = moADOCon
.CommandText = "spPost"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,0)
.Parameters.Append .CreateParameter("@PostID", adInteger, adParamInput, , msPostID)
.Parameters.Append .CreateParameter("@bodytext", adVarWChar, adParamInput, 1073741823, msBodyText)
.Execute lRecs, , adExecuteNoRecords
End With

moADOCon.Close
Set moADOCom = nothing
Set moADOCon = nothing


SQL Server Stored procedure code

CREATE PROCEDURE spPost
(
@PostID int,
@BodyText ntext
) AS

update MyTable
Set BodyText = @BodyText
Where PostID = @PostID


GO


Submission Date:  9/23/2005 2:58:49 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com