VB and Database Place Holders

[lbaker78]

Hi All,

I am trying to write some info to an Access Db, it all works fine until the data it's adding contains a ' (single apostrophe!). It then bombs out because now the command line is fragmented.

I know in perl (and other languages I believe) you can use place holders to get around this issue where you substitute a ? and apply the data after the command line and as if by magic its all inserted correctly!

How can I do this in VB? The line of code I am using that is relevant is:

With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT * FROM library WHERE URL = '" & curfile & "';"
    .CommandType = adCmdText
End With

As I mentioned above, this works fine until the curfile variable contains a ' (for example - I'm not liking this)

TIA!

 

[earthandfire]

You just need to double the apostrophe.


Hope this helps.

 

[bjd4jc]

Please see faq709-1526 on why it is not a good idea to just double the apostrophe in production applications that have user input.

 

[lbaker78]

Like this?

CommandText = "SELECT * FROM library WHERE URL = ''" & curfile & "'';"

That doesnt seem to work either...

 

[lbaker78]

bjd4jc - thats exactly the sort of thing I want to avoid! - in this instance, curfile is created by the app, but as it just pulls up a filename, that file name could contain all sorts of nasties!

 

[RoyVidar]

[tt]... WHERE URL = '" & replace(curfile,"'","''") & "'"[/tt]

- but the faq/method bjd4jc linked to is better.

Roy-Vidar

 

[lbaker78]

Thanks Guys! Much aprreciated!