>>>>>>>>Hi Viv,
>>>>>>>>
>>>>>>>>It doesn't ring a bell for me. I'll look this weekened anyway.
>>>>>>>
>>>>>>>I can't remember the exact scenario but I remember posting code that would set a public variable for use by VFPOLEDB.....
>>>>>>>Best,
>>>>>>>Viv
>>>>>>
>>>>>>Hi Viv,
>>>>>>
>>>>>>I guess you meant this one Re: Pass a web variable to a stored procedure Thread #1257541 Message #1258431
>>>>>
>>>>>Thanks Viv,
>>>>>and
>>>>>Thanks Naomi. Your memory and search ability is amazing. I truly do get embarrassed when an answer to a question turns up to be one of my previous threads. (This may actually be my 3rd go at this. In the prior case I learned how to solve the previous problem but not enough to carry it forward to my new situation. Let's review and see if it will work in the new environment. The assignment is to create an edit log that preserves date, user, old and new values, etc. The code to do this is in a stored procedure that executes via INSERT and UPDATE triggers. The reason it is by trigger is that the edits may happen any one of 3 ways, the VFP UI Browse, the VFP application, the web (.NET). So the SP looks like this

>>>>>func SaveHistory()
>>>>>lparam lcTriggerType
>>>>>** more code and  do the save etc
>>>>>INSERT INTO historylog (editdate,user,etc) values (datetime(),gcUserName,etc)
>>>>>

>>>>>and the triggers look like this:
>>>>>

>>>>>*Update Trigger
>>>>>SaveHistory("U")
>>>>>*Insert Trigger
>>>>>SaveHistory("I")
>>>>>

>>>>>This works fine from the VFP side. Now lets go to the NET side. We have an SqlDatasource using an OLEDB connection, and a Formview using the SqlDatasource. The user clicks the Formview into edit mode,makes the edits, and then clicks Save/Update which fires the SqlDataSource UpdateCommand. Assuming I can programmatically define the UpdateCommand, how can I embed the value of Session("User") so that the SP can see it?
>>>>>Note that the SP is not fired from NET. It is fired inside VFP by the trigger.
>>>>
>>>>Hi,
>>>>
>>>>I didn't remember that you were the one who'd asked the question before !
>>>>Anyway the message that Naomi referenced pretty much covers it:
>>>>Pass in the value you want to use for gcUserName when you are creating the connection. When the trigger fires the SP it should pick up the value as it normally would.
>>>>
>>>>Shout if you have problems....
>>>>Best,
>>>>Viv
>>>
>>>Naomi and Viv -
>>>Thanks
>>>The light is beginning to flicker on.
>>>If I understand right, what you have suggested is to find a way to execute these 2 commands
>>>

>>>public gcEmployee
>>>gcEmployee = 'myname'
>>>

>>>via the OLEDB connection sometime before the trigger is fired. The value of gcEmployee should then remain available to any subsequent activity as long as the connection remains open. Correct? If this is the case I can understand how the previous code suggestion would work. In my case however I don't have an oConn to work with explicitly. What I have is SQLDataSource1 which handles its connection internally. So I tried this approach
>>>

>>>Protected Sub FormView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles FormView1.DataBound
>>>    SqlDataSource1.UpdateCommand = "Public gcEmployee" + Chr(13) + Chr(10) + "gcEmployee = 'Viv'" + Chr(13) + Chr(10) +  SqlDataSource1.UpdateCommand
>>>End Sub
>>>

>>>
>>>But this didn't work. Perhaps the placement of the code is wrong. Any more insights?
>>
>>Hi,
>>
>>Sorry - missed this message. Don't use Update command. Here's an example that you should be able to adapt. VFP SPs:

PROCEDURE SetUserName
>>LPARAMETERS cUserName
>>PUBLIC UserName
>>UserName = cUserName
>>ENDPROC
>>
>>FUNCTION WhoIsCurrentUser
>>  RETURN UserName
>>ENDFUNC

>>
>>.NET test code:

       static void Main(string[] args)
>>        {
>>            //Set public variable when setting up the connection
>>            System.Data.OleDb.OleDbConnection myConn = ConnectionProvider.CreateConnectionForUser("Jim");
>>
>>            // Now see if the public variable is accessible by SP
>>            System.Data.OleDb.OleDbCommand test = new System.Data.OleDb.OleDbCommand();
>>            test.Connection = myConn;
>>            test.CommandText = "WhoIsCurrentUser()";
>>            string s = (string) test.ExecuteScalar();
>>        }
>>    }
>>
>>
>>    static class ConnectionProvider
>>    {
>>        static public System.Data.OleDb.OleDbConnection CreateConnectionForUser(string sUser)
>>        {
>>            string connection = "Provider=VFPOLEDB.1;Data Source=Data1.dbc";
>>            System.Data.OleDb.OleDbConnection oledb = new System.Data.OleDb.OleDbConnection(connection);
>>            oledb.Open();
>>
>>            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
>>            command.Connection = oledb;
>>            command.CommandText = "SetUserName('" + sUser + "')";
>>            command.ExecuteNonQuery();
>>             return oledb;
>>        }
>>    }

>>
>>HTH,
>>Viv
>
>Thanks Viv.
>But I confess I must still be missing something. I submit to 20 lashes. :)
>I can envision how this sample would work. But as explained in an earlier post, I don't have direct access to the connection. It is created via the SQLDatasource which is why I tried to bury it in the UPDATE command. Is there a way to create the connection for the SQLDatasource in separate code so it's connection can be used to execute the code as you suggest? OR if I create a connection in code as you suggest, will the public variable created with it be visible to the second connection used by the SQLDatasource? I would expect each connection to have a private dataenvironment so the one would not see a variable created by the other.
>Leaving me still puzzled.

Hi,
Apologies - I missed the whole bit about using SQLDatasource. AFAICS, SQLDataSource opens and closes the connecition implicitly for each command so there would be no way of retaining a public variable in VFP across calls. My only suggestion (if you retain the SQLDataSource) is to wrap your update command in a VFP SP and pass the username to that as an extra parameter. Simple example:

FUNCTION UpdateRecord
LPARAMETERS UserName,SomeValue
PUBLIC gUserName
gUserName = UserName
INSERT INTO Table1 (Test) VALUES (SomeValue)
ENDFUNC

Then is NET:

            string sUser = "Fred";
             SqlDataSource1.UpdateCommand = "UpdateRecord('"+sUser+"','Test')";
            SqlDataSource1.Update();

Obviously there are better ways of populating the parameters in .NET but.....
HTH,
Viv