Incorrect syntax

Hi,

I need to write a routine, that will check a field that is Alpha. I am writing records from a paradox db, to an sql db. I am using VB6.
A field is in the form as follows: The field name is Account Holder's Name, and the field inside is O'Connaly. When i insert this field into Sql, if complains because there is an apostrophe in the field '. Now i need to search each record for an apostrophe, and replace it with a space.

Can anyone help me with this, please!
Thanks.
mscalaAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Double the apostrophe using
strField = Replace(strField,"'","''")

Then you should be able to insert it and retain the apostrophe character, if you definitely want to remove it then

strField = Replace(strField,"'"," ") will work to do this.
0
 
mcriderCommented:
You can use this function to see if a string is Alpha... It currently checks A-Z and space, but you can add any other characters you want to be valid to the 1st case statement... If you add, use the ASC number of the character...

Function IsAlpha(Source As String) As Boolean
    Dim iVal As Long
    IsAlpha = True
    For iVal = 1 To Len(Source)
        Select Case Asc(UCase(Mid$(Source, iVal, 1)))
            Case 65 To 90, 32 'CHECK FOR A-Z and space
            Case Else
                IsAlpha = False
                Exit For
        End Select
    Next iVal
End Function



Then you can use it like this:

If IsAlpha(MyString) = True then
    'it's alpha, do something
Else
    'it's not alpha, do something else
End If



Cheers!®©
0
 
mscalaAuthor Commented:
TimCottee

The line you are using is good, and very simple. But when i use this code

Do Until RsReadEntry.EOF
strField = Replace(strField, "'", " ")
RsReadEntry.MoveNext
Loop

I have to check all the records that i am going to insert. And for 2000 records, it is taking more than 5 mins, which is way too long, can u not make the time less in some way?

Thanks!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
wfordCommented:
why not trap the SQL error and then clear it and correct..then you only need to replace the error record.
0
 
GunsenCommented:
I would preffer to keep the single quote intact.
Try forcing the SQL statement by replacing the ' with '' or escaping it with \'
strField = Replace(strField,"'","''")  ' double quotes (works for oracle)
strField = Replace(strField,"'","\'")  ' escaped
0
 
VoodoomanCommented:
Parsing files like this is pretty boring - why do programmers allow users to enter O'Connelly anyway! I dont!

To make it quicker do a Select on the Field to find where the apostrophes are.

i.e. In Access
SELECT Accounts.Name
FROM Accounts
WHERE ((InStr([Name],"'")>"0"))

In VB

data1.datasource="SELECT Accounts.Name
FROM Accounts
WHERE ((InStr([Name],''')>"0"))"

NB in VB you need single quotes around the apostrophe.

5 mins to loop thru 2000 recs - upgrade your pc!

0
 
johnny6Commented:
mscala:
      I think this is what you are looking for:


PUBLIC FUNCTION UnQuote(txt as string) as string
'
DIM WORK as STRING
DIM ACHAR AS STRING
DIM NDX AS INTEGER
'
WORK=""
FOR NDX = 1 TO LEN(TXT)
ACHAR = MID(TXT, NDX, 1)
IF ACHAR = CHR(34) OR ACHAR = "'" THEN ACHAR=ACHAR+ACHAR
WORK = WORK + ACHAR
NEXT NDX
'
UnQuote = WORK
'
END FUNCTION

In your SQL code:

SQL = "INSERT INTO .... VALUES( '" & UnQuote( Field3 ) & "')"

Your data will land in the database as "O'Connaly" just like you want it. No conversion is required when retreiving data.

John
0
 
mscalaAuthor Commented:
TimCottee had what i was looking for, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.