FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexActive topicsAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Listbox via MS SQL
cdmmaui
Posts: 708
Joined: Fri Oct 28, 2005 09:53 AM

Listbox via MS SQL

Posted: Wed Jul 04, 2012 05:16 AM

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 )

*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
Antonio Linares
Posts: 44162
Joined: Thu Oct 06, 2005 05:47 PM

Re: Listbox via MS SQL

Posted: Thu Jul 05, 2012 06:22 AM

Darrell,

Please try this before the ACTIVATE DIALOG :

MsgInfo( oSql:RecordCount )

regards, saludos

Antonio Linares
www.fivetechsoft.com
cdmmaui
Posts: 708
Joined: Fri Oct 28, 2005 09:53 AM

Re: Listbox via MS SQL

Posted: Thu Jul 05, 2012 07:33 AM

Hi Antonio,

Thank you. I found problem and resolved it.

*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
anserkk
Posts: 1335
Joined: Fri Jun 13, 2008 11:04 AM

Re: Listbox via MS SQL

Posted: Thu Jul 05, 2012 07:46 AM
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 (fw): Select all Collapse
oSql:CursorLocation := adUseClient


Code (fw): Select all Collapse
// 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
Posts: 44162
Joined: Thu Oct 06, 2005 05:47 PM

Re: Listbox via MS SQL

Posted: Thu Jul 05, 2012 08:41 AM

Darrell,

What was the problem ? How did you fix it ?

thanks

regards, saludos

Antonio Linares
www.fivetechsoft.com
Rick Lipkin
Posts: 2706
Joined: Fri Oct 07, 2005 01:50 PM

Re: Listbox via MS SQL

Posted: Thu Jul 05, 2012 12:41 PM
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 (fw): Select all Collapse
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
Posts: 357
Joined: Thu Nov 02, 2006 06:53 PM

Re: Listbox via MS SQL

Posted: Wed Jul 11, 2012 08:17 PM

where was the problem ?

best regards

kajot

Continue the discussion

Static archive · New replies & topics via GitHub Discussions