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

>>>>Here is an interesting problem. I have a VFP database which contains a stored procedure which is called by insert and update triggers in some of the tables. The procedure records the date and name of the person making the table edits among other things into a separate history table. The name of the user is kept in a public variable so it is always available to the stored procedure. This all works fine in the application.
>>>>
>>>>Now we want to put the database on the web and allow edits via a web app. My problem is the updates fail because I don't know how to make the users name available from the web app to the stored procedure. I've thought about putting the current username into a separate table that the stored procedure could reference but this creates additional complications. So I'm wondering if there is a way to make a variable available to the stored procedure regardless if the database is being accessed from the VFP app or the web app. In the web app the current user is stored in a session variable. I'm using mostly ASP for the web app.
>>>>
>>>>Any thoughts?
>>>
>>>
>>>Could you modify the ADODB Open() process to set the VFP public variable to the user name. Something like:

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

It should then be visible to any SPs used on the same connection.
>>>HTH,
>>>Viv
>>
>>Thanks Viv. You may be on the right track but the line you suggested does not work. Here is where I put it.
>>

>>with oConnection
>>	 .ConnectionString = "Provider=VFPOLEDB;Data source=c:\inetpub\wwwroot\tip\login\data\tipdatabase.dbc"
>>	 .Mode = 16 'adModeShareDenyNone - this is value of adModeShareDenyNone constant
>>	 .Open
>>	 .CursorLocation = adUseClient
>>	 .Execute ("set null off")
>>	 .Execute("EXEC 'Public employee' + CHR(13)+CHR(10)+'employee = [Viv]'")
>>end with
>>
>>' HEADER EDITS
>>
>>' THEN DO THE ORIGINAL TIP TABLE   		 	   					   	 	
>>   sql = "select tip_id,contact,phone from tip where tip_id= " & lntip_ID
>>   .Open sql, oConnection, adOpenKeyset, adLockBatchOptimistic, adCmdText
>>   .fields("contact").value = trim(request.form("contact"))
>>   .fields("phone").value = trim(request.form("phone"))
>>   .updatebatch
>>   .close
>>end with
>>

>>
>>I also modified the stored procedure to exit if the variable "employee" was not defined.
>>

>>function StoreHistory(tcTriggerType)
>>if type("employee")<>"C"
>>	return
>>endif
>>etc . . . .
>>

>>This gives me a workaround but loses the benefit of the stored procedure. Not critical in this case but not ideal either. I'm not clear on how the command you suggested should work or if I put it in the bext place. Any further suggestions?
>>
>>Cetin - Note that I can pass a parameter when in the VFP environment but I'm not clear on how to do that in the ASP environment.
>>
>>- Don
>
>Don,
>Viv's suggestion works. I still prefer parameters but it works:)
>

>< %
>    cSQLSelect = "Select * from customer where country = m.mCountry"
>
>    set oConnection = Server.CreateObject( "adodb.connection" )
>    with oConnection
>    	.ConnectionString = "Provider=VFPOLEDB;" & _
>        "Data source=C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data\TestData.dbc"
>       .Errors.Clear	
>       .Open
>    end with
>
>  set oCmd = Server.Createobject("Adodb.command")
>  oConnection.Execute("EXEC 'public mCountry'+chr(13)+chr(10)+'mCountry=[USA]'")
>  with oCmd
>  .ActiveConnection = oConnection
>  .CommandText = cSQLSelect
>
>' Stored procedure with parameters
>'  .CommandText = "MyStoredProcedure"
>'  .CommandType = 4
>' stored procedure
>
>'  set p0 = .CreateParameter("employee", 129,1,,"MyEmployee")
>'  set p1 = .CreateParameter("fdate", 7, 1,,#2004/11/22#)
>'  set p2 = .CreateParameter("ldate", 7, 1,,#2004/11/23#)
>
>'  .Parameters.Append(p0)
>'  .Parameters.Append(p1)
>'  .Parameters.Append(p2)
>
>  set rs = .Execute()
>  end with
>
>    ' Check results
>    Response.Write("<TABLE border='1'><TR><TH>Cust ID</TH><TH>Company</TH><TH>Contact</TH><TH>Country</TH></TR>" )
>    while not rs.eof
>            Response.Write("<TR>")
>            Response.Write("<TD>" & rs.Fields("cust_id").value & "</TD>")
>            Response.Write("<TD>" & rs.Fields("company").value & "</TD>")
>            Response.Write("<TD>" & rs.Fields("contact").value & "</TD>")
>            Response.Write("<TD>" & rs.Fields("country").value & "</TD>")
>            Response.Write("</TR>")
>            rs.MoveNext
>    wend
>    Response.Write("</TABLE><BR/>")
>	
>    oConnection.Close
>    set oConnection = nothing
>% >
>

Cetin