Solved

Incorrect syntax

Posted on 2000-03-14
8
160 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
maro to copy and paste from one worksheet to another based on a condition 11 63
Copy a row 12 68
MsgBox 4 71
checkbox to hide entire section 10 58
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

679 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