Replace(String, chr(34), """) won't work

[streborr]

I don't know why, but now it doesn't work!

mTitle = replace(mTitle, "'", "|&quot
mTitle = replace(mTitle, vbcrlf, &quot;<br>&quot
mTitle = Replace(mTitle, Chr(34), &quot;&quot;&quot
mTitle = Server.HTMLEncode(mTitle)

message = replace(message, &quot;'&quot;, &quot;|&quot
message = replace(message, vbcrlf, &quot;<br>&quot
message = Replace(message, chr(34), &quot;&quot;&quot
message = Server.HTMLEncode(message)

When I try to Insert into db this error comes up:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Field 'forum.mTitle' cannot be a zero-length string.

/testdb/reply.asp, line 229

line 229 is this:

Set oRS = oConn.Execute(&quot;INSERT INTO forum (mTitle, Message) VALUES (''&quot; & mTitle & &quot;', '&quot; & Message & &quot;')&quot

When I use (&quot in the mTitle or Message I get the error, it used to work, I can't figure out why it stopped working.

 

[streborr]

Let's see if I can do this right.

for some reason I can't get this to show properly so I added spaces.
this is what was supposed to be between the quotes:

& # 34 ;

 

[Genimuse]

Did you copy and paste your line 229? Here's what it looks like (putting [ code ] tags around it so that it formats better):

Set oRS = oConn.Execute("INSERT INTO forum (mTitle, Message) VALUES (''" & mTitle & "', '" & Message & "')")

The double single quotes right after &quot;VALUES (&quot; don't make any sense. The line should be:

Set oRS = oConn.Execute("INSERT INTO forum (mTitle, Message) VALUES ('" & mTitle & "', '" & Message & "')")

 

[streborr]

That was just a mistake on my part when I posteds the code. I shortened the actual Insert statement for readability. Information does get inserted into the db as long as I don't use (&quot in the Title or Message.
The proplem lies with the replace statement. I'm trying to allow (&quot to be submitted in the Title and Message and be inserted into the db(Access). It was working and now it doesn't.
I also tried using the [ code ] [/ code ] thing to show the text in the replace statement, but had no luck.

Here's what I have:

replace(mTitle, chr(34), &quot;-----&quot

The &quot;-----&quot; is actually &quot; & # 34 ; &quot; without the blanks spaces. Ijust can't get that part to display correctly in this post. It displays as &quot;

Anyway, I can't seem to replace quotes the in the Title or Message.

 

[Veep]

Why don't you just go into the database, open the table in design view, select the mTitle field and change the &quot;Allow zero length string&quot; property?

 

[clarkin]

You can escape double quotes in VBscript by, hem, doubling them . SQL Server has no problems with dbl quotes in strings as it uses single quotes to mark start/end.

So lets say you wanted to insert this message:
Look at the big &quot;bad&quot; wolf.

message = "Look at the big ""bad"" wolf."

Instead of using a SQL statment built on the fly, build the sql string you want to run & then response.write it. Then test it in Query Analyser. Once its working how you like it, make your page run it.

Let us know what sqlString your code is currently making for you.


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].