Solved

SQL Statment

Posted on 1998-08-06
13
300 Views
Last Modified: 2010-04-30
I am writing a SELECT statment which will include the values in a text box and bound to the data source. My code is as follow.

strSQL = "Select * from table1 where Field1 = '" & txtBox & "'"
datSource.Recordsource = strSQL

What my problem is when I have a ' in the text box which will cause error in the SQL statment.

eg. If the txtBox.Value = "Mary's Car", then the strSQL will be "Select * from Table1 where field1 = 'Mary's Car'"

what is the cure for this?? Thanks
0
Comment
Question by:kachan
[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
13 Comments
 
LVL 6

Expert Comment

by:clifABB
ID: 1468136
Use double quotes:
strSQL = "Select * from table1 where Field1 = " & chr$(34) & txtBox & chr$(34)
datSource.Recordsource = strSQL

0
 
LVL 2

Expert Comment

by:shchuka
ID: 1468137
The way clifABB suggested first of all will not work on most of the databases (e.g. on Oracle or MS SQL), second, even if it will, it'll have problems with a double quote in the field.

I believe, the correct way to do it is to parse the value and insert the backslash ( \ ) before each ' char.
0
 
LVL 6

Expert Comment

by:clifABB
ID: 1468138
While technically it's true that Oracle won't handle this, Jet does.  As long as you aren't using ODBC Passthrough, it will work.
Considering that kachan is using a data control, this will be fine.
0
Technology Partners: 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!

 

Author Comment

by:kachan
ID: 1468139
I tested ClifABB's idea, and it works for ' but not "

for shchuka's idea, I am a little bit confuse... You mean put a \ in front of every char. Does it mean to add \ every for abcdefg??
0
 
LVL 6

Expert Comment

by:clifABB
ID: 1468140
Do you mean that you might have field values with both double quotes and single quotes?

What shchuka suggests is putting a backslash (\) before each single quote.
The code of which might look something like this:
  nCnt1 = 1
  Do Until nCnt1 > Len(sText)
    If Mid$(sText, nCnt1, 1) = "'" Then
      sText = Left$(sText, nCnt1 - 1) & "\" & Mid$(sText, nCnt1)
      nCnt1 = nCnt1 + 1
    End If
    nCnt1 = nCnt1 + 1
  Loop

0
 

Author Comment

by:kachan
ID: 1468141
CLIFABB,

Follows is my actual code, and the udfSQLFix() is the function where I Copy and Paste your code above, but also don't work and rtn with data type mismatch in the criteria.


grdTask.Col = 5
strSQL = "Select remark from dbo_Task where FormaT(date_input,'mm/dd/yyyy hh:mm:ss AMPM') = Format(#" & grdTask.Text

grdTask.Col = 4
strSQL = strSQL & "#, 'mm/dd/yyyy hh:mm:ss AMPM') And Subject = '" & udfSQLFix(grdTask.Text) & "'"

strSQL = strSQL & " And Employee_ID = '" & gcEmployee_ID & "'"
               
datTaskRemark.RecordSource = strSQL
datTaskRemark.Refresh

Pls Advice.

0
 
LVL 6

Expert Comment

by:clifABB
ID: 1468142
By my code above, to which do you refer?  My answer, or the explanation of shchuka's comment?
0
 

Author Comment

by:kachan
ID: 1468143
CLifABB,

I think I buy your explanation, buy still can't fulfill my needs. What do I need to do in order to have both ' and ". This is not under my control, it is really up to the user to input. I really need to sort this out. Thanks
0
 
LVL 6

Expert Comment

by:clifABB
ID: 1468144
I understand.  I will talk with my DBA to see what he says on the matter.  If I can't come up with a viable solution, you should reject my answer and give others a try.
0
 
LVL 2

Expert Comment

by:dabellei
ID: 1468145
Function apostrophe(texte As String)
      temp_texte$ = texte$
      lo = Len(temp_texte$)
      x = 0
      Do While x < lo
         x = x + 1
         If Mid$(temp_texte$, x, 1) = "'" Then
             temp_texte$ = Mid$(temp_texte$, 1, x) + Mid$(temp_texte$, x)
             lo = lo + 1
             x = x + 1
         End If
      Loop
      apostrophe = temp_texte$
End Function


Here's the code i use on every program wich access a database in SQL server

You out it in a module and call it from every where

text1$ = apostrophe(text1.text)
and then use the text1$ value for update or insert.

The thing is that the ' is terminating your string so it generate an error in SQL  you just have to had a ' before.

Ex.:   true value is: O'Connel
       using apostrophe
       new value is O''Connel
0
 

Author Comment

by:kachan
ID: 1468146
dabellie,

if the textbox1.value  = Testing 3''" ' two ' follows by one "

even your above method will not solve the problem... Your method will elimiate the error but will not return any record in the query.
0
 
LVL 1

Accepted Solution

by:
tgambee earned 50 total points
ID: 1468147
Instead of trying to put a character there, put a wildcard.  It might not be bullet proof (ie. Mc?Intrye : would return : Mc'Intrye and McIIntrye)  but it would return records at least.


0
 

Author Comment

by:kachan
ID: 1468148
I think this will best bet, and I will take your suggestion. Many Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 9 hours left to enroll

623 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