>>>>>>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 :-{
>
>Hi Viv-
>I've been exploring this a little further and was wondering if there is a way to extract a value from the new record. Something like:
>

>< %
>  execstr = "EXEC 'insert into list_index (name,desc) values ([" & trim(request.form("txtName")) & "],[" & trim(request.form("txtDesc")) & "])'"
>   oConn.Execute(execstr)
>   execstr = "EXEC 'select last filename from list_index into array a1' + chr(13) + chr(10) + 'RETURN a1'"
>myvar=   oConn.Execute(execstr)
>response.write(myvar.Fields(0).Value)
>% >
>

>
>This produces an error but illustrates what I am trying to do. Assume the filename is generated by a default formula value in the table definition.

Hi,
Top of my head - I think you could do this using a cursor:

execstr = "EXEC 'select last filename from list_index into cursor x' + chr(13) + chr(10) + 'RETURN SETRESULTSET(x)'"

Can't remember whether you were working with classic ASP or ASP.NET but the return value should be a RecordSet/Dataset.
OTOH I might be completely wrong - I'm relying on 'race-memory' for this.. maybe someone else will jump in. IAC check out the SETRESULTSET command.
HTH,
Viv