Checking null values and not null values in foxpro9

[Niki_S]

I used select statement and got some records into cursor in foxpro. Here is my query,

stra="SELECT  cCm_Sgl_TaxInv,cInvNo FROM MIS.dbo.vInvFinalAll where cInvNo=?thisform.txtInvoiceNo.value"
SQLEXEC(hndOps,stra,'TaxInv')

And I need to check null values and not null values using IF Condition. I did my code as below and it is not working.

SELECT TaxInv
IF NOT ISNULL(cCm_Sgl_TaxInv)

	ELSE
		ISNULL(cCm_Sgl_TaxInv)
		thisform.cmdCreate.Enabled = .T. 
		MESSAGEBOX("The value is already inserted")
    endif

How can I do this?
Thank you

 

[Chris Miller]

I think you want

SELECT TaxInv
IF Reccount('TaxInv')=0 OR ISNULL(cCm_Sgl_TaxInv)
   thisform.cmdCreate.Enabled = .T.
ELSE && NOT NULL
    MESSAGEBOX("The value is already inserted")
ENDIF

Chriss

 

[Niki_S]

Thank You. It is working but that is not what I want.I will show my whole code here.

This is my Foxpro form.

This is my code inside create button,

stra="select  nParaID, cParaNo, cParaYear, cParaCD from MIS.dbo.wshPara  WHERE cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra,'_wshPara')

_UpdInv=ALLTRIM(STR(VAL(_wshPara.cParaNo)+1))

stra="update MIS.dbo.wshPara set cParaNo=?_UpdInv where cParaCD=?ALLTRIM(thisform.cboFactory.value)+'C'"
SQLEXEC(hndOps,stra)

_subtaxn='SUBCM'+PADL(ALLTRIM(_wshPara.cParaNo),4,'0')+'-'+ALLTRIM(_wshPara.cParaYear)

stra="update MIS.dbo.vInvFinalAll set cCm_Sgl_TaxInv=?_subtaxn where cInvno=?thisform.txtInvoiceNo.value"
SQLEXEC(hndOps,stra)

This is my code for generate report inside method in form.

PARAMETERS CINVNO

stra="SELECT cInvNo,cFtyCD,cStyle,cGMTName,cPoNo,nInvQty,nPricePcs,nLotFob,nExRate,nTaxRate,cfacDes, cAddress, cVatRegNo,cSVATNo FROM  MIS.dbo.vInvFinalAll WHERE cInvNo= ?thisform.txtInvoiceNo.value and cFtyCD=?thisform.cboFactory.value"

SQLEXEC(hndOps,stra,'SVAT_Inv')

_subtaxn='SUBCM'+PADL(ALLTRIM(_wshPara.cParaNo),4,'0')+'-'+ALLTRIM(_wshPara.cParaYear)

SELECT SVAT_Inv

If Reccount('SVAT_Inv')>0
Copy To C:\TEMPFILES\s_vatinv Type Fox2x
      	xreppath= 'C:\TEMPFILES'
      	creppath = xreppath+"\SUSPENDED_TAX INVOICE.RPT"

With Thisform.oleprint
            .windowShowSearchBtn= .T.
            .windowShowPrintSetupBtn = .T.
            .progressDialog = .T.
            .reportfilename=creppath
            
            .ParameterFields(0)="SubSvat;"+ _subtaxn +";true"
            
            .printReport()
      Endwith
Else
      
Endif

This is the code inside my preview button.

CINVNO=thisform.txtInvoiceNo.Value

thisform.invoivereport(CINVNO)

This is the code I asked that how to check null and not null values. I did this inside my valid event in txtInvoice field.

stra="SELECT  cCm_Sgl_TaxInv,cInvNo FROM MIS.dbo.vInvFinalAll where cInvNo=?thisform.txtInvoiceNo.value"
SQLEXEC(hndOps,stra,'TaxInv')
SELECT TaxInv
	IF Not ISNULL(cCm_Sgl_TaxInv)
 MESSAGEBOX("The value is already inserted")
        else 
           thisform.cmdCreate.Enabled = .F. 
	ENDIF

When I run my form I can't preview my report without create button. It says that alias _wshPara not found.
How can I fix this?

 

[Chris Miller]

Well, what's not to understand about this?

Only your cretae button makes the query that generates the _wshPara cursor.
Before that's done there is no cursor with that name. Not even an empty one.




Chriss

 

[Niki_S]

When I insert _wshPara inside the report method it generate another new report with new Invoice Number. I need to get the previous invoice number which is in sql server. It means if my sql server has cCm_Sgl_TaxInv=SUBCM0001-2020 I need to get it when I lock Create button.In here if I insert,

stra="select  nParaID, cParaNo, cParaYear, cParaCD from MIS.dbo.wshPara  WHERE cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra,'_wshPara')

this code inside my method the cCm_Sgl_TaxInv =SUBCM0002-2020, But i need it as SUBCM0001-2020. Do you have any idea of this?