Adding fields to an open recordset

[Jason West]

Greetings,

I am new to ADO programming. I have been using a book to help me along but
I have run into problem I can't seem to find the answer for. I have a connection
to a datbase open then based on the table the user opens I open a recordset
for that table. I then look at the table to see what fields are in it to
decide what fields I need to add for the user. When I try to implement this
I get run time error '3219'. I have tried setting the CursorLocation = adUserClient
because my book said I could not use the .Fields.Append method unless I am
using a client-side cursor, but I still get '3219'.

Can I append fields to an open ADO recordset at all? Can anyone give me some
suggestions on how to implement this?

Here is exerpts from my code:

'open DB with ADO
Set adoDatabase = New Connection
adoDatabase.Open sOpenString & gstrDBPath

...
... other code
...
'open table with ADO
Set adoEncodeTable = New ADODB.Recordset
adoEncodeTable.CursorLocation = adUseClient

adoEncodeTable.CursorType = adOpenStatic '= adOpenDynamic
adoEncodeTable.LockType = adLockOptimistic '= adLockPessimistic
adoEncodeTable.Open gstrEncodeTableName, adoDatabase, , ,
adCmdTable
...
... other code
...
Call Autoencoder_Setup

...
... other code
...


Sub Autoencoder_Setup()

Dim FldCnt
Dim x
Dim sFldName As String

giNumFldph = 0

'count num of fields
FldCnt = adoEncodeTable.Fields.Count - 1


'check fields for type of setup
For x = 0 To FldCnt

'check for barcode print and encoding
If adoEncodeTable.Fields(x).Name = "Barcode1" Then
gbPrint = True
gbBarcode1 = True
giNumFldph = giNumFldph + 1

ElseIf adoEncodeTable.Fields(x).Name = "Barcode2" Then
gbPrint = True
gbBarcode2 = True
giNumFldph = giNumFldph + 1

ElseIf Left(adoEncodeTable.Fields(x).Name, 5) = "Print" Then
gbPrint = True
giNumFldph = giNumFldph + 1

ElseIf adoEncodeTable.Fields(x).Name = "Track1" Then
gbTrack1 = True

ElseIf adoEncodeTable.Fields(x).Name = "Track2" Then
gbTrack2 = True

ElseIf adoEncodeTable.Fields(x).Name = "Track3" Then
gbTrack3 = True

End If
Next x

'add fields for read back verify
With adoEncodeTable

If gbBarcode1 Then
.Fields.Append "Barcode1_Read", adVarChar, 60

End If
If gbBarcode2 Then
.Fields.Append "Barcode2_Read", adVarChar, 60

End If
If gbTrack1 Then
.Fields.Append "Read1", adVarChar, 255

End If
If gbTrack2 Then
.Fields.Append "Read2", adVarChar, 255

End If
If gbTrack3 Then
.Fields.Append "Read3", adVarChar, 255

End If

.Fields.Append "Approved", adBoolean
.Open gstrEncodeTableName, adoDatabase, , , adCmdTable

End With


End Sub



Regards,
Jason

[jy]

"Jason West" <jvwest@northstate.net> wrote:
>
>Greetings,
>
>I am new to ADO programming. I have been using a book to help me along but
>I have run into problem I can't seem to find the answer for. I have a connection
>to a datbase open then based on the table the user opens I open a recordset
>for that table. I then look at the table to see what fields are in it to
>decide what fields I need to add for the user. When I try to implement this
>I get run time error '3219'. I have tried setting the CursorLocation = adUserClient
>because my book said I could not use the .Fields.Append method unless I
am
>using a client-side cursor, but I still get '3219'.
>
>Can I append fields to an open ADO recordset at all? Can anyone give me
some
>suggestions on how to implement this?
>
>Here is exerpts from my code:
>
> 'open DB with ADO
> Set adoDatabase = New Connection
> adoDatabase.Open sOpenString & gstrDBPath
>
>...
>... other code
>...
> 'open table with ADO
> Set adoEncodeTable = New ADODB.Recordset
> adoEncodeTable.CursorLocation = adUseClient
>
> adoEncodeTable.CursorType = adOpenStatic '= adOpenDynamic
> adoEncodeTable.LockType = adLockOptimistic '= adLockPessimistic
> adoEncodeTable.Open gstrEncodeTableName, adoDatabase, , ,

> adCmdTable
>...
>... other code
>...
> Call Autoencoder_Setup
>
>...
>... other code
>...
>
>
>Sub Autoencoder_Setup()
>
>Dim FldCnt
>Dim x
>Dim sFldName As String
>
> giNumFldph = 0
>
> 'count num of fields
> FldCnt = adoEncodeTable.Fields.Count - 1
>
>
> 'check fields for type of setup
> For x = 0 To FldCnt
>
> 'check for barcode print and encoding
> If adoEncodeTable.Fields(x).Name = "Barcode1" Then
> gbPrint = True
> gbBarcode1 = True
> giNumFldph = giNumFldph + 1
>
> ElseIf adoEncodeTable.Fields(x).Name = "Barcode2" Then
> gbPrint = True
> gbBarcode2 = True
> giNumFldph = giNumFldph + 1
>
> ElseIf Left(adoEncodeTable.Fields(x).Name, 5) = "Print" Then
> gbPrint = True
> giNumFldph = giNumFldph + 1
>
> ElseIf adoEncodeTable.Fields(x).Name = "Track1" Then
> gbTrack1 = True
>
> ElseIf adoEncodeTable.Fields(x).Name = "Track2" Then
> gbTrack2 = True
>
> ElseIf adoEncodeTable.Fields(x).Name = "Track3" Then
> gbTrack3 = True
>
> End If
> Next x
>
> 'add fields for read back verify
> With adoEncodeTable
>
> If gbBarcode1 Then
> .Fields.Append "Barcode1_Read", adVarChar, 60
>
> End If
> If gbBarcode2 Then
> .Fields.Append "Barcode2_Read", adVarChar, 60
>
> End If
> If gbTrack1 Then
> .Fields.Append "Read1", adVarChar, 255
>
> End If
> If gbTrack2 Then
> .Fields.Append "Read2", adVarChar, 255
>
> End If
> If gbTrack3 Then
> .Fields.Append "Read3", adVarChar, 255
>
> End If
>
> .Fields.Append "Approved", adBoolean
> .Open gstrEncodeTableName, adoDatabase, , , adCmdTable
>
> End With
>
>
>End Sub
>
>
>
>Regards,
>Jason
>
jason, you can't append fields to a recordset while it's open; what you can
do is create a new recordset, which mimics the 'live' recordset, append the
required fields to that and then open it and copy the data from the original
recordset; alternatively you could try persisting the recordset as xml and
modifying the rowset schema to add the required fields & then either loading
the xml into a new recordset, or just returning the xml string to the client
for processing there. j-y.

[Jason West]

Thanks for the help.
Jason


"jy" <jy@directdialog.com> wrote:
>
>"Jason West" <jvwest@northstate.net> wrote:
>>
>>Greetings,
>>
>>I am new to ADO programming. I have been using a book to help me along
but
>>I have run into problem I can't seem to find the answer for. I have a connection
>>to a datbase open then based on the table the user opens I open a recordset
>>for that table. I then look at the table to see what fields are in it to
>>decide what fields I need to add for the user. When I try to implement
this
>>I get run time error '3219'. I have tried setting the CursorLocation =
adUserClient
>>because my book said I could not use the .Fields.Append method unless I
>am
>>using a client-side cursor, but I still get '3219'.
>>
>>Can I append fields to an open ADO recordset at all? Can anyone give me
>some
>>suggestions on how to implement this?
>>
>>Here is exerpts from my code:
>>
>> 'open DB with ADO
>> Set adoDatabase = New Connection
>> adoDatabase.Open sOpenString & gstrDBPath
>>
>>...
>>... other code
>>...
>> 'open table with ADO
>> Set adoEncodeTable = New ADODB.Recordset
>> adoEncodeTable.CursorLocation = adUseClient
>>
>> adoEncodeTable.CursorType = adOpenStatic '= adOpenDynamic
>> adoEncodeTable.LockType = adLockOptimistic '= adLockPessimistic
>> adoEncodeTable.Open gstrEncodeTableName, adoDatabase, , ,

>
>> adCmdTable
>>...
>>... other code
>>...
>> Call Autoencoder_Setup
>>
>>...
>>... other code
>>...
>>
>>
>>Sub Autoencoder_Setup()
>>
>>Dim FldCnt
>>Dim x
>>Dim sFldName As String
>>
>> giNumFldph = 0
>>
>> 'count num of fields
>> FldCnt = adoEncodeTable.Fields.Count - 1
>>
>>
>> 'check fields for type of setup
>> For x = 0 To FldCnt
>>
>> 'check for barcode print and encoding
>> If adoEncodeTable.Fields(x).Name = "Barcode1" Then
>> gbPrint = True
>> gbBarcode1 = True
>> giNumFldph = giNumFldph + 1
>>
>> ElseIf adoEncodeTable.Fields(x).Name = "Barcode2" Then
>> gbPrint = True
>> gbBarcode2 = True
>> giNumFldph = giNumFldph + 1
>>
>> ElseIf Left(adoEncodeTable.Fields(x).Name, 5) = "Print" Then
>> gbPrint = True
>> giNumFldph = giNumFldph + 1
>>
>> ElseIf adoEncodeTable.Fields(x).Name = "Track1" Then
>> gbTrack1 = True
>>
>> ElseIf adoEncodeTable.Fields(x).Name = "Track2" Then
>> gbTrack2 = True
>>
>> ElseIf adoEncodeTable.Fields(x).Name = "Track3" Then
>> gbTrack3 = True
>>
>> End If
>> Next x
>>
>> 'add fields for read back verify
>> With adoEncodeTable
>>
>> If gbBarcode1 Then
>> .Fields.Append "Barcode1_Read", adVarChar, 60
>>
>> End If
>> If gbBarcode2 Then
>> .Fields.Append "Barcode2_Read", adVarChar, 60
>>
>> End If
>> If gbTrack1 Then
>> .Fields.Append "Read1", adVarChar, 255
>>
>> End If
>> If gbTrack2 Then
>> .Fields.Append "Read2", adVarChar, 255
>>
>> End If
>> If gbTrack3 Then
>> .Fields.Append "Read3", adVarChar, 255
>>
>> End If
>>
>> .Fields.Append "Approved", adBoolean
>> .Open gstrEncodeTableName, adoDatabase, , , adCmdTable
>>
>> End With
>>
>>
>>End Sub
>>
>>
>>
>>Regards,
>>Jason
>>
>jason, you can't append fields to a recordset while it's open; what you
can
>do is create a new recordset, which mimics the 'live' recordset, append
the
>required fields to that and then open it and copy the data from the original
>recordset; alternatively you could try persisting the recordset as xml and
>modifying the rowset schema to add the required fields & then either loading
>the xml into a new recordset, or just returning the xml string to the client
>for processing there. j-y.