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

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 10:48:03 +1000

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 Spread*** 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
>
>
>
>

Quantcast