Listbox via MS SQL

[cdmmaui]

Hello,

I am having a problem displaying MS SQL data in a listbox, can someone help me and tell me what I am doing wrong? Also, should I switch to XBROWSE instead of LISTBOX?

I appreciate your assistance?

*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*`*
#include "FiveWin.ch"
//----------------------------------------------------------------//
function Main()

local cConnectString := "" , ;
cSql := "" , ;
oSql , ;
oDlg , ;
oLbx

// Connecting to MS SQL Database
cConnectString:="Provider=SQLOLEDB;server=64.78.59.231;database=globallogistics;uid=cdmwoodlands;pwd=Alexander1209"

// Connection Object ====> oSql:=CreateObject("ADODB.Connection")
TRY
oSql:=CreateObject("ADODB.Recordset")
CATCH
MsgInfo("It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END
// MsgInfo( "Object Created!")

// Set SQL string...
cSql := "SELECT * FROM support"

TRY
oSql:Open( cSql, cConnectString )
CATCH oError
MsgInfo("Failed to Connect to the Database")
RETURN .F.
END
//MsgInfo( "Yay!!! Here We Go!!!" )

// Check for EOF...
IF oSql:eof
MsgInfo( "No Data Found" )
oSql:Close()
oSql:=NIL
RETURN (.F.)
ENDIF

// Move to first...
oSql:MoveFirst()

// Dialog...
DEFINE DIALOG oDlg FROM 1,1 TO 35,104 TITLE "Support Ticket"

@00,00 LISTBOX oLbx FIELDS ;
oSql:Fields( "company_name" ):Value , ;
oSql:Fields( "contact_first" ):Value , ;
oSql:Fields( "contact_last" ):Value , ;
oSql:Fields( "contact_phone" ):Value , ;
oSql:Fields( "contact_email" ):Value , ;
oSql:Fields( "system" ):Value , ;
oSql:Fields( "module" ):Value , ;
oSql:Fields( "adddate" ):Value , ;
oSql:Fields( "addtime" ):Value , ;
oSql:Fields( "adduser" ):Value ;
SIZES 100 , 100 , 100 , 100 , 100 , 100 , 100 , 100 , 100 , 100 ;
HEADERS "Company", "First Name", "Last Name", "Telephone", "E-mail", "System", "Module", "Add Date", "Add Time", "Add User"

oLbx:bLogicLen = { || oSql:RecordCount }
oLbx:bGoTop = { || oSql:MoveFirst() }
oLbx:bGoBottom = { || oSql:MoveLast() }
oLbx:bSkip = { | nSkip | Skipper( oSql, nSkip ) }
oLbx:cAlias = "ARRAY1"

ACTIVATE DIALOG oDlg ON INIT ( oDlg:SetControl( oLbx ), Sysrefresh() )

// Disconnecting
oSql:Close()
oSql:=NIL

RETURN (.T.)

//-------------------------------
STATIC FUNCTION SKIPPER( oRsx, nSkip )

LOCAL nRec := oRsx:AbsolutePosition

oRsx:Move( nSkip )

IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF

RETURN( oRsx:AbsolutePosition - nRec )

[Antonio Linares]

Darrell,

Please try this before the ACTIVATE DIALOG :

MsgInfo( oSql:RecordCount )

[cdmmaui]

Hi Antonio,

Thank you. I found problem and resolved it.

[anserkk]

The problem is with your cursor location of the recordset. It will be Server by default. Please change it to use Client before opening the recodset.

Code: Select all  Expand view

oSql:CursorLocation := adUseClient

Code: Select all  Expand view

// Set SQL string...
cSql := "SELECT * FROM support"
oSql:CursorLocation := adUseClient
TRY
   oSql:Open( cSql, cConnectString )
CATCH oError
   MsgInfo("Failed to Connect to the Database")
   RETURN .F.
END

It will work fine.
Regards
Anser

[Antonio Linares]

Darrell,

What was the problem ? How did you fix it ?

thanks

[Rick Lipkin]

Darrell

I have been using ADO for many years and found when you set up your recordset object to use the opendkeyset, local cache and lockoportunistic options. Most importantly is the Local Cache option which takes your recordset and places it in local memory of the workstation. You will find your data ( especially in browses ) MUCH FASTER than without using the Local Cache option.

Here is some quick code using xBrowse .. notice you do not need the Skipper() function and you have a lot more control over the code blocks in creating your listbox.

Rick Lipkin

Code: Select all  Expand view

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

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

TRY
   oRsuser:Open( cSQL,cConnectString )
CATCH oErr
   MsgInfo( "Error in Opening USERINFO table" )
   RETURN(.F.)
END TRY

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",                   ;
               "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 DATA {|x| x := oRsUser:Fields(3):Value} HEADER "Read" size 60
     *  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. );
    VALID ( IIF( !lOK, UserClose(.T., oRsUser), .F. ))

RETURN( NIL )


 

[kajot]

where was the problem ?