>>>>>>>>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 :-{
>>>
>>>Viv -
>>>I can get the following to work in VFP
>>>

>>>sql = "insert into users (name) values ([newname])" + chr(13)+chr(10) +"return user_id"
>>>x = execscript(sql)
>>>? x
>>>

>>>But I can't get it to work in ASP even after adding the EXEC at the front of the string.
>>>

>>>x = oConn.Execute("EXEC 'insert into users (name) values ([newname])' + chr(13)+chr(10) +'return user_id'")
>>>response.write(x)
>>>

>>What happens? Is there an error? Is the insertion made? What's user_id?
>
>Thanks Viv -
>user_id is an autoinc integer field in the table.
>The command refuses to execute and the error says "Unrecognized command verb"

Hi,
Try:

oConn.Execute("Insert into Users (Name) Value ([Fred])")
o= oConn.Execute("Select MAX(User_id) from Users")
? o.Fields(0).Value

But it could of course fail to return the correct User_id if another session snuck in another insertion between the two calls :={