>>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