Xbrowse errors using ADO and column READ

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Xbrowse errors using ADO and column READ
Posted: Tue Jul 12, 2011 11:25 PM

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 (fw): Select all Collapse

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Xbrowse errors using ADO and column READ
Posted: Wed Jul 13, 2011 05:36 AM

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.
_

Regards

G. N. Rao.

Hyderabad, India

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Xbrowse errors using ADO and column READ
Posted: Wed Jul 13, 2011 09:38 PM

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

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Xbrowse errors using ADO and column READ
Posted: Thu Jul 14, 2011 04:04 PM

Rao

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

Thanks
Rick Lipkin

Code (fw): Select all Collapse

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Xbrowse errors using ADO and column READ
Posted: Thu Jul 14, 2011 04:44 PM

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

Regards

G. N. Rao.

Hyderabad, India

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Xbrowse errors using ADO and column READ
Posted: Thu Jul 14, 2011 06:04 PM

Rao

Thanks for your help

Rick

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Xbrowse errors using ADO and column READ
Posted: Sun Jul 17, 2011 04:23 PM

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Xbrowse errors using ADO and column READ
Posted: Sun Jul 17, 2011 04:50 PM

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 := { || ............ }

Regards

G. N. Rao.

Hyderabad, India

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Xbrowse errors using ADO and column READ
Posted: Mon Jul 18, 2011 12:50 PM

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

FiveTech Support Forums

Continue the discussion