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

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

Date: Mon, 16 Aug 2004 13:59:59 +1000

FWIW, I don't have any problem with "single quotes' in
DAO.findfirst, in Access 2000 or A97.

Two thoughts:
1) You haven't set Option Compare Binary at the top of the code module?

2) If you check using AscW() instead of Asc() do you see anything different?

(david)

"Stephen Rasey" <raseysm@wiserways.com> wrote in message
news:uUDLgoOgEHA.3416@TK2MSFTNGP09.phx.gbl...
> 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 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
> > >
> > >
> > >
> > >
> >
> >
>
>

Loading