Comparing recordsets in VB2005

30 Jun 2006 2:21 AM

Kevin

I'm trying to convert a VB6 app to VB2005. I'm trying to search for
changes made to a record and record them in a log file. This is my
code in VB6:

Dim X As Long
Dim oField As ADODB.Field
Dim rs as New Adodb.Recordset

pstrSQL = "SELECT * FROM qryCRClasses WHERE fldStudentNumber = '" &
txtStudentNumber & "' AND fldCourseID = " & txtCourseID
    rs.Open pstrStudent, db, adOpenStatic, adLockOptimistic, adCmdText
    lFieldCount = rs.Fields.Count - 1

'Compare the new record to the one stored in the SavedRecord array

For X = 0 To lFieldCount
    Set oField = rs.Fields(X)
     If SavedRecord(X).Value <> oField.Value Then
         kString = kString & oField.Name & " * " & oField.Value & " |
"
     End If
Next

Worked well enough in VB6, but I get the error:
Operator '<>' is not defined for type 'DBNull' and type 'DBNull'

I think I need to use IComparer.Compare Method, but can't quite figure
out the syntax. Does anyone know of an easy way to do this?

30 Jun 2006 5:12 AM

Cor Ligthert [MVP]

Kevin,

The IComparer is to compare fields. It will not help you in this.

You get somehow DBNull values while your datafield is not from that value.

if Not oField.Value Is Dbnull.value

Will probably do your job.

I hope this helps,

Cor

If rs.Field(
Show quoteHide quote

"Kevin" <kev***@cfl.rr.com> schreef in bericht
news:st19a2d08t63uojrtq7d4eccl8n5p8mf33@4ax.com...
> I'm trying to convert a VB6 app to VB2005. I'm trying to search for
> changes made to a record and record them in a log file. This is my
> code in VB6:
>
> Dim X As Long
> Dim oField As ADODB.Field
> Dim rs as New Adodb.Recordset
>
> pstrSQL = "SELECT * FROM qryCRClasses WHERE fldStudentNumber = '" &
> txtStudentNumber & "' AND fldCourseID = " & txtCourseID
>    rs.Open pstrStudent, db, adOpenStatic, adLockOptimistic, adCmdText
>    lFieldCount = rs.Fields.Count - 1
>
> 'Compare the new record to the one stored in the SavedRecord array
>
> For X = 0 To lFieldCount
>    Set oField = rs.Fields(X)
>     If SavedRecord(X).Value <> oField.Value Then
>         kString = kString & oField.Name & " * " & oField.Value & " |
> "
>     End If
> Next
>
>
> Worked well enough in VB6, but I get the error:
> Operator '<>' is not defined for type 'DBNull' and type 'DBNull'
>
> I think I need to use IComparer.Compare Method, but can't quite figure
> out the syntax. Does anyone know of an easy way to do this?

30 Jun 2006 1:25 PM

Kevin

Right, I'm wanting to compare fields. But I took your advice and just
did it the easy way with the IF THEN statements. Thanks.

On Fri, 30 Jun 2006 07:12:56 +0200, "Cor Ligthert [MVP]"
<notmyfirstn***@planet.nl> wrote:

Show quoteHide quote

>Kevin,
>
>The IComparer is to compare fields. It will not help you in this.
>
>You get somehow DBNull values while your datafield is not from that value.
>
>if Not oField.Value Is Dbnull.value
>
>Will probably do your job.
>
>I hope this helps,
>
>Cor
>
>
>
>If rs.Field(
>"Kevin" <kev***@cfl.rr.com> schreef in bericht
>news:st19a2d08t63uojrtq7d4eccl8n5p8mf33@4ax.com...
>> I'm trying to convert a VB6 app to VB2005. I'm trying to search for
>> changes made to a record and record them in a log file. This is my
>> code in VB6:
>>
>> Dim X As Long
>> Dim oField As ADODB.Field
>> Dim rs as New Adodb.Recordset
>>
>> pstrSQL = "SELECT * FROM qryCRClasses WHERE fldStudentNumber = '" &
>> txtStudentNumber & "' AND fldCourseID = " & txtCourseID
>>    rs.Open pstrStudent, db, adOpenStatic, adLockOptimistic, adCmdText
>>    lFieldCount = rs.Fields.Count - 1
>>
>> 'Compare the new record to the one stored in the SavedRecord array
>>
>> For X = 0 To lFieldCount
>>    Set oField = rs.Fields(X)
>>     If SavedRecord(X).Value <> oField.Value Then
>>         kString = kString & oField.Name & " * " & oField.Value & " |
>> "
>>     End If
>> Next
>>
>>
>> Worked well enough in VB6, but I get the error:
>> Operator '<>' is not defined for type 'DBNull' and type 'DBNull'
>>
>> I think I need to use IComparer.Compare Method, but can't quite figure
>> out the syntax. Does anyone know of an easy way to do this?
>