Hiya,
Thanks for this.... I was close, but I am modifying to try it out....
Ric
>Hi Ric,
>
>I am mainly an Oracle Guy and it would be easy to use Sequence in Oracle.
>Don't know much about MS SQL anyway, check for something like SEquence in MSSQL.
>Or Create a sequence like function returning a unique value:
>
>I am enclosing a function in VFP / Try the same concept in MSSQL.
>
>
>Function AutoGen
>Para forwhom
>If Used("Generator")
> Use In generator
>Endif
>Use generator In 0 Alias generator
>Select generator
>Set Order To Module
>Seek Allt(forwhom)
>if found()
> Repl generator.counter With generator.counter+1
> RetValue=generator.counter
> if used('Generator')
> Use In generator
> endif
> RetValue=Padl(Allt(Str(temp)),10,'0')
> Return RetValue
>else
> *Error Statement
>endif
>
>
>Issuing an update will be like
>
>ConnectHandle=SqlConnect("MSSQLDSNNAME","UN","PWD")
>SQLSETPROP(ConnectHandle, 'Transactions', 2)
>SQLSETPROP(ConnectHandle,"DispWarnings",.T.)
>
>*Assuming they are numeric in nature
>
>Sqlexec(ConnectHandle,"Update mySQLTable set lockkeycolumn = "+altrim(str(keycolumn))+" where custno = "+alltrim(str(mycustno)))
>
>Sqlrollback(ConnectHandle)/SqlCommit(ConnectHandle)
>Sqldisconnect(ConnectHandle)
>
>
>
>
>>Hi Everyone,
>>
>>What is the best or most appropriate way to control record or file locking when using SQL Pass Through on a remote view, to insert transactions into a MS SQL database from my application. There is a table in SQL that I need to push recrods into, but am not sure how I should control the locking requirements to ensure I have the right transaction numbers in a number
>>field. I might be adding 100 records, each needs the next transaction number, but how do I know someone else isn't doing the same from the main application if I can't lock the file termporarily?
>>
>>
>>I am thinking something like normal scripting - based on teh keycolum, but not sure if the content or syntax is right:
>>
>>
begin transaction
>>update mySQLTable
>>set lockkeycolumn = keycolumn
>>where custno = mycustno
>>.....
>>*-- do rollback or commit
>>
>>TIA
>>
>>Ric