SQL Server/Classic ASP Escape Characters

So I want to grab data from my remote server and bring it to my local server using a web page.  Here is the codd:
        Set rs = New ADODB.Recordset
        sql = "SELECT FirstName, LastName, City, St, Phone, Email, DOB, Gender, Country, FBook, Twitter, Uploaded "
        sql = sql & "FROM Participant WHERE ParticipantID = " & PartsToAdd(i)
        rs.Open sql, srvr_conn, 1, 2
        For j = 0 To 10
            PartArr(j) = rs(j).Value
        Next j
        rs(11).Value = "y"
        rs.Update
        rs.Close
        Set rs = Nothing
                
        'check to see if this participant exists
        lParticipantID = 0
        Set rs = New ADODB.Recordset
        sql = "SELECT ParticipantID FROM Participant WHERE FirstName = '" & PartArr(0) & "' AND LastName = '" & PartArr(1)
        sql = sql & "' AND Email = '" & PartArr(5) & "' AND Gender = '" & PartArr(7) & "'"
        rs.Open sql, conn, 1, 2
        If rs.RecordCount > 0 Then lParticipantID = rs(0).Value
        rs.Close
        Set rs = Nothing

Open in new window


The problem is that If I get an error when I try to check for a match in the local data (conn) if there is an O'Brien in the remote data.  If know how to use Replace(rs(1).Value, " ' ' ", " ' ") but I keep getting the error.  Any suggestions?
Bob SchneiderCo-OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I would just replace with an entity

You can use chr(39) for the replace
replace(myval,"'",chr(39))

http://dev.w3.org/html5/html-author/charref
http://www.w3schools.com/html/html_entities.asp
http://www.ascii.cl/htmlcodes.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee W, MVPTechnology and Business Process AdvisorCommented:
It could help to see what error you're getting.

You should also debug it.  Output the sql string and see what you're getting - find the line that is experiencing the error and RIGHT BEFORE IT, use RESPONSE.WRITE to display the SQL statement you expect to execute.

OF course, I don't see you're "replace" code that you say you know to use anywhere in your code above.  

Finally, if you didn't, in fact, just make a typo in your explanation, the PROPER syntax of Replace is
Replace(string, "findThis", "replaceWithThis")

The example you use above would find '' and replace it with ' and what you need to find is ' and replace it with '' -- the exact opposite.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I think I meant

replace(myval,"'","'")

http://jsbin.com/yebeworuso/1/edit
0
Bob SchneiderCo-OwnerAuthor Commented:
This worked:
        'get remote part data based on part id
        Set rs = New ADODB.Recordset
        sql = "SELECT FirstName, LastName, City, St, Phone, Email, DOB, Gender, Country, FBook, Twitter, Uploaded "
        sql = sql & "FROM Participant WHERE ParticipantID = " & PartsToAdd(i)
        rs.Open sql, srvr_conn, 1, 2
        For j = 0 To 10
            PartArr(j) = rs(j).Value
        Next j
        rs(11).Value = "y"
        rs.Update
        rs.Close
        Set rs = Nothing
                
        'check to see if this participant exists
        lParticipantID = 0
        Set rs = New ADODB.Recordset
        sql = "SELECT ParticipantID FROM Participant WHERE FirstName = '" & Replace(PartArr(0), Chr(39), Chr(34))
        sql = sql & "' AND LastName = '" & Replace(PartArr(1), Chr(39), Chr(34))
        sql = sql & "' AND Email = '" & PartArr(5) & "' AND Gender = '" & PartArr(7) & "'"
        rs.Open sql, conn, 1, 2
        If rs.RecordCount > 0 Then lParticipantID = rs(0).Value
        rs.Close
        Set rs = Nothing

Open in new window


Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.