How to reset Auto increment field into 1 in Foxpro9

[Niki_S]

I have a table in sql server and I need to reset my field into 1.
sql table1:

nParaID     cParaNo      cParaYear     cParaCD
120         10           2021          XYZ

I need it to be like,

nParaID     cParaNo      cParaYear     cParaCD
120        [b] 1[/b]            2022          XYZ

I used this code,

stra="select   cFtyCD, cInvNo,dXFactory from MIS.dbo.vInvFinalAll WHERE  cInvNo=?thisform.txtInvoiceNo.VALUE"
SQLEXEC(hndOps,stra,'_XFactory')

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

SELECT _ParaYear
SELECT _XFactory

IF VAL(cParaYear) = YEAR(TTOD(dXFactory))
   SELECT dXFactory FROM _XFactory INTO CURSOR N_Year 
   
ELSE
**here I need to reset my cParaNo into 1****
   _cParaYear=ALLTRIM(STR(dXFactory))
   stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear where cParaCD=?thisform.cboFactory.value+'C'"
   SQLEXEC(hndOps,stra)
ENDIF

How can I do this?
Thank YOU

 

[Chris Miller]

Your update only sets cParaYear. Well, set cParaNo to 1 and cParaYear to what it was +1

And what does this have to do with VFP autoinc? You're querying a cursor from MSSQL, there will never be a VFP autoinc column in such a queried cursor.

Even if VFP would translate MSSQL integer identity fields to int autoinc, that wouldn't work as VFP has its own counter separate from MSSQL. Incrementing values always have to be generated at the database.


Chriss

 

[Niki_S]

What I need to do is,
Reset my cParaNo field into 1 using foxpro.
How can I do that?

 

[Chris Miller]

Well, you UPDATE it, just like any other field, don't you?

 stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear, cParaNo='1' where cParaCD=?thisform.cboFactory.value+'C'"

Btw, this doesn't update cParaYear to 2022, as far as I see the _cParYear is just the year you read, so you write back what's already stored.

Chriss

 

[Niki_S]

Thank You Chris

 

[Niki_S]

stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear, cParaNo='1' where cParaCD=?thisform.cboFactory.value+'C'"

I used above code, but cParaNo is not updating. Why is that?

 

[Chris Miller]

Either no record has cParaCD=?thisform.cboFactory.value+'C', you don't get into this else branch of your code, OR the sql causes an error.

I just see you use ?thisform.cboFactory.value+'C' both in the initial query into cursor _XFactory and then in your else branch, so when you get data in the first place, the update should address the same record(s) again.

Are you looking into _XFactory? Nothing changes there from the initial query, you change MIS.dbo.wshPara with your UPDATE-SQL, to refresh _XFactory you have to query and generate it again.

Also, your update might cause some error, syntax is okay, but there might be a type mismatch or insufficient privileges. All in all here's how to check SQL Server errors: thread184-1583706

I'd also write out stra into a command.log so you see what has been executed.

Chriss