FiveTech Software tech support forums

by **nageswaragunupudi** » Tue May 30, 2023 10:08 pm

Code: Select all Expand view: #include "fivewin.ch" #include "adodef.ch" static oCn function Main() local oWnd, oBar FWNumFormat( "A", .t. ) SET DATE FORMAT TO "dd/mm/yyyy" CursorWait() oCn := FW_OpenAdoConnection( { "MYSQL","208.91.198.197:3306", "fwhdemo", "gnraofwh", "Bharat@1950" }, .t. ) DEFINE WINDOW oWnd MDI DEFINE BUTTONBAR oBar OF oWnd SIZE 45,45 2007 DEFINE BUTTON OF oBar PROMPT "TEST" CENTER ACTION Test() oWnd:oWndClient:bPainted := {||oWnd:oWndClient:DrawImage( "c:\fwh\bitmaps\logo.bmp", "BR" )} ACTIVATE WINDOW oWnd return nil static function Test() local oDlg, oWndChild local oBar, oMsgBar, oBrw local oRsHdr CreateTable() oRsHdr := OpenTable() DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL DEFINE BUTTONBAR oBar OF oDlg SIZE 56,42 2007 DEFINE BUTTON OF oBar PROMPT "Add" CENTER ACTION InsertMovs( oBrw ) DEFINE BUTTON OF oBar PROMPT "Zap" CENTER ACTION ( ; oCn:Execute( "TRUNCATE TABLE `HdrVda`" ), ; oRsHdr:ReQuery(), oBrw:Refresh(), oBrw:SetFocus() ) DEFINE BUTTON OF oBar PROMPT "Close" CENTER ACTION oWndChild:End() @ oBar:nHeight, 0 XBROWSE oBrw SIZE 0,-30 PIXEL OF oDlg ; DATASOURCE oRsHdr ; COLUMNS "HDR_FOL","HDR_FDM","HDR_REF",; "IF( HDR_EOS == 1, 'Entrada', 'Salida' )",; "IF( HDR_EOS == 1, HDR_TOU, 0.00 )",; "IF( HDR_EOS == 1, HDR_TOC, 0.00 )",; "IF( HDR_EOS == 2, HDR_TOU, 0.00 )",; "IF( HDR_EOS == 2, HDR_TOC, 0.00 )",; "[SUC_NOM]","HDR_CAN"; HEADERS "Folio","Fecha","Referencia","Tipo","Unidades","Costos","Unidades","Costos","Sucursal","Can"; LINES NOBORDER WITH OBJECT oBrw :bGotFocus := { || oRsHdr:Requery(), oBrw:Refresh() } // :CreateFromCode() END oDlg:oClient := oBrw oDlg:bPostEnd := { || oRsHdr:Close() } ACTIVATE DIALOG oDlg AS MDICHILD SETUP ( ; oWndChild := oWnd, ; oMsgBar := TMsgBar():New( oWnd,,,,,,,,,,.t. ) ) return nil STATIC FUNCTION CreateTable() LOCAL cCmdSql := NIL // oApp:oCon:Execute("USE Test") cCmdSql := "CREATE TABLE IF NOT EXISTS HdrVda (" +; "HDR_FOL INTEGER(03) NOT NULL AUTO_INCREMENT COMMENT 'Folio'," +; "HDR_EOS DECIMAL(01,0) NOT NULL DEFAULT 0 COMMENT 'E=Entrada y S=Salida'," +; "HDR_SUC DECIMAL(03,0) NULL COMMENT 'Número de sucursal'," +; "HDR_FDM DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'Fecha del movimiento'," +; "HDR_REF VARCHAR(15) NOT NULL DEFAULT '' COMMENT 'Referencia'," +; "HDR_TOU DECIMAL(05,0) NOT NULL DEFAULT 0 COMMENT 'Total de unidades'," +; "HDR_TOC DECIMAL(11,2) NOT NULL DEFAULT 0.00 COMMENT 'Total de costos'," +; "HDR_CAN BIT NOT NULL DEFAULT 0 COMMENT 'Cancelado ?'," +; "HDR_MOT VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'Motivo de la cancelación'," +; "PRIMARY KEY(HDR_FOL))" +; "ENGINE = InnoDB COMMENT 'Encabezado de vales de almacén';" oCn:Execute(cCmdSql) RETURN(.T.) STATIC FUNCTION OpenTable() local oRsHdr /* oRsHdr := FW_OpenRecordSet( oCn,"SELECT " +; "*," +; "SUC_NOM " +; "FROM " +; "HdrVda " +; "LEFT JOIN " +; "Sucursales " +; "ON " +; "HDR_SUC = Sucursales.SUC_NUM",adLockOptimistic,adOpenDynamic,0) */ oRsHdr := FW_OpenRecordSet( oCn, "HdrVda" ) IF oRsHdr == nil MsgStop("I can't open the table HDRVDA !",) FW_ShowAdoError( oCn ) RETURN nil ENDIF IF oRsHdr:BOF() .AND. oRsHdr:EOF() ELSE oRsHdr:MoveFirst() ENDIF RETURN oRsHdr STATIC FUNCTION InsertMovs(oBrw) oBrw:oRs:AddNew( { "HDR_EOS", "HDR_FDM", "HDR_TOU", "HDR_TOC" }, ; { 1, Date(), 10, 175.25 } ) oBrw:oRs:AddNew( { "HDR_EOS", "HDR_SUC", "HDR_FDM", "HDR_REF", "HDR_TOU", "HDR_TOC" }, ; { 2, 1, Date(), "F_254", 150, 15000.00 } ) oBrw:oRs:ReQuery() oBrw:Refresh() oBrw:SetFocus() RETURN(.T.)

by **Armando** » Wed May 31, 2023 12:16 am

Mr. Rao:

Could you please add the HDR_SUC column and with LEFT JOIN access the BRANCHES table to obtain the name of the branch
and display it in the BROWSE?

In an input movement the HDR_SUC column must be null

With best regards

by **nageswaragunupudi** » Wed May 31, 2023 4:23 am

ok

Code: Select all Expand view: #include "fivewin.ch" #include "adodef.ch" static oCn function Main() local oWnd, oBar FWNumFormat( "A", .t. ) SET DATE FORMAT TO "dd/mm/yyyy" CursorWait() oCn := FW_OpenAdoConnection( { "MYSQL","208.91.198.197:3306", "fwhdemo", "gnraofwh", "Bharat@1950" }, .t. ) DEFINE WINDOW oWnd MDI DEFINE BUTTONBAR oBar OF oWnd SIZE 45,45 2007 DEFINE BUTTON OF oBar PROMPT "TEST" CENTER ACTION Test() oWnd:oWndClient:bPainted := {||oWnd:oWndClient:DrawImage( "c:\fwh\bitmaps\logo.bmp", "BR" )} ACTIVATE WINDOW oWnd return nil static function Test() local oDlg, oWndChild local oBar, oMsgBar, oBrw local oRsHdr CreateTable() oRsHdr := OpenTable() DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL DEFINE BUTTONBAR oBar OF oDlg SIZE 56,42 2007 DEFINE BUTTON OF oBar PROMPT "Add" CENTER ACTION InsertMovs( oBrw ) DEFINE BUTTON OF oBar PROMPT "Zap" CENTER ACTION ( ; oCn:Execute( "TRUNCATE TABLE `HdrVda`" ), ; oRsHdr:ReQuery(), oBrw:Refresh(), oBrw:SetFocus() ) DEFINE BUTTON OF oBar PROMPT "Close" CENTER ACTION oWndChild:End() @ oBar:nHeight, 0 XBROWSE oBrw SIZE 0,-30 PIXEL OF oDlg ; DATASOURCE oRsHdr ; COLUMNS "HDR_FOL","HDR_FDM","HDR_REF",; "IF( HDR_EOS == 1, 'Entrada', 'Salida' )",; "IF( HDR_EOS == 1, HDR_TOU, 0.00 )",; "IF( HDR_EOS == 1, HDR_TOC, 0.00 )",; "IF( HDR_EOS == 2, HDR_TOU, 0.00 )",; "IF( HDR_EOS == 2, HDR_TOC, 0.00 )",; "[SUC_NOM]","HDR_CAN"; HEADERS "Folio","Fecha","Referencia","Tipo","Unidades","Costos","Unidades","Costos","Sucursal","Can"; LINES NOBORDER WITH OBJECT oBrw :bGotFocus := { || oRsHdr:Requery(), oBrw:Refresh() } // :CreateFromCode() END oDlg:oClient := oBrw oDlg:bPostEnd := { || oRsHdr:Close() } ACTIVATE DIALOG oDlg AS MDICHILD SETUP ( ; oWndChild := oWnd, ; oMsgBar := TMsgBar():New( oWnd,,,,,,,,,,.t. ) ) return nil STATIC FUNCTION CreateTable() LOCAL cCmdSql := NIL local c // oApp:oCon:Execute("USE Test") cCmdSql := "CREATE TABLE IF NOT EXISTS HdrVda (" +; "HDR_FOL INTEGER(03) NOT NULL AUTO_INCREMENT COMMENT 'Folio'," +; "HDR_EOS DECIMAL(01,0) NOT NULL DEFAULT 0 COMMENT 'E=Entrada y S=Salida'," +; "HDR_SUC DECIMAL(03,0) NULL COMMENT 'Número de sucursal'," +; "HDR_FDM DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'Fecha del movimiento'," +; "HDR_REF VARCHAR(15) NOT NULL DEFAULT '' COMMENT 'Referencia'," +; "HDR_TOU DECIMAL(05,0) NOT NULL DEFAULT 0 COMMENT 'Total de unidades'," +; "HDR_TOC DECIMAL(11,2) NOT NULL DEFAULT 0.00 COMMENT 'Total de costos'," +; "HDR_CAN BIT NOT NULL DEFAULT 0 COMMENT 'Cancelado ?'," +; "HDR_MOT VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'Motivo de la cancelación'," +; "PRIMARY KEY(HDR_FOL))" +; "ENGINE = InnoDB COMMENT 'Encabezado de vales de almacén';" oCn:Execute(cCmdSql) if !FW_AdoTableExists( "Sucursales" ) FWAdoCreateTable( "Sucursales", {{ "SUC_NOM","C",10,0}}, oCn, "SUC_NUM" ) for each c in { "One", "Two", "Three" } oCn:Execute( "INSERT INTO Sucursales ( SUC_NOM ) VALUES ('" + c + "')" ) next endif RETURN(.T.) STATIC FUNCTION OpenTable() local oRsHdr /* oRsHdr := FW_OpenRecordSet( oCn,"SELECT " +; "*," +; "SUC_NOM " +; "FROM " +; "HdrVda " +; "LEFT JOIN " +; "Sucursales " +; "ON " +; "HDR_SUC = Sucursales.SUC_NUM",adLockOptimistic,adOpenDynamic,0) */ oRsHdr := FW_OpenRecordSet( oCn, ; "SELECT *,S.SUC_NOM FROM HdrVda H LEFT JOIN Sucursales S ON H.HDR_SUC = S.SUC_NUM ORDER BY HDR_FOL" ) IF oRsHdr == nil MsgStop("I can't open the table HDRVDA !",) FW_ShowAdoError( oCn ) RETURN nil ENDIF IF oRsHdr:BOF() .AND. oRsHdr:EOF() ELSE oRsHdr:MoveFirst() ENDIF RETURN oRsHdr STATIC FUNCTION InsertMovs(oBrw) oBrw:oRs:AddNew( { "HDR_EOS", "HDR_FDM", "HDR_TOU", "HDR_TOC" }, ; { 1, Date(), 10, 175.25 } ) oBrw:oRs:AddNew( { "HDR_EOS", "HDR_SUC", "HDR_FDM", "HDR_REF", "HDR_TOU", "HDR_TOC" }, ; { 2, 1, Date(), "F_254", 150, 15000.00 } ) oBrw:oRs:ReQuery() oBrw:Refresh() oBrw:SetFocus() RETURN(.T.)

by **Armando** » Wed May 31, 2023 5:06 pm

Mr. Rao:

Your sample works very fine.

If I use

Code: Select all Expand view: oCn := FW_OpenAdoConnection( { "MYSQL","localhost", "SiaJAEM", "root", "91502127" }, .t. )

Instead

Code: Select all Expand view: oCn := FW_OpenAdoConnection( { "MYSQL","208.91.198.197:3306", "fwhdemo", "gnraofwh", "Bharat@1950" }, .t. )

I got the error, perhaps the problem is my MySql version? what do you think?, or my connector?

I have MySql 5.1 version

BEst regards

by **nageswaragunupudi** » Thu Jun 01, 2023 7:20 am

I got the error

Same Error?

Please make sure that except the connection, please do not make any changes in my program. In particular, retian oRsHdr as local variable only.

Cloud server version is 5.6.39. But I don't this it is version problem.

by **Armando** » Fri Jun 02, 2023 1:42 am

Mr. Rao:

Yes, it's the same error, and I'm sure I haven't changed anything other than the connection

by the way, if I remove the LEFT JOIN code, there is no error at all.

With best regards

by **nageswaragunupudi** » Fri Jun 02, 2023 2:58 am

by the way, if I remove the LEFT JOIN code, there is no error at all.

What is the structure of `Sucursales` table?

by **Armando** » Fri Jun 02, 2023 3:08 am

Mr. Rao:

Here is the full structure of sucursales table

Code: Select all Expand view: cCmdSql := "CREATE TABLE IF NOT EXISTS Sucursales (" +; "SUC_NUM TINYINT NOT NULL AUTO_INCREMENT COMMENT 'Número de sucursal'," +; "SUC_NOM VARCHAR(40) NOT NULL DEFAULT '' COMMENT 'Nombre o Razón Social'," +; "SUC_IAT VARCHAR(05) NOT NULL DEFAULT '' COMMENT 'Código IATA'," +; "SUC_CAL VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'Domicilio'," +; "SUC_NUE VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Número exterior'," +; "SUC_NUI VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Número interior'," +; "SUC_COL VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'Colonia'," +; "SUC_COP VARCHAR(05) NOT NULL DEFAULT '' COMMENT 'Código postal'," +; "SUC_CIU VARCHAR(25) NOT NULL DEFAULT '' COMMENT 'Población'," +; "SUC_MUN VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'Delegación o municipio'," +; "SUC_EST VARCHAR(25) NOT NULL DEFAULT '' COMMENT 'Entidad federativa'," +; "SUC_TEL VARCHAR(25) NOT NULL DEFAULT '' COMMENT 'Teléfono'," +; "SUC_EMA VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'e-mail'," +; "PRIMARY KEY(SUC_NUM))" +; "ENGINE = InnoDB COMMENT 'Sucursales';"

Best regards

by **nageswaragunupudi** » Sun Jun 04, 2023 7:21 am

This is the table I used

Code: Select all Expand view: CREATE TABLE `Sucursales` ( `SUC_NUM` int(11) NOT NULL AUTO_INCREMENT, `SUC_NOM` varchar(10) DEFAULT NULL, PRIMARY KEY (`SUC_NUM`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

by **Armando** » Sun Jun 04, 2023 5:04 pm

Mr. Rao:

I'll try with your structur.

Best regards

by **Armando** » Sun Jun 04, 2023 5:20 pm

Mr. Rao:

I tried with your structure, the problem persists.
I'm going crazy

Regards

by **nageswaragunupudi** » Sun Jun 04, 2023 5:33 pm

Please try my original program on any other computer.
Or try on this cloud server

Code: Select all Expand view: "209.250.245.152","fwh","fwhuser","FiveTech@2022"

by **Armando** » Sun Jun 04, 2023 5:47 pm

Mr. Rao:

With the new connection string I got the same error

At the first time everything is fine, but at the second time i got the same error.

Here is the full code

Code: Select all Expand view: #include "fivewin.ch" #include "adodef.ch" static oCn function Main() local oWnd, oBar FWNumFormat( "A", .t. ) SET DATE FORMAT TO "dd/mm/yyyy" CursorWait() oCn := FW_OpenAdoConnection( { "MYSQL","209.250.245.152","fwh","fwhuser","FiveTech@2022" }, .t. ) // oCn := FW_OpenAdoConnection( { "MYSQL","208.91.198.197:3306", "fwhdemo", "gnraofwh", "Bharat@1950" }, .t. ) // oCn := FW_OpenAdoConnection( { "MYSQL","localhost", "SiaJAEM", "root", "91502127" }, .t. ) DEFINE WINDOW oWnd MDI DEFINE BUTTONBAR oBar OF oWnd SIZE 45,45 2007 DEFINE BUTTON OF oBar PROMPT "TEST" CENTER ACTION Test() oWnd:oWndClient:bPainted := {||oWnd:oWndClient:DrawImage( "c:\fwh\bitmaps\logo.bmp", "BR" )} ACTIVATE WINDOW oWnd return nil static function Test() local oDlg, oWndChild local oBar, oMsgBar, oBrw local oRsHdr CreateTable() oRsHdr := OpenTable() DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL DEFINE BUTTONBAR oBar OF oDlg SIZE 56,42 2007 DEFINE BUTTON OF oBar PROMPT "Add" CENTER ACTION InsertMovs( oBrw ) DEFINE BUTTON OF oBar PROMPT "Zap" CENTER ACTION ( ; oCn:Execute( "TRUNCATE TABLE `HdrVda`" ), ; oRsHdr:ReQuery(), oBrw:Refresh(), oBrw:SetFocus() ) DEFINE BUTTON OF oBar PROMPT "Close" CENTER ACTION oWndChild:End() @ oBar:nHeight, 0 XBROWSE oBrw SIZE 0,-30 PIXEL OF oDlg ; DATASOURCE oRsHdr ; COLUMNS "HDR_FOL","HDR_FDM","HDR_REF",; "IF( HDR_EOS == 1, 'Entrada', 'Salida' )",; "IF( HDR_EOS == 1, HDR_TOU, 0.00 )",; "IF( HDR_EOS == 1, HDR_TOC, 0.00 )",; "IF( HDR_EOS == 2, HDR_TOU, 0.00 )",; "IF( HDR_EOS == 2, HDR_TOC, 0.00 )",; "[SUC_NOM]","HDR_CAN"; HEADERS "Folio","Fecha","Referencia","Tipo","Unidades","Costos","Unidades","Costos","Sucursal","Can"; LINES NOBORDER WITH OBJECT oBrw :bGotFocus := { || oRsHdr:Requery(), oBrw:Refresh() } // :CreateFromCode() END oDlg:oClient := oBrw oDlg:bPostEnd := { || oRsHdr:Close() } ACTIVATE DIALOG oDlg AS MDICHILD SETUP ( ; oWndChild := oWnd, ; oMsgBar := TMsgBar():New( oWnd,,,,,,,,,,.t. ) ) return nil STATIC FUNCTION CreateTable() LOCAL cCmdSql := NIL local c // oApp:oCon:Execute("USE Test") cCmdSql := "CREATE TABLE IF NOT EXISTS HdrVda (" +; "HDR_FOL INTEGER(03) NOT NULL AUTO_INCREMENT COMMENT 'Folio'," +; "HDR_EOS DECIMAL(01,0) NOT NULL DEFAULT 0 COMMENT 'E=Entrada y S=Salida'," +; "HDR_SUC DECIMAL(03,0) NULL COMMENT 'Número de sucursal'," +; "HDR_FDM DATE NULL COMMENT 'Fecha del movimiento'," +; "HDR_REF VARCHAR(15) NOT NULL DEFAULT '' COMMENT 'Referencia'," +; "HDR_TOU DECIMAL(05,0) NOT NULL DEFAULT 0 COMMENT 'Total de unidades'," +; "HDR_TOC DECIMAL(11,2) NOT NULL DEFAULT 0.00 COMMENT 'Total de costos'," +; "HDR_CAN BIT NOT NULL DEFAULT 0 COMMENT 'Cancelado ?'," +; "HDR_MOT VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'Motivo de la cancelación'," +; "PRIMARY KEY(HDR_FOL))" +; "ENGINE = InnoDB COMMENT 'Encabezado de vales de almacén';" // "HDR_FDM DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'Fecha del movimiento'," +; oCn:Execute(cCmdSql) if !FW_AdoTableExists( "Sucursales" ) FWAdoCreateTable( "Sucursales", {{ "SUC_NOM","C",10,0}}, oCn, "SUC_NUM" ) for each c in { "One", "Two", "Three" } oCn:Execute( "INSERT INTO Sucursales ( SUC_NOM ) VALUES ('" + c + "')" ) next endif RETURN(.T.) STATIC FUNCTION OpenTable() local oRsHdr oRsHdr := FW_OpenRecordSet( oCn, ; "SELECT *,S.SUC_NOM FROM HdrVda H LEFT JOIN Sucursales S ON H.HDR_SUC = S.SUC_NUM ORDER BY HDR_FOL" ) //// "SELECT * FROM HdrVda ORDER BY HDR_FOL" IF oRsHdr == nil MsgStop("I can't open the table HDRVDA !",) FW_ShowAdoError( oCn ) RETURN nil ENDIF IF oRsHdr:BOF() .AND. oRsHdr:EOF() ELSE oRsHdr:MoveFirst() ENDIF RETURN oRsHdr STATIC FUNCTION InsertMovs(oBrw) oBrw:oRs:AddNew( { "HDR_EOS", "HDR_FDM", "HDR_TOU", "HDR_TOC" }, ; { 1, Date(), 10, 175.25 } ) oBrw:oRs:AddNew( { "HDR_EOS", "HDR_SUC", "HDR_FDM", "HDR_REF", "HDR_TOU", "HDR_TOC" }, ; { 2, 1, Date(), "F_254", 150, 15000.00 } ) oBrw:oRs:ReQuery() oBrw:Refresh() oBrw:SetFocus() RETURN(.T.)

Perhaps you can see my fault.

Best regards

by **Cgallegoa** » Mon Jun 05, 2023 11:45 pm

Armando:

Instead of:

Code: Select all Expand view: "SELECT *,S.SUC_NOM FROM HdrVda H LEFT JOIN Sucursales S ON H.HDR_SUC = S.SUC_NUM ORDER BY HDR_FOL"

Use

Code: Select all Expand view: "SELECT H.*,S.SUC_NOM FROM HdrVda H LEFT JOIN Sucursales S ON H.HDR_SUC = S.SUC_NUM ORDER BY HDR_FOL"

"H.*" make the trick.

Regards,

FiveTech Software tech support forums

Problem with xBrowse (Fixed)

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Re: Problem with xBrowse

Who is online