Solved

SQL Statment

Posted on 1998-08-06
13
293 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
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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now