Xbrowse errors using ADO and column READ

[Rick Lipkin]

To All

Using SQL Server and migrating an app from twbrowse to xbrowse and for some reason the Column "READ" errors when creating the browse.

I do realize "READ" is a protected word .. however the sql statement used is "select * from Userinfo" which includes the column "READ" .. I have used [read] when I just want this column .. However, for some reason I can not get this code to fail on my local instance of SQL server so I can not replicate the error ..I am using FWH 910 and xHarbour .. here is the code for the browse and the error ..

Any help would be appreciated.

Rick Lipkin

Code: Select all | Expand

oRsuser := TOleAuto():New( "ADODB.Recordset" )
oRsuser:CursorType     := 1        // opendkeyset
oRsuser:CursorLocation := 3        // local cache
oRsuser:LockType       := 3        // lockoportunistic

cSQL := "SELECT * FROM USERINFO order by USERID"

TRY
   oRsuser:Open( cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
   MsgInfo( "Error in Opening USERINFO table" )
   RETURN(.F.)
END TRY

// someone has to already exist in this table to get this far //

oRsuser:Find("USERID = '"+xLOGIN+"'" )
IF oRsuser:eof
   oRsuser:MoveFirst()
ENDIF

DEFINE ICON oICO RESOURCE "SCANNER"

DEFINE WINDOW oUser                        ;
      FROM 2,2 to 25,65                    ;
      of oWndMDI                           ;
      TITLE "USERINFO Administrative Browse" ;
      MENU BuildMenu(oRsUser)              ;
      ICON oICO ;
      NOMINIMIZE                           ;
      NOZOOM                               ;
      MDICHILD

@ 0, 0 xBROWSE oBrow of oUser              ;
       RECORDSET oRsUser                   ;
       COLUMNS "USERID",                   ;
               "READ",                     ;
               "WRITE",                    ;
               "MGR",                      ;
               "SUPER",                    ;
               "lastlog"                   ;
       COLSIZES 90,60,60,60,60,100         ;
       HEADERS "Userid",                   ;
               "Read",                     ;
               "Write",                    ;
               "ProjMgr",                  ;
               "Super",                    ;
               "Last Login"                ;
       AUTOSORT AUTOCOLS LINES CELL  // <<-- errors here  line 73

       oUSER:oClient := oBrow
       oBrow:bLDblClick := { |nRow,nCol | _Userview( "V", oRsUser ) }

       oCol := oBrow:aCols[ 2 ]
       oCol:bStrData := { |x| x := oRsUser:Fields("read"):Value,    If( Empty(x), 'Y', x ) }

       oCol := oBrow:aCols[ 6 ]
       oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }


       oBrow:CreateFromCode()

ACTIVATE WINDOW oUser           ;
    ON INIT( oBrow:SetFocus(), .F. );
    ON PAINT( NIL );
    VALID ( IIF( !lOK, UserClose(.T., oRsUser), .F. ))

RETURN( NIL )
 

[code]
Application
===========
Path and name: C:\Documents and Settings\watsonjt\My Documents\Pmo11\Pmo11\pmow32.Exe (32 bits)
Size: 843,264 bytes
Time from start: 0 hours 0 mins 14 secs
Error occurred at: 07/12/2011, 07:25:02
Error description: Error ADODB.Recordset/6 DISP_E_UNKNOWNNAME: FIELDS
Args:
[ 1] = C READ

Stack Calls
===========
Called from: source\rtl\win32ole.prg => TOLEAUTO:FIELDS(0)
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:SETCOLFROMADO(4123)
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:SETADO(3988)
Called from: .\source\classes\XBROWSE.PRG => XBRWSETDATASOURCE(10553)
Called from: .\source\classes\XBROWSE.PRG => XBROWSENEW(10375)
Called from: USERBROW.PRG => _USERBROW(73)
Called from: UTILMENU.PRG => (b)_UTILMENU(40)
Called from: .\source\classes\BUTTON.PRG => TBUTTON:CLICK(176)
Called from: .\source\classes\CONTROL.PRG => TBUTTON:HANDLEEVENT(1427)
Called from: .\source\classes\WINDOW.PRG => _FWH(3347)
Called from: => SENDMESSAGE(0)
Called from: .\source\classes\DIALOG.PRG => TDIALOG:COMMAND(407)
Called from: => TWINDOW:HANDLEEVENT(0)
Called from: .\source\classes\DIALOG.PRG => TDIALOG:HANDLEEVENT(928)
Called from: .\source\classes\WINDOW.PRG => _FWH(3347)
Called from: => WINRUN(0)
Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE(966)
Called from: MAIN.PRG => MAIN(410)

[code]

[nageswaragunupudi]

You can not use a reserved word in the columns statement. Create browse without that column and then use

ADD TO oBrw AT 2 DATA { || .... oRs:Fields( <fldnum> ):Value ... } ...
statement.
Having seen your problem, I am considering modifying xbrowse code to handle even reserved words safely in the next versions.
_

[Rick Lipkin]

Rao

Thanks .. just curious .. I have no clue why the same code works on my local install of Sql server .. the production box is client\server which is the one giving me the error.

I will try your suggestion and let you know.

Rick

[Rick Lipkin]

Rao

Your suggestion worked !! .. how to I assign a colsize ? ..

Thanks
Rick Lipkin

Code: Select all | Expand

DEFINE WINDOW oUser                        ;
      FROM 2,2 to 25,65                    ;
      of oWndMDI                           ;
      TITLE "USERINFO Administrative Browse" ;
      MENU BuildMenu(oRsUser)              ;
      ICON oICO ;
      NOMINIMIZE                           ;
      NOZOOM                               ;
      MDICHILD

@ 0, 0 xBROWSE oBrow of oUser              ;
       RECORDSET oRsUser                   ;
       COLUMNS "USERID",                   ;
               "WRITE",                    ;
               "MGR",                      ;
               "SUPER",                    ;
               "lastlog"                   ;
       COLSIZES 90,60,60,60,100            ;
       HEADERS "Userid",                   ;
               "Write",                    ;
               "ProjMgr",                  ;
               "Super",                    ;
               "Last Login"                ;
       AUTOSORT AUTOCOLS LINES CELL

       oUSER:oClient := oBrow
       oBrow:bLDblClick := { |nRow,nCol | _Userview( "V", oRsUser ) }
   
       ADD oCol TO oBrow AT 2 HEADER "Read" // << assign a colsize ?
   
       oCol := oBrow:aCols[ 6 ]
       oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }


       oBrow:CreateFromCode()

ACTIVATE WINDOW oUser           ;
    ON INIT( oBrow:SetFocus(), .F. );
    ON PAINT( NIL );
    VALID ( IIF( !lOK, UserClose(.T., oRsUser), .F. ))

RETURN( NIL )
 

[nageswaragunupudi]

ADD TO oBrw ............ WIDTH <n> or SIZE <n>

[Rick Lipkin]

Rao

Thanks for your help

Rick

[Rick Lipkin]

Rao

Unfortunately I am just now getting around to really testing your code and I do not get a value for the READ column ..
I had the line ( in blue ) in the code previously .. and it works on my box but fails in production ..

Any ideas ??
Rick Lipkin




[code]
DEFINE ICON oICO RESOURCE "SCANNER"

DEFINE WINDOW oUser ;
FROM 2,2 to 25,65 ;
of oWndMDI ;
TITLE "USERINFO Administrative Browse" ;
MENU BuildMenu(oRsUser) ;
ICON oICO ;
NOMINIMIZE ;
NOZOOM ;
MDICHILD

@ 0, 0 xBROWSE oBrow of oUser ;
RECORDSET oRsUser ;
COLUMNS "USERID", ;
"WRITE", ;
"MGR", ;
"SUPER", ;
"lastlog" ;
COLSIZES 90,60,60,60,100 ;
HEADERS "Userid", ;
"Write", ;
"ProjMgr", ;
"Super", ;
"Last Login" ;
AUTOSORT AUTOCOLS LINES CELL

oUSER:oClient := oBrow
oBrow:bLDblClick := { |nRow,nCol | _Userview( "V", oRsUser ) }

ADD oCol TO oBrow AT 2 HEADER "Read" size 60 //RIGHT

// works on my machine but fails in production //

* oCol := oBrow:aCols[ 2 ]
* oCol:bStrData := { |x| x := oRsUser:Fields("read"):Value, If( Empty(x), 'Y', x ) }

oCol := oBrow:aCols[ 6 ]
oCol:bStrData := { |x| x := oRsUser:Fields("lastlog"):Value, If( Empty(x), '00/00/0000',DToC(x) ) }

oBrow:CreateFromCode()

ACTIVATE WINDOW oUser ;
ON INIT( oBrow:SetFocus(), .F. );
ON PAINT( NIL );
VALID ( IIF( !lOK, UserClose(.T., oRsUser), .F. ))

RETURN( NIL )
[code]

[nageswaragunupudi]

Let us assume the field number of 'READ' in the record set is 1 (starting from 0 ). Then

After creating xbrowse

ADD TO oBrw DATA {|x| x := oRsUser:Fields(1):Value, If(Empty(x),"Y",x)} HEADER "Read"

oBrw:aCols[6]:bStrData := { || ............ }

[Rick Lipkin]

Rao

Thanks for your help .. This appears to be the fix

ADD oCol TO oBrow AT 2 DATA {|x| x := oRsUser:Fields(3):Value} HEADER "Read" size 60

I have sent it to my customer to try in their environment..

Rick