Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Statment

Posted on 1998-08-06
13
Medium Priority
?
301 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
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.

 

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 150 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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

688 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