Solved

SQL Statment

Posted on 1998-08-06
13
294 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

930 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

8 Experts available now in Live!

Get 1:1 Help Now