Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL query with apostrophe 4
- Thread starter rgordley
- Start date
-
1
- #2
"select * from sometable where name = 'O''conner' " <br>
<br>
or<br>
<br>
"select * from sometable where name = " & chr(39) & "O'conner" & chr(39) <br>
<p>nick bulka<br><a href=mailto:nick@bulka.com>nick@bulka.com</a><br><a href= > </a><br>
<br>
or<br>
<br>
"select * from sometable where name = " & chr(39) & "O'conner" & chr(39) <br>
<p>nick bulka<br><a href=mailto:nick@bulka.com>nick@bulka.com</a><br><a href= > </a><br>
-
2
- #4
RoyLofquist
Programmer
The function SqS returns a proper SQL expression for any string - e.g. SqS("O'Connor"
-> 'O''Connor'<br>
<br>
SQL= "Select * from Tbl where Name = " & SqS("O'Connor"<br>
<br>
Cut and paste this code into VB IDE to read it - " and '' look the same in this font.<br>
<br>
Public Function SqS(Element) As String<br>
SqS = "'" & FindAndReplace(CStr(Element), "'", "''" & "'"<br>
End Function<br>
<br>
Public Function FindAndReplace(StringToExamine As String, Replace As String, WithThis As String) As _<br>
String<br>
Dim Work As String<br>
Dim Found As String<br>
Dim ix As Integer<br>
Work = StringToExamine<br>
Do While Len(Work) > 0<br>
ix = ThisInThatText(Replace, Work)<br>
If ix = 0 Then<br>
Found = Found & Work<br>
Work = ""<br>
Else<br>
Found = Found & Left$(Work, ix - 1) & WithThis<br>
Work = Mid$(Work, ix + Len(Replace))<br>
End If<br>
Loop<br>
FindAndReplace = Found<br>
End Function<br>
<br>
Public Function ThisInThatText(ThisString As String, InThatString As String, Optional _<br>
StartPositionInThatString = 1) As Long<br>
ThisInThatText = InStr(StartPositionInThatString, InThatString, ThisString, vbTextCompare)<br>
End Function<br>
<br>
<p>Roy Lofquist<br><a href=mailto:roylofquist@msn.com>roylofquist@msn.com</a><br><a href= > </a><br>
-> 'O''Connor'<br><br>
SQL= "Select * from Tbl where Name = " & SqS("O'Connor"
<br><br>
Cut and paste this code into VB IDE to read it - " and '' look the same in this font.<br>
<br>
Public Function SqS(Element) As String<br>
SqS = "'" & FindAndReplace(CStr(Element), "'", "''"
& "'"<br>End Function<br>
<br>
Public Function FindAndReplace(StringToExamine As String, Replace As String, WithThis As String) As _<br>
String<br>
Dim Work As String<br>
Dim Found As String<br>
Dim ix As Integer<br>
Work = StringToExamine<br>
Do While Len(Work) > 0<br>
ix = ThisInThatText(Replace, Work)<br>
If ix = 0 Then<br>
Found = Found & Work<br>
Work = ""<br>
Else<br>
Found = Found & Left$(Work, ix - 1) & WithThis<br>
Work = Mid$(Work, ix + Len(Replace))<br>
End If<br>
Loop<br>
FindAndReplace = Found<br>
End Function<br>
<br>
Public Function ThisInThatText(ThisString As String, InThatString As String, Optional _<br>
StartPositionInThatString = 1) As Long<br>
ThisInThatText = InStr(StartPositionInThatString, InThatString, ThisString, vbTextCompare)<br>
End Function<br>
<br>
<p>Roy Lofquist<br><a href=mailto:roylofquist@msn.com>roylofquist@msn.com</a><br><a href= > </a><br>
- Thread starter
- #5
VB400, <br>
I couldn't either, but using that idea I came up with this<br>
<br>
customername="O" & chr(39) & chr(39) & "Connor"<br>
<br>
strSQL="select * from sometable where name = '" & customername & "' "<br>
<br>
This works....so thanks nick for getting me on the right track.<br>
I couldn't either, but using that idea I came up with this<br>
<br>
customername="O" & chr(39) & chr(39) & "Connor"<br>
<br>
strSQL="select * from sometable where name = '" & customername & "' "<br>
<br>
This works....so thanks nick for getting me on the right track.<br>
<br>
rgordley,<br>
<br>
You are on the same track as Roy. However, Roy's solution works for any name with an apostrophe (not just O'Conner). Additionally, if the name has more than one apostrophe (unlikely as it may be), it will take care of that as well. <br>
<br>
I assume that at the time you're running the query you don't know the name (O'Conner, O'Brian, etc.)-- in which case Roy's code will fit in perfectly.<br>
<br>
Regards,<br>
Tarek
rgordley,<br>
<br>
You are on the same track as Roy. However, Roy's solution works for any name with an apostrophe (not just O'Conner). Additionally, if the name has more than one apostrophe (unlikely as it may be), it will take care of that as well. <br>
<br>
I assume that at the time you're running the query you don't know the name (O'Conner, O'Brian, etc.)-- in which case Roy's code will fit in perfectly.<br>
<br>
Regards,<br>
Tarek
- Thread starter
- #8
For ASP programmers :<br>
IN VBScript you can also use the replace(string,"'","''" command...<br>
<br>
Regards<br>
<p>Peter Van Eeckhoutte<br><a href=mailtoeter.ve@itinera.be>peter.ve@itinera.be</a><br><a href= > </a><br>
IN VBScript you can also use the replace(string,"'","''"
command...<br><br>
Regards<br>
<p>Peter Van Eeckhoutte<br><a href=mailto
eter.ve@itinera.be>peter.ve@itinera.be</a><br><a href= > </a><br> Some people are making this alot harder then it has to be. OK create a module. Put this code in it.
Public Function FindChr(strTemp As String) As String
FindChr = Replace(strTemp, "'", Chr(34), 1)
End Function
Public Function GetBackChr(strTemp As String) As String
GetBackChr = Replace(strTemp, Chr(34), Chr(39))
End Function
The FindChr function will replace the '
the GetBackChr will get back the '
For example here I remove the ' for the SQL Query then set it back
txtDescription = modUtilities.FindChr(txtDescription)
m_objNewDrawing.sDescription = txtDescription
txtDescription = modUtilities.GetBackChr(txtDescription)
Public Function FindChr(strTemp As String) As String
FindChr = Replace(strTemp, "'", Chr(34), 1)
End Function
Public Function GetBackChr(strTemp As String) As String
GetBackChr = Replace(strTemp, Chr(34), Chr(39))
End Function
The FindChr function will replace the '
the GetBackChr will get back the '
For example here I remove the ' for the SQL Query then set it back
txtDescription = modUtilities.FindChr(txtDescription)
m_objNewDrawing.sDescription = txtDescription
txtDescription = modUtilities.GetBackChr(txtDescription)
Hi everybody!
I was experiencing the same problem of a single quote in a query string. Although the problem is solved in my application by replacing ( ' ) character,But it has now created another problem in reporting point of view. I am using Crystal reports to prepare the reports. Now problem is that my reports get data from that table in which my application saves the data. so when i get a report the data is printed like
O"Connor
instead of
O'Connor
And it is a lot of work to format every string in the report. So isnt there any way that i can save (') based string as it is using a query string. I can do that by using a recordset and then saving data using the recordset, but that method does not suite my application.
So if anybody has a real solution to that then plz do reply
Thanks in advance
I was experiencing the same problem of a single quote in a query string. Although the problem is solved in my application by replacing ( ' ) character,But it has now created another problem in reporting point of view. I am using Crystal reports to prepare the reports. Now problem is that my reports get data from that table in which my application saves the data. so when i get a report the data is printed like
O"Connor
instead of
O'Connor
And it is a lot of work to format every string in the report. So isnt there any way that i can save (') based string as it is using a query string. I can do that by using a recordset and then saving data using the recordset, but that method does not suite my application.
So if anybody has a real solution to that then plz do reply
Thanks in advance
Hi,
Most of you have probabply allready seen this:
But if you want to use the ADO .find method and you have more that on single quote (apostrophe) in your string, then you're out of luck:
:-( sunaj
Most of you have probabply allready seen this:
But if you want to use the ADO .find method and you have more that on single quote (apostrophe) in your string, then you're out of luck:
:-( sunaj
-
1
- #14
Try this. It works fine if you have multiple ' or " , but will blow up if your string has a # in it
"select * from sometable where name = #O'conner#" or
"select * from sometable where name = #O'conner's#"
Tim
Remember the KISS principle:
Keep It Simple, Stupid!
"select * from sometable where name = #O'conner#" or
"select * from sometable where name = #O'conner's#"
Tim
Remember the KISS principle:
Keep It Simple, Stupid!
I have a nice solutions if using ADO and its low on processing power:
This will solve all possible xcharacter issues, and doesn't require a find and replace operation(s) or multiple string concatenation. A side effect is that you can also set
and create a temporary stored procedure by just replacing parameters.
This model also works with Inserts/Updates/Deletes very nicely. It is a VERY flexible model if you are not using DB Stored Procedures.
James
James Culshaw
jculshaw@active-data-solutions.co.uk
Code:
strSQL = "SELECT * FROM tblMyTable WHERE myColumn = ?"
cmMyCommandObject.CommandType = adText
cmMyCommandObject.CommandText = strSQL
cmMyCommandObject.Parameters.Append _
MyCommandObject.CreateParameter("1", adVarChar, adParamInput, 20, myVar)
set rsResults = cmMyCommandObject.ExecuteThis will solve all possible xcharacter issues, and doesn't require a find and replace operation(s) or multiple string concatenation. A side effect is that you can also set
Code:
Prepared = TrueThis model also works with Inserts/Updates/Deletes very nicely. It is a VERY flexible model if you are not using DB Stored Procedures.
James
James Culshaw
jculshaw@active-data-solutions.co.uk
- Status
Similar threads
