Test if a Ms Access SQL field is Number

Test if a Ms Access SQL field is Number

Postby Rick Lipkin » Thu Oct 25, 2012 1:30 pm

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
User avatar
Rick Lipkin
 
Posts: 2666
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Test if a Ms Access SQL field is Number

Postby Armando » Thu Oct 25, 2012 2:58 pm

Rick:

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

Code: Select all  Expand view  RUN

/*
* --------------------------------------------------------------------------*
* 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
SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero
User avatar
Armando
 
Posts: 3229
Joined: Fri Oct 07, 2005 8:20 pm
Location: Toluca, México

Re: Test if a Ms Access SQL field is Number

Postby Rick Lipkin » Thu Oct 25, 2012 4:13 pm

Armando

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

Code: Select all  Expand view  RUN

oCat:ActiveConnection := oApp:oCon:ConnectionString
 


Thanks
Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2666
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Test if a Ms Access SQL field is Number

Postby Rick Lipkin » Thu Oct 25, 2012 5:13 pm

Armando

I figured out the connection ..

Code: Select all  Expand view  RUN

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
User avatar
Rick Lipkin
 
Posts: 2666
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Test if a Ms Access SQL field is Number

Postby Armando » Thu Oct 25, 2012 6:45 pm

Rick:

Here is my conection code

Code: Select all  Expand view  RUN

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
SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero
User avatar
Armando
 
Posts: 3229
Joined: Fri Oct 07, 2005 8:20 pm
Location: Toluca, México


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 57 guests