query on mdb

[Silvio]

I made the listbox of the products ( see the picture) of a MDB

I want show the name of each category and subcategory and not the number

How I can made for search on category table the description and insert on listbox ?

I made :

Code: Select all

 IF oDb != NIL .AND. oDb:State == adStateOpen

      IF OpenTables()


 aBrowsep   := { { { || STR( oPs:Fields( "ID" ):Value) }, i18n("Contatore"), 50, 0 },;
                  { { || oPs:Fields( "CODICE" ):Value }, i18n("Codice"), 150, 0 },;
                    { { || STR( oPs:Fields( "SCATEGORIA" ):Value)} , i18n("Categoria"), 150, 0 } ,;
                  { { || STR( oPs:Fields( "CATEGORIA" ):Value)}, i18n("Sotto categoria"), 150, 0 },;
                  { { || oPs:Fields( "PRODOTTO" ):Value} , i18n("Prodotto"), 150, 0 }}


 FOR i := 1 TO Len(aBrowsep)
      oCol := oApp():oGrid:AddCol()
      oCol:bStrData := aBrowsep[ i, 1 ]
      oCol:cHeader  := aBrowsep[ i, 2 ]
      oCol:nWidth   := aBrowsep[ i, 3 ]
      oCol:nDataStrAlign := aBrowsep[ i, 4 ]
      oCol:nHeadStrAlign := aBrowsep[ i, 4 ]
   NEXT

   oApp():oGrid:SetADO( oPs )
   oApp():oGrid:CreateFromCode()


   oApp():oGrid:bChange  := { || RefreshCont(oCont,oPs) }

   oApp():oGrid:nRowHeight  := 21
   oApp():oGrid:nHeaderHeight  := 36


  oApp():oGrid:bClrStd := { || Colores(oPs) }
  oApp():oGrid:RestoreState( cState )


   // colors
      oApp():oGrid:bClrHeader :=   { || { CLR_BLUE, RGB(143,172,230)} }
      oApp():oGrid:nColDividerStyle    := LINESTYLE_FORECOLOR
      oApp():oGrid:nMarqueeStyle       := MARQSTYLE_HIGHLROW






      oPs:MoveFirst()


ACTIVATE DIALOG oApp():oDlg NOWAIT


STATIC FUNCTION ConnectToDataBase
   oDB                    := TOLEAuto():New( "ADODB.Connection" )
   oDB:ConnectionString   := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ecom.mdb;User Id=admin;Password=;"
   oDB:CursorLocation     := adUseClient
   TRY
      oDB:Open()
   CATCH
      oDB                  := NIL
      MsgInfo('Connect Fail')
      RETURN NIL
   END
RETURN oDB




 STATIC FUNCTION OpenTables
    oPs          := TOLEAuto():New( "ADODB.RecordSet" )
   WITH OBJECT  oPs
      :ActiveConnection    := oDB
      :CursorType          := adOpenDynamic
      :LockType            := adLockOptimistic
      :Source              := "PRODOTTI"
      :CursorLocation      := adUseClient
   END

   TRY
       oPs :Open()
   CATCH
      oPs                   := NIL
      MsgInfo( "Attenzione - Non posso aprire la tabella Prodotti" )
   END


     IF  oPs  != NIL
      RETURN .T.
   ENDIF

     oSc         := TOLEAuto():New( "ADODB.RecordSet" )
   WITH OBJECT oSc
      :ActiveConnection    := oDB
      :CursorType          := adOpenDynamic
      :LockType            := adLockOptimistic
      :Source              := "SCATEGORIE"
      :CursorLocation      := adUseClient
   END

   TRY
      oSc:Open()
   CATCH
      oSc                  := NIL
      MsgInfo( "ATTENZIONE - Non posso aprire la tabella SCATEGORIE" )
   END

   oCt         := TOLEAuto():New( "ADODB.RecordSet" )
   WITH OBJECT oCt
      :ActiveConnection    := oDB
      :CursorType          := adOpenDynamic
      :LockType            := adLockOptimistic
      :Source              := "CATEGORIE"
      :CursorLocation      := adUseClient
   END

   TRY
      oCt:Open()
   CATCH
      oCt                  := NIL
      MsgInfo( "ATTENZIONE - Non posso aprire la tabella CATEGORIE" )
   END

   IF oSc != NIL .AND. oCt != NIL
      RETURN .T.
   ENDIF

RETURN .F.

can I made somethig of it to search the name of category ?

Code: Select all

STATIC FUNCTION search()

   oCt:Search := "SCATEGORIA = " + STR( oPs:Fields( "SCATEGORIA" ):Value)

   IF oCt:RecordCount() > 0
      oCt:MoveFirst()
   ENDIF

RETURN  oCt:Fields( "Categoria" ):Value