Re: Trouble reading fields containing special characters

I double any possible single quote characters in the string. For example:

strValue = "O'Hare"
strValue = Replace(strValue, "'", "''")

Any double single quotes in a string (all SQL strings are enclosed by single
quotes) resolve to a single single quote within the string.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--
"Martin" <ironwoodcanyon@xxxxxxxxx> wrote in message
news:oukat3d693a1idve20vmercadveqjptt44@xxxxxxxxxx
I need to retrieve some records from an MS Access database where some
of the data fields contain special characters (primarily the single
quote - chr$(39)). This is not just a simple "import" of data; I need
to selectively retrieve the records. I'm trying to do this by
executing an SQL statement to create a recordset which I then write
out to an SQL Server table.

I'm doing this in a VB6 program. I'm building my SQL statement by
extracting data from a 2nd MS Access table (which is where the special
characters are coming from). Like so:

Dim rsX As New ADODB.Recordset

SQL$ = "SELECT EndUserNumber FROM EndUsers WHERE EndUserName = '" &
rsOldData.Fields("EndUser").Value & "' AND " & "EndUserAddress = '" &
rsOldData.Fields("ProjAddress").Value & "' AND " & "EndUserCity = '" &
rsOldData.Fields("ProjCity").Value & "' AND " & "EndUserState = '" &
rsOldData.Fields("ProjState").Value & "'"

rsX.Open SQL$, SQLCN_KVKMain, adOpenStatic, adLockOptimistic
If rsX.RecordCount > 0 Then
rsZ.Fields("EndUserNum").Value = rsX.Fields("EndUserNumber").Value
Else
rsZ.Fields("EndUserNum").Value = 0
End If
rsX.Close

You'll notice that I'm using the single-quote character inside the SQL
statement to enclose the data field values. This, of course, results
in an "incorrect syntax" error when the field value contains a
single-quote character.

I've tried using the back-quote character (chr$(96)) but that didn't
work either.

I tried it with double-quotes (chr$(34)). That resulted in an "Invalid
column name" error (which makes no sense - the field names are
correct).

How do I get this to work?

Thanks



.

Relevant Pages

  • RE: Looking for help with odd DBD::Oracle::db prepare behavior
    ... It appears that the invisible characters are delimiters passed via the HTML ... that I could parse the string argument into its pieces. ... SQL. ...
    (perl.dbi.users)
  • Re: INSERT or UPDATE a VFP memo field using VBSCRIPT
    ... dim conn, comm, sql, memoTXT, parLen ... Everything is oK but the memoTXT must be less than 255 char. ... > LOCAL strSQL as String, ... > This does not work because it is longer than 254 characters. ...
    (microsoft.public.fox.vfp.dbc)
  • Re: MD5 Hash with single quote = grief in dao.findfirst
    ... I set the Hash Field size to Text 20. ... I do not get a SQL error. ... >> I have a small function to replace a single quote with two single quotes ... >> I build the criteria SQL string. ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL Injection Code Help
    ... The most important characters to get rid of are the single quote and ... If your application connects to SQL server with a SQL ... user that is a member of the sysadmin role, SQL injection could be very ... ' Replace globally disallowed characters: ...
    (microsoft.public.sqlserver.security)
  • Re: MD5 Hash with single quote = grief in dao.findfirst
    ... > Hundreds of assumption cells combined into one 16 character Hash. ... But if the Hash string contains a single ... I do not get a SQL error. ... > I have a small function to replace a single quote with two single quotes ...
    (microsoft.public.access.modulesdaovba)
Loading