Test if a Ms Access SQL field is Number

[Rick Lipkin]

To All

How can I test if a Sql field is a character value type. I can test for the valtype of a variable assigned from a field is character, but if the table is empty there is no way to assign a field value to a variable to test its valtype ?

cName := oRs:Fields("Covered By Warranty"):Type .. returns the position of the field in the structure but not its valtype ..

Rick Lipkin

[Armando]

Rick:

Long time ago I made this function perhaps these can help you

Code: Select all  Expand view

/*
* --------------------------------------------------------------------------*
* Lee las tablas y sus estructuras                                          *
* --------------------------------------------------------------------------*
*/
STATIC FUNCTION LeeTablas()
LOCAL oCat
LOCAL nVez, j

oCat = CREATEOBJECT( "ADOX.Catalog" )
oCat:ActiveConnection := oApp:oCon:ConnectionString

FOR nVez := 0 TO oCat:Tables:Count() - 1
    MsgInfo(oCat:Tables(nVez):Name)

    IF UPPER(ALLTRIM(oCat:Tables(nVez):Name)) == "PROYECTO"
        FOR j := 0 TO oCat:Tables(nVez):Columns:Count() - 1
            ? SPACE( 4 ) + oCat:Tables(nVez):Columns( j ):Name
            ? SPACE( 4 ) + STR(oCat:Tables(nVez):Columns( j ):Type,3,0)
            ? SPACE( 4 ) + STR(oCat:Tables(nVez):Columns( j ):DefinedSize,5,0)
            ? SPACE( 4 ) + STR(oCat:Tables(nVez):Columns( j ):Precision)
            ? SPACE( 4 ) + STR(oCat:Tables(nVez):Columns( j ):NumericScale)
        NEXT
    ENDIF

NEXT
/*
          NAME: Nombre del campo
      TYPE: 200 = VARCHAR, 133 = DATE, 201 = TEXT, 131 = DECIMAL
 DEFINEDSIZE: Longitud del campo, solo para tipos VARCHAR y TEXT
   PRECISION: Longitud del campo, solo para tipos DECIMAL
NUMERICSCALE: Número de decimales, solo para tipos NUMERIC y DECIMAL
*/
RETURN(NIL)
 

Best regards

[Rick Lipkin]

Armando

Your code looks promising .. how did you create your connection ?

Code: Select all  Expand view

oCat:ActiveConnection := oApp:oCon:ConnectionString
 

Thanks
Rick Lipkin

[Rick Lipkin]

Armando

I figured out the connection ..

Code: Select all  Expand view

oCn  := CREATEOBJECT( "ADODB.Connection" )

TRY
   oCn:Open( xCONNECT )
CATCH oErr
   Saying := "Could not open a Connection to Database "+xSource+chr(10)
   MsgInfo( Saying )
   RETURN(.F.)
END TRY

Try
  oCat:ActiveConnection := oCn
Catch
   Saying := "Could not open connection to ADOX"
   MsgInfo( Saying )
   Return(.f.)
End Try
 

The rest of your code worked perfectly !!

Thanks
Rick Lipkin

[Armando]

Rick:

Here is my conection code

Code: Select all  Expand view

TRY
    oApp:oCon   :=  TOleAuto():new("adodb.connection")
CATCH oError
    MsgStop( "No se ha podido crear la conexión al servidor !", oApp:cAplicacion)
    RETURN(.F.)
END

oApp:oCon:ConnectionString := ALLTRIM(oApp:cDriveName) + ;
                                        ";Server=" + ALLTRIM(oApp:cServer) + ;
                                        ";Port=" + ALLTRIM(oApp:cPort) + ;
                                        ";Database=" + ALLTRIM(oApp:cDataBase) + ;
                                        ";User=" + ALLTRIM(oApp:cUser)+;
                                        ";Password=" + ALLTRIM(oApp:cPassWord) + ;
                                        ";Option=3;"

TRY
    oApp:oCon:Open()
CATCH oError
    MsgInfo("No se pudo lograr la conexión al servidor, REVISE LA CONEXION DE SU RED O LA CONEXION A INTERNET !",oApp:cAplicacion)
    ShowError(oError)
    RETURN(.F.)
END
 

Regards