Re: MD5 Hash with single quote = grief in dao.findfirst

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Stephen Rasey (raseysm_at_wiserways.com)
Date: 08/13/04 

Date: Thu, 12 Aug 2004 23:00:41 -0500

I set the Hash Field size to Text 20.

>From the immediate window.
?strhash
-s3ë=­'[+@û;ÒëN
?sql
HASH = '-s3ë=­''[+@û;ÒëN'

What is in the database
-s3ë=­'[+@û;ÒëN

The Asc() codes for the strHash.
      151, 115, 2, 51, 235, 61, 173, 39, 91, 43, 64, 251, 59, 210, 235, 78,

With the Field size at Text 20, it still errors. So that wasn't it.

I'm dumbfounded.

Stephen Rasey

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:O%23Ogl7MgEHA.2028@tk2msftngp13.phx.gbl...
> You don't show the code where you write the value to the database.
> Check to see that you are not truncating at that stage.
>
>
>
>
> "Stephen Rasey" <raseysm@wiserways.com> wrote in message
> news:eA6PpkLgEHA.3632@TK2MSFTNGP09.phx.gbl...
> > (this is a cross-posting from microsoft.public.excel.programming with
some
> > editing)
> >
> > I know when you need to create a query string and the data contains a
> single
> > quote, you must double the quote as an escape sequence. I am doing
that,
> > but my dao recordset.FindFirst gives a NoMatch. Am I blundering?
> >
> > The situation. I am using a wwGetMD5Hash2 function See:
> > http://excelsig.org/VBA/wwHash.htm#wwGetMD5Hash2
> > I use this to track whole sets of assumptions in my portfolio models.
> > Hundreds of assumption cells combined into one 16 character Hash. That
> > Hash I write to a database table called H70. The HASH field is a No
> > Duplicates index. Each record has an IDHash, a Long integer, random
> > autonumber.
> >
> > When I run the model, I check the Hash result against the H70 table in
the
> > database using a FindFirst method. If the Hash cannot be found, I
> create
> > a new record. If it can
> > be found, I retrieve the IDHash long integer value.
> >
> > It works 95% of the time. But if the Hash string contains a single
> quote,
> > my DAO rsH70.FindFirst fails to find the Hash string. I double the
> single
> > quote. I do not get a SQL error. But it will not find the record.
> >
> > Here is an example. The 14th character in the hash stored in a row of
> the
> > H70
> > table contains a single quote.
> > Field:HASH in Table H70 contains
> > ~8æQzzûù¦ÿ5'?;
> >
> > The Hash Value in Spreadsheet read into variable strHash (as seen in
the
> > Immediate window)
> > ~8æQzzûù¦ÿ5'?;
> >
> > I have a small function to replace a single quote with two single quotes
> as
> > I build the criteria SQL string.
> > Dim sql as string
> > sql is set to the value
> > sql = "HASH = '" & wwQuoteFix(strHash) & "'"
> >
> > Function wwQuoteFix(str1 As String) As String
> > wwQuoteFix = Replace(str1, "'", "''")
> > End Function
> >
> >
> > In the H70Check function below, the value of sql in the find first
> statement
> > is:
> > HASH = '~8æQzzûù¦ÿ5''?;'
> > so I think I have correctly doubled the single quote within the string.
> >
> > yet the DAO recordset.NoMatch is true
> >
> > Function H70Check(strHash As String) As Long
> > 'return the IDHashProspSched if the strHash is found in
> > H70HashProspSched table
> > 'return 0 if not found. Protect against 0 being a legitimate ID.
> > Dim sql As String
> > sql = "HASH = '" & wwQuoteFix(strHash) & "'" 'Rasey 040804 v24j
> > With rsH70ProspSched 'a Public DAO.Recordset
> > .FindFirst sql
> > If .NoMatch Then
> > H70Check = 0
> > Else
> > H70Check = !IDHashProspSched
> > End If
> > End With
> > End Function
> >
> > When I attempt to write the Hash as a new record, I error because the
> Access
> > Index already has the Hash of that value.
> >
> > Maybe the non-printing characters are part of the problem. Other
Hashes
> > with non-printing characters work if they do not have single quotes.
> >
> > I am prepared to be very embarrased at some dumb mistake I made. I'll
> > accept that. I am out of ideas.
> > I hope someone sees the problem.
> >
> > I am on the lookout for a Hash function that provided a more restricted
> set
> > of characters. the wwGetMD5Hash2 function will return asc(0),
asc(10),
> > asc(39) and some other characters that might be problematic in Excel and
> > Access.
> >
> > Stephen Rasey
> > WiserWays, LLC
> > Houston
> > http://excelsig.org
> >
> >
> >
> >
>
>

Relevant Pages

  • Re: sql syntax error
    ... Hash: SHA1 ... The red highlight is a VBA error indication, not a SQL syntax error. ... the string probably isn't properly formatted for VBA. ...
    (comp.databases.ms-access)
  • 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)
  • An MD5 Hash with a single quote = grief in SQL query
    ... I know when you need to create a query string and the data contains a single ... quote, you must double the quote as an escape sequence. ... Hundreds of assumption cells combined into one 16 character Hash. ... But if the Hash string contains a single quote, ...
    (microsoft.public.excel.programming)
  • MD5 Hash with single quote = grief in dao.findfirst
    ... I know when you need to create a query string and the data contains a single ... quote, you must double the quote as an escape sequence. ... Hundreds of assumption cells combined into one 16 character Hash. ... I build the criteria SQL string. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Trouble reading fields containing special characters
    ... I double any possible single quote characters in the string. ... Any double single quotes in a string (all SQL strings are enclosed by single ...
    (microsoft.public.vb.database.ado)