LinkBack URL
About LinkBacks
Hi all,
i'm trying to input a string into a table in a database via vba from an excel sheet, my problem is that every so often a string will have a name like " mr 'ben' " which errors as ' is a special character
what do i do?
Hi all,
i'm trying to input a string into a table in a database via vba from an excel sheet, my problem is that every so often a string will have a name like " mr 'ben' " which errors as ' is a special character
what do i do?
Replace ' with '' before you try and add it.
Remember what the dormouse said
Feed your head
unfortunately i'm reading the strings in from a file, there are many thousands of them so going through 1 by one would not really be an option!
Where did I suggest you should do that? You do it in code.
what like:
?
Please Login or Register to view this content.
Last edited by romperstomper; 11-08-2010 at 12:16 PM. Reason: correct tags
More like:
assuming that's how you are doing the update, or do a global replace on the whole sheet first.Please Login or Register to view this content.
hmm that gives me a missing operator error
: syntax error (missing operator) in query expression 'bbc 1'
Typo as you need quotes around the values:
Please Login or Register to view this content.
What if I don't want to remove the special character? I have customers names with ' in, so need to keep this. I also have free text with words like can't, to the ' causes an error. How do a do the VBA to handle this? this is what I have currently, which is part of the code I use to upload from excel to MS SQL. How do I alter this to handle the ' ?
conn.Execute "insert into dbo.Comments (CompanyID, ContactID, ContactName, EnteredBy, Source, Comments) values ('" & sCompanyID & "', '" & sContactID & "', '" & sContactName & "', '" & sEnteredBy & "', '" & sSource & "', '" & sComments & "')"
Nowhere was it suggested you have to remove the apostrophe - you just have to double it in the VALUES statement using code like I posted.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks