Adding fields to an open recordset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Adding fields to an open recordset

Hybrid View

  1. #1
    Jason West Guest

    Adding fields to an open recordset


    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


  2. #2
    jy Guest

    Re: Adding fields to an open recordset


    "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.

  3. #3
    Jason West Guest

    Re: Adding fields to an open recordset


    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center