Hello, I want to insert the next 1

[MARELUAD]

Hello,

I want to insert the next string from ACCESS97 to Oracle
"insert into test (PROJECTNUMBER, PROJECTNAME)
VALUES(" & number & ",'" & name & "')"

number = 12345
name = foto's

Now I get the error ORA-01756

Thanks.

 

[Spikmeister]

I'll put money on it being because there's a quote mark in your string.

That would turn it into:

"insert into test (PROJECTNUMBER, PROJECTNAME)  
 VALUES(12345,'foto's')"

Which wouldn't work.

Try removing the quote first and see if that works.

 

[MARELUAD]

The problem is the quote in: foto's
But how can I insert this string?

 

[Spikmeister]

when inserting it it'll need to be two quote marks, ie:

''

Although this might cause you problems when using it later.

For a system I worked on we replaced all quote marks in data with something we could later replace back (<Q>) to avoid errors occuring.

Either that or you just remove all the quote marks.

Which ever way you do it you'll probably need to do a REPLACE.

Which side is your code on to copy the data, Access or Oracle?

 

[SantaMufasa]

(Note: the best solution would come from Oracle if they would cause double quotes to have the same functionality as singe quotes. Hmmm.)

Another solution we have used to advantage is to replace all &quot;single quotes&quot; (') [ascii 39] with a &quot;backward quote&quot; (`) [ascii 96], and to ensure that your future inputs also filter for that replacement. The backward quote looks close enough to the forward quote that no seems to notice or complain.