Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Incorrect syntax

Posted on 2000-03-14
8
Medium Priority
?
168 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 150 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

927 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