Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get records using combo box in vfp9 1

Status
Not open for further replies.
Niki_S

Niki_S

Programmer
Jun 4, 2021
232
LK
I have a combo box and there have 3 records such as,
[ul]
[li]All GRN[/li]
[li]Completed GRN[/li]
[li]Pending GRN[/li]
[/ul]

Now I want to get records from my MSSQL tables using these 3 records. For that I used like this code. But this is not working.

Code: 
IF !EMPTY(thisform.cboReportType.DisplayValue) then  

	IF (thisform.cboReportType.Value = 'Pending GRN') then
		stra="SELECT  cBatchNo, cAdvanceRef, dAdvanceDate, cFactAccno FROM MAS.dbo.Acp_AdvancePayment where  "
		stra=stra+" Acp_AdvancePayment.dAdvanceDate>= ?thisform.txtFrom.Value AND Acp_AdvancePayment.dAdvanceDate<= ?thisform.txtTo.Value"
		SQLEXEC(hndOps,stra,'_Pending')
	ENDIF
	
	IF (thisform.cboReportType.Value = 'Completed GRN') then
		stra="SELECT  cBatchNo, nPaymentTypeID, cPaymentNumber, dPaymentDate FROM MAS.dbo.Acp_FinalPayment where  "
		stra=stra+" Acp_FinalPayment.dPaymentDate>= ?thisform.txtFrom.Value AND Acp_FinalPayment.dPaymentDate<= ?thisform.txtTo.Value"
		SQLEXEC(hndOps,stra,'_Completed')
	ENDIF
	
	IF (thisform.cboReportType.Value = 'All GRN') then
		stra="SELECT  cBatchNo, nPaymentTypeID, cPaymentNumber, dPaymentDate FROM MAS.dbo.Acp_Batch where  "
		stra=stra+"  Acp_Batch.dPaymentDate>= ?thisform.txtFrom.Value AND  Acp_Batch.dPaymentDate<= ?thisform.txtTo.Value"
		SQLEXEC(hndOps,stra,'_All')
	ENDIF 
		

ENDIF

How can I get records into my cursors using this logic?
Thank you
 
Sort by date Sort by votes
Code: 
IF !EMPTY(thisform.cboReportType.DisplayValue) then  
    lcFldName = "dPaymentDate"
    lcTblName = "Acp_FinalPayment"
    lcCrsName = "_Completed"
    DO CASE
       CASE thisform.cboReportType.Value = 'Pending GRN'
            lcFldName = "dAdvanceDate"
            lcCrsName = "_Pending"
           lcTblName = "Acp_AdvancePayment"

       CASE thisform.cboReportType.Value = 'Completed GRN'
            lcFldName = "dPaymentDate"
            lcCrsName = "_Completed"
            lcTblName = "Acp_FinalPayment"

       CASE thisform.cboReportType.Value = 'All'
            lcFldName = "dPaymentDate"
            lcCrsName = "_All"
            lcTblName = "Acp_Batch"
   ENDCASE
   TEXT TO lcSQL NOSHOW TEXTMERGE
        SELECT cBatchNo
              ,cAdvanceRef
              ,dAdvanceDate
              ,cFactAccno 
        FROM MAS.dbo.<<m.lcTblName>>
        where <<m.lcFldName>> BETWEEN ?thisform.txtFrom.Value AND ?thisform.txtTo.Value
   ENDTEXT
   IF SQLEXEC(m.hndOps,m.lcSQL,m.lcCrsName) < 0
      *** Error handling here
   ENDIF 
ENDIF


NOT TESTED!!!!!

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
This is working. But the output is not what I want. When I selected anything in the combo box the output is same.
Simple I want this, If I select Completed GRN from combo box I need to select records from MSSQL table and pass my records into a cursor. There have 3 different tables to get records.
How can I do this?
 
Upvote 0 Downvote
There is a tool named DEBUGGER. Just step on this and check what happens.
What is the SELECT generated?


Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Okay.
In my select statement I need to get data from MSSQL and insert them into a cursor. As I sadid before there have 3 main functions in my combo and if I selected one I need to select records from MSSQL table and pass them into a cursor.
I will show an example.

MSSQL table1
Code: 
Batchno     PaymentID        Payvalue       Pdate
123         12               658            2021-05-07 00:00:00
324         87               54325          2021-01-07 00:00:00
456         23               2165           2020-05-08 00:00:00

MSSQL table2
Code: 
Batchno     AdvNo        Advance         Adate
123         15862        7546            2021-04-07 00:00:00
567         321          876415          2021-07-07 00:00:00
768         31654        3135            2021-03-07 00:00:00
432         356          7654            2021-06-07 00:00:00

Now , in my form combo box I need to select one. there have 3 such as ,
[ul]
[li]All GRN[/li]
[li]Completed GRN[/li]
[li]Pending GRN[/li]
[/ul]

Here If I select Completed GRN I need to get records from table1. And If I select Completed GRN I need to get records from table2. If I select All GRN I need to get records from all two tables. Here I need to select them using date.
 
Upvote 0 Downvote
Code: 
   DO CASE
       CASE thisform.cboReportType.Value = 'Pending GRN'
            lcFldName = "dAdvanceDate"
            lcCrsName = "_Pending"
           lcTblName = "Acp_AdvancePayment"

       CASE thisform.cboReportType.Value = 'Completed GRN'
            lcFldName = "dPaymentDate"
            lcCrsName = "_Completed"
            lcTblName = "Acp_FinalPayment"

       CASE thisform.cboReportType.Value = 'All'
            lcFldName = "dPaymentDate"
            lcCrsName = "_All"
            lcTblName = "Acp_Batch"
   ENDCASE
Here you can change the table name from where you want records

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
I did it as below. But this is not working because it didn't create any cursor.
Code: 
    DO CASE
       CASE thisform.cboBatchType.Value = 'Pending GRN'
            lcFldName = "dAdvanceDate"
            lcCrsName = "_Pending"
           lcTblName = "Acp_AdvancePayment"

       CASE thisform.cboBatchType.Value = 'Completed GRN'
            lcFldName = "dPaymentDate"
            lcCrsName = "_Completed"
            lcTblName = "Acp_FinalPayment"

       CASE thisform.cboBatchType.Value = 'All'
            lcFldName = "dPaymentDate"
            lcCrsName = "_All"
            lcTblName = "Acp_Batch"
   ENDCASE
   TEXT TO lcSQL NOSHOW TEXTMERGE
        SELECT cUpdatedBy
        ,cBatchNo
        ,nAdvanceNumber
        ,dAdvanceDate
        ,cPaymentNumber
        ,nPaymentValue
              
        FROM MAS.dbo.Acp_AdvancePayment
        where dAdvanceDate BETWEEN ?thisform.txtFrom.Value AND ?thisform.txtTo.Value
   ENDTEXT
 
Upvote 0 Downvote
That means no CASE is satisfied.
As I said, DEBUG and see how this code is executed.
Copy and paste lcSQL variable value and try to execute this statement in SSMS.
Just replace thisform.txtFrom.Value and thisform.txtTo.Value with real values when you execute the statement.


Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
The code is working for only one selected item.
Code: 
IF !EMPTY(thisform.cboBatchType.DisplayValue) then  
    lcFldName = "dPaymentDate"
    lcTblName = "Acp_FinalPayment"
    lcCrsName = "_Completed"
    DO CASE
       CASE thisform.cboBatchType.Value = 'Pending GRN'
            lcFldName = "dAdvanceDate"
            lcCrsName = "_Pending"
           lcTblName = "Acp_AdvancePayment"

       CASE thisform.cboBatchType.Value = 'Completed GRN'
            lcFldName = "dPaymentDate"
            lcCrsName = "_Completed"
            lcTblName = "Acp_FinalPayment"

       CASE thisform.cboBatchType.Value = 'All'
            lcFldName = "dPaymentDate"
            lcCrsName = "_All"
            lcTblName = "Acp_Batch"
   ENDCASE
   TEXT TO lcSQL NOSHOW TEXTMERGE
        SELECT cUpdatedBy
        ,cBatchNo
        ,nAdvanceNumber
        ,dAdvanceDate
        ,cPaymentNumber
        ,nPaymentValue
              
        FROM MAS.dbo.Acp_AdvancePayment
        where dAdvanceDate BETWEEN ?thisform.txtFrom.Value AND ?thisform.txtTo.Value
   ENDTEXT
   
      TEXT TO lcSQL NOSHOW TEXTMERGE
        SELECT cUpdatedBy
        ,cBatchNo
        ,dPaymentDate
        ,cPaymentNumber
        ,nPaymentValue
        ,cPaymentCurr
              
        FROM MAS.dbo.Acp_FinalPayment
        where dAdvanceDate BETWEEN ?thisform.txtFrom.Value AND ?thisform.txtTo.Value
   ENDTEXT
   
   IF SQLEXEC(m.hndOps,m.lcSQL,m.lcCrsName) < 0
      *** Error handling here
   ENDIF 
ENDIF

I can use Acp_AdvancePayment and get records frrom MSSQL . But , if I select Completed GRN I can't get records because they said dAdvanceDate in Acp_AdvancePayment is invalid. I need to know how to get records from different tables when selecting items in combo box.
I tried by debugging. But it doesn't shows any result.
 
Upvote 0 Downvote
Well, of course you need to know which columns exist in which tables. If you need an overview use MS SQL Server Management Studio to know what you can query. After you connect to the Database Engine you will find databases, their tables and their columns when drilling down the object explorer treeview:

new-query_hhxbaj.png


You could try to use DisplayValue instead of Value in the cases. Or even better, don't read the contrtol properties, just look into the cursor used as the RowSource. Because when you pick an item in the combobox this does not only change the Value (most often the value is the ID of a record, not what you see displayed) and DisplayValue (well, that IS what is displayed), the picked item also makes the record the current record of the RowSource cursor aka alias name of a workarea.

Chriss
 
Upvote 0 Downvote
Oh [sad] I can't understand how to do this.
The items in my combo box are not in the MSSQL table.
I just only need to get records from MSSQL table when display an item in my combo box like this, This is depending on the display value and the display value is not in the MSSQL table.
Code: 
if (thisform.cboBatchType.DisplayValue = 'all grn')
**select items from sql table***
endif

if (thisform.cboBatchType.DisplayValue = 'completed grn')
**select items from sql table***
endif

if (thisform.cboBatchType.DisplayValue = 'pending grn')
**select items from sql table***
endif

Anyone can please help me to do this?
Thank you
 
Upvote 0 Downvote
I completed it as below.
Code: 
IF !EMPTY(thisform.cboBatchType.DisplayValue) then

	IF thisform.cboBatchType.Value = 'Pending GRN'
		stra="SELECT  cUpdatedBy,cBatchNo,nAdvanceNumber,dAdvanceDate,cPaymentNumber,cPaymentCurr,nPaymentValue FROM MAS.dbo.Acp_AdvancePayment where  "
		stra=stra+" Acp_AdvancePayment.dAdvanceDate>= ?thisform.txtFrom.Value AND Acp_AdvancePayment.dAdvanceDate<= ?thisform.txtTo.Value"
		SQLEXEC(hndOps,stra,'_Pending')
	ENDIF
	
	IF thisform.cboBatchType.DisplayValue= 'Completed GRN'
		stra="SELECT  cBatchNo, nPaymentTypeID, cPaymentNumber, dPaymentDate FROM MAS.dbo.Acp_FinalPayment where  "
		stra=stra+" Acp_FinalPayment.dPaymentDate>= ?thisform.txtFrom.Value AND Acp_FinalPayment.dPaymentDate<= ?thisform.txtTo.Value "
		SQLEXEC(hndOps,stra,'_Completed')
	ENDIF
		
	
ENDIF

Thank you for the helps. [bigsmile]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Steve-vfp9user
  • Question
WhatsAPP and VFP9 SP2 2
Replies
4
Views
204
Paul Gibson
Paul Gibson
parkmingyu
  • Locked
  • Question
ComBo BOX Listindex ERR
Replies
1
Views
266
Chris Miller
Chris Miller
Scott24x7
  • Locked
  • Question
VFP9 and OpenAI 1
Replies
5
Views
892
Scott24x7
Scott24x7
mohinder singh
  • Locked
  • Question
IN VFP9 EXE FLICKER (BLINKING)
Replies
1
Views
288
Scott24x7
Scott24x7
ggreen61
  • Locked
  • Question
NAS for Shared Files Access via VFP9
Replies
3
Views
376
Drainie
Drainie

Part and Inventory Search

Sponsor

Back
Top