Solved

Incorrect syntax

Posted on 2000-03-14
8
159 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

856 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