Problem with Access recordset

[James Bott]

I am having a problem with an Access recordset. I can get the connection and the oRS:eof works, but both oRS:FieldGet() and oRS:Fields() error out with an UKNOWN NAME error. Any ideas?

Here is the actual error:

Error description: Error ADODB.Recordset/6 DISP_E_UNKNOWNNAME: FIELDGET

Here is my code:

Code: Select all  Expand view

#include "Fivewin.ch"
#include "Tcbrowse.ch"

#define TABLE "ERROR_CODES"


FUNCTION MAIN()

    LOCAL oRs, oErr
    LOCAL cFile:= "TIPDATA.MDB"

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    TRY
        oRS:Open( "SELECT * FROM " + TABLE, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=tipdata.mdb", 1, 3 )
    CATCH oErr
        ? oErr:Description
        RETURN NIL
    END TRY

    msgInfo( oRS:eof )  // OK
    msgInfo( oRS:FieldGet(1) )  // Error
    msgInfo( oRS:Fields("ERROR_CODE"):value ) // Error

[James Bott]

OK, I have found that oRS:FieldGet(1) is invalid syntax, I guess. I found this example somewhere, but it continues to error out.

All of a sudden, oRS:Fields("ERROR_CODE") started working and I can't figure out why.

James

[Rochinha]

Bott,

Change:

Code: Select all  Expand view

FUNCTION MAIN()

    LOCAL oRs


With and try

Code: Select all  Expand view

FUNCTION MAIN()

    PUBLIC oRs


[Armando]

James:

With MySql connection i use this code:

Code: Select all  Expand view

TRY
   oRsEmp   :=   TOleAuto():New("adodb.recordset")
CATCH oError
   MsgStop( "No se ha podido crear el RECORDSET de Empresas !", oApp:cAplicacion)
   ShowError(oError)
   oRsEmp   :=   NIL
   RETURN(.F.)
END

oRsEmp:CursorLocation   := adUseClient
oRsEmp:LockType         := adLockOptimistic
oRsEmp:CursorType         := adOpenDynamic
oRsEmp:Source         := "SELECT " +;
                              "* " +;
                           "FROM " +;
                              "empresa"
oRsEmp:ActiveConnection(oApp:oCon)

TRY
   oRsEmp:Open()
CATCH oError
   MsgStop( "No se ha podido abrir el RECORDSET de Empresas !", oApp:cAplicacion)
   ShowError(oError)
   RETURN(.F.)
END

IF oRsEmp:BOF() .AND. oRsEmp:EOF()
ELSE
   oRsEmp:MoveFirst()
ENDIF


Regards

[Armando]

James:

I forgot, this is the right sintaxis.

Code: Select all  Expand view

oRsEmp:Fields("EMP_PDI"):Value


Where the "EMP_PDI" text is the field name of the EMPRESAS table in the oRsEmp Record set.

I hope this can help you.

Regards

[Rochinha]

Bott,

The table is empty?

[James Bott]

Thanks everyone for the responses. However, everything in the example posted in the first message is now working except the oRS:fieldGet( 1 ), which I think must be invalid syntax.

This wasn't working, then it started working.

oRS:Fields("ERROR_CODE"):Value

There must have been something different when it wasn't working but I don't know what it was.

Regards,
James

[anserkk]

Dear Mr.James,

If your field ERROR_CODE is of type VarChar and if you have not done an alltrim while adding/replacing data to this column in the table, then you may get similiar errors when you try to manipulate data of this particular column.

I experienced a similiar problem with MySQl and ADODB

Regards

Anser

[James Bott]

Anser,

Thanks for the tip. Everything is working fine now. I must have had a bug in my code that I didn't notice, and accitdentally fixed, because all of a sudden it started working.

James