How to convert sql server string to numeric in foxpro9

[Niki_S]

I have a sql server table as below,

nParaID      cParaNo      cParaYear       cParaCD
218          1            2020            vtmc

And I gave my foxpro code like this,

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

Now I want to convert my cParaNo Into numeric using VAL FUNCTION.

How can I do this.

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

I tried this but it is not success.
How can I do this.

 

[Mike Lewis]

There is no VAL() function in T-SQL. Instead, you can use either CONVERT() or CAST():

SELECT nParaID, CONVERT(int, cParaNo) + 1, cParaYear, cParaCD .....etc.

[i]or[/i]

SELECT nParaID, CAST(cParaNo AS int) + 1, cParaYear, cParaCD .....etc.

This is assuming that cParaNo is an integer. If it is another data type (e.g. float, real), adjust as necessary.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

The names indicate data types in the name convention of VFP, so I guess this database was designed by VFP developers.

It's not natural the actual column data types are what the prefix suggests. Check the table definition in MSSQL wtools or by usage of VFPs SQLTABLES() function, which reads a list of tables of the connected database, for example. Much more details in the VFP help, of course.

Chriss