Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL Statment

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
kachan
Asked:
kachan
1 Solution
 
clifABBCommented:
Use double quotes:
strSQL = "Select * from table1 where Field1 = " & chr$(34) & txtBox & chr$(34)
datSource.Recordsource = strSQL

0
 
shchukaCommented:
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
 
clifABBCommented:
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!

 
kachanAuthor Commented:
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
 
clifABBCommented:
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
 
kachanAuthor Commented:
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
 
clifABBCommented:
By my code above, to which do you refer?  My answer, or the explanation of shchuka's comment?
0
 
kachanAuthor Commented:
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
 
clifABBCommented:
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
 
dabelleiCommented:
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
 
kachanAuthor Commented:
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
 
tgambeeCommented:
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
 
kachanAuthor Commented:
I think this will best bet, and I will take your suggestion. Many Thanks
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now