Solved

Incorrect syntax

Posted on 2000-03-14
8
164 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:mscala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 50 total points
ID: 2615471
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
 
LVL 14

Expert Comment

by:mcrider
ID: 2615495
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
 

Author Comment

by:mscala
ID: 2615548
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 1

Expert Comment

by:wford
ID: 2615593
why not trap the SQL error and then clear it and correct..then you only need to replace the error record.
0
 
LVL 3

Expert Comment

by:Gunsen
ID: 2615594
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
 
LVL 5

Expert Comment

by:Voodooman
ID: 2615616
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
 
LVL 2

Expert Comment

by:johnny6
ID: 2615717
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
 

Author Comment

by:mscala
ID: 2622399
TimCottee had what i was looking for, thanks!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month4 days, 3 hours left to enroll

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question