Handling Special Characters

[KaiserSouza]

Hi,

I'm using a VB app to connect to an Access database via odbc. I'm trying to transfer data from one access database to another using 'company_name' as the index field for both tables. The problem is, company_name may contain special characters i.e. &, literal comma, etc. This is wreaking havoc to the sql statement i'm trying to pass. here's part of my code:

sql = "Select Company_Name from COMPANY"


objconn1.Open "DB1"
objrs1.Open sql, objconn1
objconn2.Open "DB2"

objrs1.MoveFirst
While Not objrs1.EOF
Set txtCompany.DataSource = objrs1
txtCompany.DataField = "company_name"
sql2 = "Select company.company_name, company.company_ID from company where (((company.Company_Name) =" & Chr(34) & Trim(txtCompany.Text) & Chr(34) & "));"
txtCID.Text = sql2
objrs2.Open sql2, objconn2


If Not objrs2.EOF Then
'this is where I will copy company_id from db2 to db1

End If

Wend


I've passed the string literal in sql2 in MS-Access' query wizard and the sql statement was accepted by access (I substituted the ascii values with proper characters of course.) If the company name is for example "Sanford & son, Co" The app dies a horrible death because of the comma and the apersand.

any help would be greatly appreciated.

Thanks!

 

[RoyVidar]

Hi!

Double quoting, as you do here, isn't recognized the same way in ADO as in DAO (or within the Access QBE), where this would probably have worked.

One way of dealing with this, is to to replace single occurances of special characthers with double, i e create a function doing such with the replace function:

[tt]dim sCrit as String
sCrit = Trim$(txtCompany.Text)
sCrit = replace(sCrit,"&","&&")
sCrit = replace(sCrit,'"&","''")
...[/tt]

- but I'm not sure how this would work with ampersand within the string (or I believe it won't work with ampersand and comma)

Another way, if apliccable, is to open the complete recordset, and use the .Find method on it (or .Filter property) which allows something similar to DAO double quoting using the hash (#) character:

[tt] sql2 = "Select company.company_name, company.company_ID from company"
objrs2.Open sql2, objconn2
if not objrs2.eof and not objrs2.bof then
with objrs2
.find "Company_Name =#" & Trim(txtCompany.Text) & "#"
if not .eof then
... [/tt]

Roy-Vidar

 

[KaiserSouza]

Hi Roy,

Thanks for your reply. I've tried the double single-quotes and that worked for strings like "O'Reilly" and such that when I doble the sigle quotes to "O''Reilly", it works like a charm. However, this method of doubling up doesn't work for Ampersands and commas. I was wondering if there is another way of doing it.

Would using DAO solve this issue? or ADO but DSN-Less?

Thanks, and I appreciate your thoughts.

Kaiser

 

[RoyVidar]

Yes, I did mention my doubts obout that too;-)

DAO and double quoting would normally work. DSN less ADO, don't know, it's supposed to be faster, but I think this is ADO characteristics, and not whether or not you go thru the ODBC layer. But again, this I'm not sure of.

But you can't use the .Find method or .Filter property? I've found that works.

BTW - Welcome to Tek-Tips! Here's a little faq on how to get the most out of the membership faq181-2886. Didn't see it yesterday, but you're using VB against Access. This is Access Query & sql forum, where we mainly deal with "pure" Access issues. Perhaps some fora where this might be addressed with VB knowledge might assist further? For instance the VB5/6 forum (forum222) or the VB (Microsoft) Databases forum (forum709).

Another twist, is using the parameters collection, as in this faq faq709-1526, haven't used that, so I don't know how it works with Access, but it could be worth a try. Else you could try a keyword search in one of the two mentioned fora.

Roy-Vidar

 

[KaiserSouza]

Hi Roy,

Thanks for the tip. I actually just imported the table from one to the other and worked everything within access. Well, whadya know? Access figured out the special chars all by itself.

Sometimes I try to overcomplicate matters