searching an ntext field in a table.

[cossiboon]

I have a table that contains an ntext data type field. I need to do a search on that field to see if a record exists in that table. I'm running a stored procedure, but the data may have a single quote in it. I've tried two ways to try to run the query/stored procedure within vbscript/ASP.

1.
set rstQuestionText = server.CreateObject("ADODB.Recordset&quot
with rstQuestionText
.CursorLocation = adUseClient
.Open "imagecareinc.get_question_text_from_text('" & strQuestionText & "')", cnn,,,adCmdStoredProc
end with

Obviously, if there is a single quote within the strQuestionText variable, it bombs.

So I tried using the command object.

2.
dim objCmd, objParam
set objParam = server.CreateObject("ADODB.Parameter&quot
set objCmd = server.CreateObject("ADODB.Command&quot
objCmd.ActiveConnection = cnn
objCmd.CommandText = "imagecareinc.get_question_text_from_text"
objCmd.CommandType = adcmdstoredproc
objParam.Attributes = adFldLong
objParam.Name = "QuestionText"
objParam.Type = adLongVarWChar
objParam.Direction = adParamInput
objParam.Size = 16
objParam.value = strQuestionText
objCmd.Parameters.Append objParam
objCmd.Parameters.Append objCmd.CreateParameter ("QuestionID",adNumeric,adParamOutput)
objCmd.Execute

This gives me an error "Invalid scale value"???

Anybody have any ideas?

 

[tlbroadbent]

Simply convert the single quote to two single quotes.

'can't' should be sent to SQL Server as 'can''t'. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[cossiboon]

It's one of those things I should have known. I can't believe that!!! Thanks for the help!