[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

sql

When i try to read some name with ' in the name it gives an error 3075, that is using SQL in VB6 e.g" the name" O'sullivan", how can I overcome this problem. The statement I use is <<<<  Set RsCheckIfMemExist = dbTo.OpenRecordset("select surname, firstname from tblmembers where surname like '" & rsFrom!surname & "' and firstname like '" & rsFrom!firstname & "'", 2)  >>>>
0
sml41
Asked:
sml41
  • 4
  • 3
  • 2
  • +2
1 Solution
 
nico5038Commented:
Two options:
1) Switch quotes
<<<<  Set RsCheckIfMemExist = dbTo.OpenRecordset('select surname, firstname from tblmembers where surname like "' & rsFrom!surname & '" and firstname like "' & rsFrom!firstname & '"', 2)  >>>>
2) Replace all single quotes in the name by a double single quote. (Making a qoute duplication function would be the easiest way.

0
 
MarineCommented:
say you have to enter this search creteria into a textbox and then build your select upon it.This will work.

surName = Replace(text1,"'","''")
then you can build your select
Select surname .......
0
 
Éric MoreauSenior .Net ConsultantCommented:
Your complete query should read like this:
Set RsCheckIfMemExist = dbTo.OpenRecordset("select surname, firstname from tblmembers where surname like '" & replace(rsFrom!surname, "'", "''") & "' and firstname like '" & replace(rsFrom!firstname, "'", "''") & "'", 2)  

Since you use the LIKE operator, you need to place the wildcard (or use the = operator instead):
Set RsCheckIfMemExist = dbTo.OpenRecordset("select surname, firstname from tblmembers where surname like '" & replace(rsFrom!surname, "'", "''") & "*' and firstname like '" & replace(rsFrom!firstname, "'", "''") & "*'", 2)  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sml41Author Commented:
nico5038 , i have tried that, it failed in the 1st quote.
0
 
nico5038Commented:
correct sml41,

I screwed up with COBOL. That's supporting the "quote-switch", sorry.

So just the replace single quote two singles quotes should do it ! (like marine suggests with REPLACE.

Success!
0
 
Éric MoreauSenior .Net ConsultantCommented:
Your query must definetly be surrounded by double quotes! Look at the query I gave you.
0
 
sml41Author Commented:
emoreau
 
I have used your line, the replace works ok.

thanks to all
0
 
nico5038Commented:
I gave the basic solution,
Marine added the REPLACE
And emoreau typed it for you !

That's cooperation !
 
0
 
johnny6Commented:
sml41:

I think this is what you are looking for:

Public Function RemoveApostrophe(text As String) As String
    Dim Temp As String
    Dim ASymbol As String
    Dim i As Integer
    Temp = ""
    For i = 1 To Len(text)
    ASymbol = Mid(text, i, 1)
    If ASymbol = Chr(34) Or ASymbol = "'" Then ASymbol = ASymbol + ASymbol
    Temp = Temp + ASymbol
    Next i
    RemoveApostrophe = Temp
End Function

In your SQL code do the folowing:

strSQL = "Insert Into ... Values( '" & RemoveApostrophe( Field1 ) & "')"

Your data will end up in the database as "O'sullivan". No conversion is required when retreiving data.

John
0
 
sml41Author Commented:
nico, sorry i did not realise, what do u suggest, everyone is great!!
0
 
nico5038Commented:
sml41,

I normally give the direction(s) of a solution as finding the working code by yourself is often the most rewarding and gives the best learning experience.

If you know now the way to handle "embedded" quotes (as I assume you do), that's for me rewarding enough!
(It went wrong because your string started with a single qoute and the textfield contained one that terminated your first qoute, thus corrupting the SQL-string)

I learned using the Replace, as I normally use the 'traditional' INSTR and MID commands, so I also gained from this question !

Success with your program !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now