>>>>>>Hi Cetin,
>>>>>>
>>>>>>I also would normally prefer using parameters but in this instance the value has to be available from within a trigger. So wouldn't it have to be declared public in the sproc anyway? My thinking was that, by doing it when the conneciton was opened, there would be virtually no disruption to existing code (i.e. no change in VFP and [hopefully] only in the one place in ASP)
>>>>>>Regards,
>>>>>>Viv
>>>>>
>>>>>I don't understand quite well why it has to be public. Triggers can have parameters.
>>>>
>>>>Hi,
>>>>I guess I didn't meand 'has to'. But, as above:
>>>>(a) Dons triggers were already using a public variable
>>>>(b) A parameter approach would mean modifying every SP and every call to it from ASP (and from the existing VFP app) to pass in the username parameter.
>>>>My approach involved just adding one line of code in ASP.
>>>>Or am I missing something?
>>>>Regards,
>>>>Viv
>>>
>>>Viv -
>>>I think your solution is clean and simple. I like it. The stored procedure is only called from the triggers so there really is no opportunity to "send" it a parameter other than embedding it into the trigger code (which I could do). But I would still need to somehow pass that parameter from ASP. Your idea gives me a little more knowledge that I didn't have before. Can you explain in simple terms what the EXEC in your code line does?
>>
>>Hi,
>>

oConn.Execute("EXEC 'Public UserName' + CHR(13)+CHR(10)+'UserName = [Viv]'")

just causes the OLEDB provider to execute the two lines of code i.e.:

>>PUBLIC UserName
>>UserName = [Viv]

Your code would just need to provide the required username instead of 'Viv'. Try this in VFP:

oConn=CREATEOBJECT("adodb.connection")
>>cs="Provider=vfpoledb;;Data Source='c:\program files\microsoft visual foxpro 8\data1.dbc'"
>>oConn.Open(cs)
>>cUserName = "Whatever"
>>Execstring  = "EXEC 'Public UserName' + CHR(13)+CHR(10)+'UserName = [" + cUserName + "]'"
>>? ExecString
>>oConn.Execute(ExecString)
>>l = oConn.Execute("EXEC 'RETURN UserName'")
>>? l.Fields(0).Value

I'm sure there are more elegant ways of constructing the 'ExecString' but my VFP is getting rusty :-{
>
>Did you mean ExecScript?:) Kidding but execscript() works for things that OleDb doesn't support and calling is like calling a stored procedure:
>
>CommandText = 'ExecScript'
>CommandType = 4
>Parameters if any (like the code itself) and Execute.

Seems like a golden opportunity to confuse everyone by using EXECSCRIPT(EXECSCRIPT()) :=}