[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

DB SQL Text String Question

I've got a input field for a last name. I built an SQL statement to pull this field and search a DB for the closest match. How do I account for the (') in a last name???

For example, when a user inputs, say, O'Malley (Gets assigned the variable, SelectUser), the program errors because of the ('). My SQL statement looks like this:

               strSQL = "Select * from PHONEDB WHERE [Last Name] Like '" & SelectUser & "*'"
0
sehenkel
Asked:
sehenkel
  • 8
  • 7
1 Solution
 
vmanoCommented:
use double quotes around '
0
 
vmanoCommented:
here is more info:
Single quotation marks ('), double quotation marks ("), and the pipe symbol (|) are all special characters in SQL. Double up the quotation marks or, if you have only one type of quotation mark (single or double) in your data, use the other one as the delimiter.All other characters are handled as is.

let me know if this helps,
vmano
0
 
sehenkelAuthor Commented:
Edited text of question
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sehenkelAuthor Commented:
Not sure if I follow what you are saying. Are you saying the the ("), (') and (|) are interchangeable? I tried the change but it did not work. Here's more code so you can see:


SelectUser = UCase(lblLastName.text) 'Assign variable from input field
strSQL = "Select * from PHONEDB WHERE [Last Name] LIKE ' " & SelectUser & "* ' " 

'Search Phone Database
Set DBPhones = OpenDatabase(pDBFileName)
Set RSPhones = DBPhones.OpenRecordset(strSQL, dbOpenSnapshot)
0
 
vmanoCommented:
OK. here is the code to convert your SQL:
   
   Dim nPos       As Integer
   Dim sTmp       As String
   Dim sSQL       As String

  strSQL = "Select * from PHONEDB WHERE [Last Name] LIKE ' " & SelectUser & "* ' " 
   sTmp = sSQL
   nPos = InStr(1, sTmp, "'")
   While nPos <> 0
      sTmp = Mid(sTmp, 1, nPos - 1) & "'" & "'" & Mid(sTmp, nPos + 1)
      nPos = InStr(nPos + 2, sTmp, "'")
   Wend
   strSQL = sTmp
   MsgBox strSQL

after this conversion your SQL should work fine.

let me know if  this helps,
vmano
0
 
vmanoCommented:
a correction to the above comment.just replace sSQL with strSQL.
sorry for the confusion.

vmano
0
 
sehenkelAuthor Commented:
Ok, after the coversion, my SQL statement looks like this (inputting O'Malley as a last name):

    Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

When trying to open the recordset, I get a 3075 error
0
 
sehenkelAuthor Commented:
Ok, after the coversion, my SQL statement looks like this (inputting O'Malley as a last name):

    Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

When trying to open the recordset, I get a 3075 error
0
 
vmanoCommented:
i think your SQL statement should be:
strSQL = "Select * from PHONEDB WHERE [Last Name] LIKE " & SelectUser & "*"
0
 
sehenkelAuthor Commented:
The SQL line I typed was what the value of the srtSQL variable was when debugging. In the code, my line reads like this:

strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "*'"

When debugging (inputting in O'Malley into the program) it looks like this:

Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

Sorry, I should have made that clearer.
0
 
sehenkelAuthor Commented:
The SQL line I typed was what the value of the srtSQL variable was when debugging. In the code, my line reads like this:

strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "*'"

When debugging (inputting in O'Malley into the program) it looks like this:

Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

Sorry, I should have made that clearer.
0
 
vmanoCommented:
some problem with your SQL.try this (just cut and paste):
strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "'*"

this should do it.

vmano
0
 
sehenkelAuthor Commented:
The SQL line I typed was what the value of the srtSQL variable was when debugging. In the code, my line reads like this:

strSQL = "Select * from PHONEDB_CT WHERE [Last Name] LIKE '" & SelectUser & "*'"

When debugging (inputting in O'Malley into the program) it looks like this:

Select * From PHONEDB WHERE [Last Name] LIKE "O"MALLEY*"

Sorry, I should have made that clearer.
0
 
vmanoCommented:
have you tried my SQL (comment) posted at Tuesday, October 27 1998 - 10:40AM PST. there is a difference between this SQL and what you are trying. please try to notice the difference carefully, and please try what i gave you and let me know.

vmano
0
 
sehenkelAuthor Commented:
That did the trick. Thanks so much for your help!
0

Featured Post

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!

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