Hi all,

I recon this is really simple but its a problem that keeps coming up and i use a replace function to remove them, but this time i need apostrophes in my data field

When you insert via SQL if a ' is contained withing a text value while inserting into a database it will break the sql statement as it thinks its the end of the data for that data field.

Is there a way i can wrap my fields to not kill the sql statement if a ' is contained within a variable.

eg. say i was inserting a company name
FYI: im using mySQL database

Cheers

Tim

TDA

__________________
TDA

I run this function around ALL text fields on insert or update, it will fix your problem:

Function StoreText(theText)
    StoreText = ""
    on error resume next
    StoreText = CStr(theText)
    if (len(StoreText) > 0) Then
     StoreText = Replace(StoreText, """", """, 1, -1, 1)
     StoreText = Replace(StoreText, "'", "''", 1, -1, 1)
     StoreText = Replace(StoreText, vbCrLf, "<BR>", 1, -1, 1)
    end if
End Function

Use it like so:
strAdd = "INSERT INTO tblCompany SET CompanyName = '"& trim(storeText(CompanyName)) &"';"
objConn.Execute(strAdd)

FYI: running the trim function and using trailing semi colons are also very good habbits to get into

Wind is your friend
Matt

Thanks matt, thats definately a consistant and good way of doing it!

Also from your example learnt about the Start, Count, Compare parameters of the replace function (had no idea).

thanks again

Tim :)

TDA

You are in Melbourne, what company do you work for? I am in Sydney working for the Framegroup

Wind is your friend
Matt

Hi Matt,

I freelance and sub contract to The Aussie Internet.

Tim :)

TDA