VB6, Access database, Data encryption

[Karen]

Please help,

I am encrypting an RTF file and then building a SQL statement to insert the
data into an Access database. The encryption is confusing the syntax of the
SQL. I don't know exactly which symbol is causing the problem. I tried replacing
all ' with '' and I still had my problem. I don't know what other symbols
would cause a problem

Is there anyway to fix this problem. Maybe I can encrypt after it gets to
the access database. I don't even know if this is possible. If anybody knows
please share your thoughts.

I am using a com component from the DEVX website ENCRYPTex. It is a very
simple component that has 4 functions.

thanks for any help
karen

[Bernie]

"Karen" <vb.@127.0.0.1> wrote:
>
>
>Please help,
>
>I am encrypting an RTF file and then building a SQL statement to insert
the
>data into an Access database. The encryption is confusing the syntax of
the
>SQL. I don't know exactly which symbol is causing the problem. I tried replacing
>all ' with '' and I still had my problem. I don't know what other symbols
>would cause a problem
>
>Is there anyway to fix this problem. Maybe I can encrypt after it gets to
>the access database. I don't even know if this is possible. If anybody knows
>please share your thoughts.
>
>I am using a com component from the DEVX website ENCRYPTex. It is a very
>simple component that has 4 functions.
>
>thanks for any help
>karen
>
>

Hi Karen,

Both singlequotes and doublequotes must be doubled before putting the string
into the sql-statement. Also check that there are no Chr(0) in the string
(shouldn't be but who knows how this encryption works). I'm not sure, though,
if Chr(0)'s in a string mess up the processing.

Another approach is to use the function

ADODB.RecordSet.Update Fields, Values

where Fields and Values are VariantArrays. By using this function you don't
have to worry about qoutes in the string. Just open the recordset with the
desired record and update it with

rs.Update Array("myField"), Array(EncryptedString)

or you can update the record like this;

rs!myField = EncryptedString
rs.Update

Both ways you get rid of the quote-problem.

Bernie

[karen]

bernie,
Thanks for you input. I want to make sure I understand.

I have a class named CDataService that handles the database interaction.
I declare the ADODB.connection
--->>conavb as ADODB.connection

I then have a function that I pass the SQL Statement. I contains the following
statement
----->> conavb.execute SQLStatement,, adcmdtext

Here are my questions?
I am not quite sure how to ask my questions. I am new to ADO
1. What do I need to do before the ADODB.Recordset.update? Does this command
actually update the database or just the recordset? Or are they the same?
It sounds like I will need to do a query into a recordset, update the recordset
and then WHAT????

2. How do I handle inserts because the recordset doesn't exist for me to
update? I need to do some reading. Any suggestions?

3. How can I pass what needs to be updated,inserted, deleted without hardcoding
it in my CDataService. Currently, the business objects contain the specifics
fields and values and I pass the SQLStatement to CDataService. I am trying
to stick to a 3 tier methodology.

Any help is appreciated. Meanwhile, I am going to do some ADO research!!!

Karen





"Bernie" <magnus.bernroth@rejlers.se> wrote:
>
>"Karen" <vb.@127.0.0.1> wrote:
>>
>>
>>Please help,
>>
>>I am encrypting an RTF file and then building a SQL statement to insert
>the
>>data into an Access database. The encryption is confusing the syntax of
>the
>>SQL. I don't know exactly which symbol is causing the problem. I tried
replacing
>>all ' with '' and I still had my problem. I don't know what other symbols
>>would cause a problem
>>
>>Is there anyway to fix this problem. Maybe I can encrypt after it gets
to
>>the access database. I don't even know if this is possible. If anybody
knows
>>please share your thoughts.
>>
>>I am using a com component from the DEVX website ENCRYPTex. It is a very
>>simple component that has 4 functions.
>>
>>thanks for any help
>>karen
>>
>>
>
>Hi Karen,
>
>Both singlequotes and doublequotes must be doubled before putting the string
>into the sql-statement. Also check that there are no Chr(0) in the string
>(shouldn't be but who knows how this encryption works). I'm not sure, though,
>if Chr(0)'s in a string mess up the processing.
>
>Another approach is to use the function
>
> ADODB.RecordSet.Update Fields, Values
>
>where Fields and Values are VariantArrays. By using this function you don't
>have to worry about qoutes in the string. Just open the recordset with the
>desired record and update it with
>
> rs.Update Array("myField"), Array(EncryptedString)
>
>or you can update the record like this;
>
> rs!myField = EncryptedString
> rs.Update
>
>Both ways you get rid of the quote-problem.
>
>Bernie

[Bernie]

"karen " <vb.@127.0.0.1> wrote:
>
>bernie,
>Thanks for you input. I want to make sure I understand.
>
>I have a class named CDataService that handles the database interaction.
>I declare the ADODB.connection
>--->>conavb as ADODB.connection
>
>I then have a function that I pass the SQL Statement. I contains the following
>statement
>----->> conavb.execute SQLStatement,, adcmdtext
>
>Here are my questions?
>I am not quite sure how to ask my questions. I am new to ADO
>1. What do I need to do before the ADODB.Recordset.update? Does this command
>actually update the database or just the recordset? Or are they the same?
>It sounds like I will need to do a query into a recordset, update the recordset
>and then WHAT????
>
>2. How do I handle inserts because the recordset doesn't exist for me to
>update? I need to do some reading. Any suggestions?
>
>3. How can I pass what needs to be updated,inserted, deleted without hardcoding
>it in my CDataService. Currently, the business objects contain the specifics
>fields and values and I pass the SQLStatement to CDataService. I am trying
>to stick to a 3 tier methodology.
>
>Any help is appreciated. Meanwhile, I am going to do some ADO research!!!
>
>Karen
>

Hi again Karen,

Using the recordset directly to update a record has the advantage of not
being forced to assemble a SQL-statement (meaning no quotes are needed).

This is the basics to use this method;

The table myTable consists of 2 columns:
ID (a counter)
mystrField (a string column)


Private Function UpdateRecord(ByRef EncryptedStuff As String, _
ByRef recordID As Long)

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'Open the connection...
cn.Open ConnectionString

'Open an updatable recordset with only the desired record...
rs.Open "SELECT * FROM myTable WHERE ID=" & recordID, _
cn, _
adOpenKeyset, _
adLockPessimistic

'Check that there is a record as expected...
If Not (rs.EOF And rs.BOF) Then

'Either you can do like this:

'Set the field to the new string...
'EncryptedStuff may contain any garbage like quotes of any kind!
rs!mystrField = EncryptedStuff
'Update the recordset...
rs.Update

'or like this:
'rs.Update Array("mystrField"), Array(EncryptedStuff)

'Both ways give the same result!

End If

'Free the resources (no need to use the Close-method)...
Set rs = Nothing
Set cn = Nothing

End Function


I hope you got it. Don't forget the errorhandling, though.

Regards

Bernie

"Bernie" <magnus.bernroth@rejlers.se> wrote:
>
>"karen " <vb.@127.0.0.1> wrote:
>>
>>bernie,
>>Thanks for you input. I want to make sure I understand.
>>
>>I have a class named CDataService that handles the database interaction.
>>I declare the ADODB.connection
>>--->>conavb as ADODB.connection
>>
>>I then have a function that I pass the SQL Statement. I contains the following
>>statement
>>----->> conavb.execute SQLStatement,, adcmdtext
>>
>>Here are my questions?
>>I am not quite sure how to ask my questions. I am new to ADO
>>1. What do I need to do before the ADODB.Recordset.update? Does this command
>>actually update the database or just the recordset? Or are they the same?
>>It sounds like I will need to do a query into a recordset, update the recordset
>>and then WHAT????
>>
>>2. How do I handle inserts because the recordset doesn't exist for me to
>>update? I need to do some reading. Any suggestions?
>>
>>3. How can I pass what needs to be updated,inserted, deleted without hardcoding
>>it in my CDataService. Currently, the business objects contain the specifics
>>fields and values and I pass the SQLStatement to CDataService. I am trying
>>to stick to a 3 tier methodology.
>>
>>Any help is appreciated. Meanwhile, I am going to do some ADO research!!!
>>
>>Karen
>>
>
>Hi again Karen,
>
>Using the recordset directly to update a record has the advantage of not
>being forced to assemble a SQL-statement (meaning no quotes are needed).
>
>This is the basics to use this method;
>
>The table myTable consists of 2 columns:
> ID (a counter)
> mystrField (a string column)
>
>
>Private Function UpdateRecord(ByRef EncryptedStuff As String, _
> ByRef recordID As Long)
>
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
>
> 'Open the connection...
> cn.Open ConnectionString
>
> 'Open an updatable recordset with only the desired record...
> rs.Open "SELECT * FROM myTable WHERE ID=" & recordID, _
> cn, _
> adOpenKeyset, _
> adLockPessimistic
>
> 'Check that there is a record as expected...
> If Not (rs.EOF And rs.BOF) Then
>
> 'Either you can do like this:
>
> 'Set the field to the new string...
> 'EncryptedStuff may contain any garbage like quotes of any kind!
> rs!mystrField = EncryptedStuff
> 'Update the recordset...
> rs.Update
>
> 'or like this:
> 'rs.Update Array("mystrField"), Array(EncryptedStuff)
>
> 'Both ways give the same result!
>
> End If
>
> 'Free the resources (no need to use the Close-method)...
> Set rs = Nothing
> Set cn = Nothing
>
>End Function
>
>
>I hope you got it. Don't forget the errorhandling, though.
>
>Regards
>
>Bernie
>
>
BERNIE,

I have done what you have suggested. I think we are getting close. Now, I
am getting errors with my RTF data (which is a string variable populated
by TXTextcontrol activex control). I can updated my recordset with regular
text data, but I have problems with the Txtcontrol data or encrypted data.
I get the following error and I don't know how to determine the exact problem
or where to look for more error messages.

When I do this :
-->rs!QuestionRTF = strQuestionRTF 'rtf formated not encrypted
-->rs.update
or
-->rs!QuestionDesc = strQuestionDescEcypted 'regular string ecrypted
-->rs.update

I get this:
---> Multiple -step OLE DB operation generated errors. Check Each OLE DB
status value, if available, No work done.

When I do this

-->rs.update array("QuestionRTF"), Array(strQuestionDescEncrypted)

I get this:
----> Type mismatch

I will continue to trouble shoot with your suggestions. I wasn't able to
find the object that contained the OLE DB errors. I looked in the connection
object and the recordset object. Any suggestion here

Please stick with me Bernie.
Thanks
Karen

[Karen]

<vb.@127.0.0.1> wrote:
>
>"Bernie" <magnus.bernroth@rejlers.se> wrote:
>>
>>"karen " <vb.@127.0.0.1> wrote:
>>>
>>>bernie,
>>>Thanks for you input. I want to make sure I understand.
>>>
>>>I have a class named CDataService that handles the database interaction.
>>>I declare the ADODB.connection
>>>--->>conavb as ADODB.connection
>>>
>>>I then have a function that I pass the SQL Statement. I contains the following
>>>statement
>>>----->> conavb.execute SQLStatement,, adcmdtext
>>>
>>>Here are my questions?
>>>I am not quite sure how to ask my questions. I am new to ADO
>>>1. What do I need to do before the ADODB.Recordset.update? Does this command
>>>actually update the database or just the recordset? Or are they the same?
>>>It sounds like I will need to do a query into a recordset, update the
recordset
>>>and then WHAT????
>>>
>>>2. How do I handle inserts because the recordset doesn't exist for me
to
>>>update? I need to do some reading. Any suggestions?
>>>
>>>3. How can I pass what needs to be updated,inserted, deleted without hardcoding
>>>it in my CDataService. Currently, the business objects contain the specifics
>>>fields and values and I pass the SQLStatement to CDataService. I am trying
>>>to stick to a 3 tier methodology.
>>>
>>>Any help is appreciated. Meanwhile, I am going to do some ADO research!!!
>>>
>>>Karen
>>>
>>
>>Hi again Karen,
>>
>>Using the recordset directly to update a record has the advantage of not
>>being forced to assemble a SQL-statement (meaning no quotes are needed).
>>
>>This is the basics to use this method;
>>
>>The table myTable consists of 2 columns:
>> ID (a counter)
>> mystrField (a string column)
>>
>>
>>Private Function UpdateRecord(ByRef EncryptedStuff As String, _
>> ByRef recordID As Long)
>>
>> Dim cn As New ADODB.Connection
>> Dim rs As New ADODB.Recordset
>>
>> 'Open the connection...
>> cn.Open ConnectionString
>>
>> 'Open an updatable recordset with only the desired record...
>> rs.Open "SELECT * FROM myTable WHERE ID=" & recordID, _
>> cn, _
>> adOpenKeyset, _
>> adLockPessimistic
>>
>> 'Check that there is a record as expected...
>> If Not (rs.EOF And rs.BOF) Then
>>
>> 'Either you can do like this:
>>
>> 'Set the field to the new string...
>> 'EncryptedStuff may contain any garbage like quotes of any kind!
>> rs!mystrField = EncryptedStuff
>> 'Update the recordset...
>> rs.Update
>>
>> 'or like this:
>> 'rs.Update Array("mystrField"), Array(EncryptedStuff)
>>
>> 'Both ways give the same result!
>>
>> End If
>>
>> 'Free the resources (no need to use the Close-method)...
>> Set rs = Nothing
>> Set cn = Nothing
>>
>>End Function
>>
>>
>>I hope you got it. Don't forget the errorhandling, though.
>>
>>Regards
>>
>>Bernie
>>
>>
>BERNIE,
>
>I have done what you have suggested. I think we are getting close. Now,
I
>am getting errors with my RTF data (which is a string variable populated
>by TXTextcontrol activex control). I can updated my recordset with regular
>text data, but I have problems with the Txtcontrol data or encrypted data.
>I get the following error and I don't know how to determine the exact problem
>or where to look for more error messages.
>
>When I do this :
>-->rs!QuestionRTF = strQuestionRTF 'rtf formated not encrypted
>-->rs.update
>or
>-->rs!QuestionDesc = strQuestionDescEcypted 'regular string ecrypted
>-->rs.update
>
>I get this:
>---> Multiple -step OLE DB operation generated errors. Check Each OLE DB
>status value, if available, No work done.
>
>When I do this
>
>-->rs.update array("QuestionRTF"), Array(strQuestionDescEncrypted)
>
>I get this:
>----> Type mismatch
>
>I will continue to trouble shoot with your suggestions. I wasn't able to
>find the object that contained the OLE DB errors. I looked in the connection
>object and the recordset object. Any suggestion here
>
>Please stick with me Bernie.
>Thanks
>Karen
>
>Bernie,
I encrypted a regular text field using the array and it worked OK. The problem
seems to stem around the RTF type data (which is a rtf text string). If I
can figure out what that database error it would help. I didn't have a problem
with RTF data when I was building and using the SQL statements. GO Figure

Karen
>
>
>

[Bernie]

>>Bernie,
>I encrypted a regular text field using the array and it worked OK. The problem
>seems to stem around the RTF type data (which is a rtf text string). If
I
>can figure out what that database error it would help. I didn't have a problem
>with RTF data when I was building and using the SQL statements. GO Figure
>
>Karen

Hi,

I must honestly admit that I'm not aware of any 'RTF data type'. But I guess
you end up with some kind of binary string (everything is binary to the computer,
right?). Maybe you should try to put it in a binary column (PM, Blob, Image
or whatever db you're using).

Can you give me a sample of how this encrypted stuff looks like (I hope it
isn't too long as a string)?

I have for a long time used strings to hold binary stuff in different db's
with no problems, no matter what characters they are translated to.

Bernie