Allowing quotes with out having a extraction error?

Question

Post reply

Allowing quotes with out having a extraction error?

Dallas Richards

SSC Rookie

Points: 36
More actions
June 14, 2005 at 12:54 pm

#186262

Ok, this is what I have. I have a form that collects information about press events. The part I'm having trouble with is when I transcribe a press clip with single quotes (') I get this error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'.
When I use double quotes (") it prints everything out up to the first double quote it comes across.
I am using the nvarchar(4000) datatype.
Also, VBS and ASP to extract the information.
How do I fix this?
Much appreciated,
Dallas

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Sounds like dynamic sql to me.

Can you post the code you are using the create the query and execut it.

Pre-hint... double quote your quotes (' becomes '').

Dallas Richards SSC Rookie Points: 36 More actions · Answer 2

does this help?

in my select statement, the publish article case runs like this:

-------------------------------------

subGetFormValues

subAddArticle

-------------------------------------

sub subGetFormValues

title = Request.QueryString("t")

pub_date = Request.QueryString("pd")

news_source = Request.QueryString("ns")

article_body = Request.QueryString("ab")

end sub

sub subAddArticle

'db connect code defined here

Set CommandLine = Server.CreateObject("ADODB.Command")

Set CommandLine.ActiveConnection = connection_obj

strQ ="INSERT INTO press " &_

"(source, publication_date, article_title, body_text)" &_

" VALUES " &_

"(" &_

"'" & news_source & "', " &_

"'" & pub_date & "', " &_

"'" & title & "', " &_

"'" & article_body & "')"

CommandLine.CommandText = strQ

CommandLine.Execute strQ

end sub

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

As I explained you'd need to replace all the (') with (''). Or you could create a stored proc and avoid the problem altogether. It would also shield you from sql injection attacks (The Curse and Blessings of Dynamic SQL

)

here's the stored proc :

CREATE PROCEDURE dbo.Demo @Source as varchar(50), @publication_date as varchar(50), @article_title as varchar(50), @body_text as varchar(50)

AS

SET NOCOUNT ON

INSERT INTO dbo.press

(source, publication_date, article_title, body_text)

VALUES(@Source, @publication_date, @article_title, @body_text)

SET NOCOUNT OFF

GO

Here's an exemple in vb6 (will need some tweaking for asp, but you'll get the idea)

procedure to call the procedure :

(you'll need to find the matching values of the enumerations like adParamInput = 1 and replace them)

Private Function exec_Demo(ByVal Source As String, ByVal publication_date As String, ByVal article_title As String, ByVal body_text As String, Optional ByRef ReturnValue As Integer) As Integer

On Error GoTo Gestion

Dim MyCmd As ADODB.Command

Set MyCmd = New ADODB.Command

MyCmd.CommandText = "dbo.Demo"

MyCmd.CommandType = adCmdStoredProc

Dim MyParam As ADODB.Parameter

Set MyParam = New ADODB.Parameter

MyParam.Name = "@Source"

MyParam.Value = Source

MyParam.Size = 50

MyParam.Direction = adParamInput

MyParam.Type = adVarChar

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@publication_date"

MyParam.Value = publication_date

MyParam.Size = 50

MyParam.Direction = adParamInput

MyParam.Type = adVarChar

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@article_title"

MyParam.Value = article_title

MyParam.Size = 50

MyParam.Direction = adParamInput

MyParam.Type = adVarChar

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@body_text"

MyParam.Value = body_text

MyParam.Size = 50

MyParam.Direction = adParamInput

MyParam.Type = adVarChar

MyCmd.Parameters.Append MyParam

MyCn.Open

MyCmd.ActiveConnection = MyCn

MyCmd.Execute

MyCn.Close

Set MyParam = Nothing

Set MyCmd = Nothing

Exit Function

Gestion:

ErrHandler ModuleName, Me.Name, "exec_Demo", Err

MsgBox Err.Description & " : " & Err.Number

End Function

Dallas Richards SSC Rookie Points: 36 More actions · Answer 4

Thanks, I'll give it a whirl. I'll probably be back with questions

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

You'd be the first to understand all this by himself.

Up to the challenge ?

Dallas Richards SSC Rookie Points: 36 More actions · Answer 6

I just don't know why is has to be that hard to insert quotes... You'd think that someone would have thought about a way to fix it. heh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

It's not hard

try

--fail

insert into SomeTable (SomeCol) values ('This fails : '')

--works

insert into SomeTable (SomeCol) values ('This works : ''')

Dallas Richards SSC Rookie Points: 36 More actions · Answer 8

I don't know if this makes a difference but I forgot to add that the text goes into a textarea form element.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Not sure of what you're talking about. Can I see some code?

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 10

text area is web/html control. It is a big text box like the one we use to type body of EMail message or messages for Forums. There are chances that users can type single quotes and that will lead to error. As previous poster {I think it is Remi } suggested use a stored procedure - {good idea} or replace (') with ('') {will make it to work}

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

I know what a textarea is . I haven't coded in html for a while but I remeber the basics .