×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Get records using combo box in vfp9

Get records using combo box in vfp9

Get records using combo box in vfp9

(OP)
I have a combo box and there have 3 records such as,
  • All GRN
  • Completed GRN
  • Pending GRN
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

RE: Get records using combo box in vfp9

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

RE: Get records using combo box in vfp9

(OP)
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?

RE: Get records using combo box in vfp9

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

RE: Get records using combo box in vfp9

(OP)
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 ,
  • All GRN
  • Completed GRN
  • Pending GRN
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.

RE: Get records using combo box in vfp9

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

RE: Get records using combo box in vfp9

(OP)
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 

RE: Get records using combo box in vfp9

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

RE: Get records using combo box in vfp9

(OP)
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.

RE: Get records using combo box in vfp9

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:



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

RE: Get records using combo box in vfp9

(OP)
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

RE: Get records using combo box in vfp9

(OP)
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

RE: Get records using combo box in vfp9

Good job. Just use Display in line3, too.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close