InfoPath Dev

I want to build a simple holiday booking system using InfoPath and Share Point.

How do I make it so that when a user views the form which shows their holiday status, the form validates against their Share Point / AD login and makes it so that they can only see their own holiday and no - one elses?

All details of holiday are going to be held in an SQL database.

Hi Mark and welcome to the forum!
Cool question and definitely this is a common request. You'll need to authenticate the user. You can do this either a) in the form, b) in a web service on top of SQL, or c) in a stored procedure call in your SQL database. It's simpler to do it in a) but it's also not completely secure. Doing authentication in a web service is another way to do this, but requires some kind of mapping to the forms or logic to compare a value to what's in the form. If you are going directly to SQL, you can use Windows Authentication, and have SQL authenticate (compare user to a column in rowset). IF you use a web service on top of SQL, you'll likely have to do some form of impersonation or pass the username in as a parameter, The Recruiting Accelerator (on Microsoft website) does the latter and is a reasonable example of how to do this in general. Please go to msdn and search for it. Microsoft provides all code for the accelerator and this should give you an example of how to use InfoPath, Web service (impersonating) and SQL to authenticate user for forms access. Good luck!

Patrick Halstead [InfoPath MVP]
InfoPathDev

OK, so if I want to follow option a) and authenticate within the form itself, how do I do this?

Do I need to write some code behind in the scrpit editor or is there an easier way?

Security is less of a problem because this will initially run on a small office intranet and is not accessed by the outside world.

Hi Mark,
You need to write some code to do this. If you are using managed code then you need to get the loggedin user details from the Environment variables, System.Environment.UserName.
Use this userid to filter the details so that data belongs to that particular user.

One more important thing if you are using script you can try out ADService tool to get the logged in user details.
http://infopathdev.com/webstore/detail.aspx?itemid=18


Agni
InfoPathDev India

Do you have a form for each user? If so, you can use the System.Environment.UserName to determine the currently logged in user's name.

I hope this helps.

That returns the username but you'll still need some way to pull down the user's holiday info which requires authentication.

Patrick Halstead [InfoPath MVP]
InfoPathDev

I've found a load of useful information on the MSDN website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_landinfo03_tr.asp)

But there's something that I can't get to work and it's driving me crazy. I've set up a web service to send data to a database. This generates a schema (I think) which sets up the available fields in the Data Source (on the right of the InfoPath designer).

I can submit my data ok. But when I try to add a field and populate it with the userid from Active Directory, all I get is a blank field. If I do the same thing on a blank InfoPath form, without connecting to a web service, I can populate the field from AD without a hitch.

It's driving me mad - I'm guessing that the schema I create is stopping me from getting AD info, but I don't know what to do about it. Does anyone have any ideas?

Some questions: Are you using your Main Data Source for the DB connection? How are you populating the field from AD? Is it code or are you using an AD Web service (like the one we provide)?

Patrick Halstead [InfoPath MVP]
InfoPathDev

OK, replies in order:

1. Yes, I'm using the main data source for the database connection. I'm setting it up by enabling Submit on the form and then pointing the submit to the web service that I've written. This then puts the schema for the web service into the Data Source.

2. I'm populating the field from AD in the following way:
a) Creating a C# InfoPath project in Visual Studio 2003
b) Creating an OnLoad event from the Programming menu in InfoPath
c) Putting in the following code:
// store an XMLDOM node as a local variable
IXMLDOMNode nodeEmployee = thisXDocument.DOM.selectSingleNode("my:myFields/my:employee");

if(nodeEmployee != null)
{
if(nodeEmployee.text == "")
{
// if the employee name is blank when we load the form,
// populate the employee node with the current user name
nodeEmployee.text = System.Environment.UserName;
}
}

I also set up a text field in the Data Source called 'employee' for the username to write to.

3. I'm using a web service created in Microsoft Visual Studio (there's a pre-defined project type, C# web project) and connecting one table from a database to this.

Cheers, Mark.

Hi again,
Hmmm. This seems pretty simple. If you are setting up your form to submit from the main data source (#1) then the xpath in your code to populate from System.Environment.Username (#2) isn't correct. It will have to change to reflect the schema that was created for the main data source when you created your form to point to your Web service. This is a really simple thing to do, so it should be easy to figure out. Have patience...

Patrick Halstead [InfoPath MVP]
InfoPathDev

Yeah, I'll try to calm down.

I've been getting the username to work in projects where I haven't been adding the web service as the main data source.

So I guess that if I tried to add the web service submit as a secondary data source instead, that might work? Don't know until I try.

I did actually try what you suggested - modify the XPath to reflect the imported schema from the web service. The schema is as follows:

myFields
dataFields
tns:ReceiveDataSetResponse
ReceiveDataSetResult
ns1:DataSet1
(choice)
dooraccessrequest
fieldnames(x13)

So I edited the XPath but it fell over because it didn't like the extra ':'. For example, I wrote

IXMLDOMNode nodeEmployee = thisXDocument.DOM.selectSingleNode("my:myFields/my:dataFields/my:tns:ReceiveDataSetResponse/.../");

but the compiler didn't like the extra : after tns.

Appreciate the help.

Here's another example; if I design a new form and use an SQL database connection as the main connection, I get the following data source structure:

myFields
---> queryFields
---> some fields
---> dataFields
---> some fields

OK, so if I right click on myFields and add a text box (calling it employee) the program adds it to the dataFields group.

So now the Xpath for the System.Environment.Username onLoad event should be:

"my:myFields/my:dataFields/my:employee"

shouldn't it? Except that the text box (employee) that I want to populate with System.Environment.Username remains frustratingly empty. I'm following the example given in Lab 15 on the MSDN site and trying to modify it to take into account the data connection.

probably the namespace is dns or something so the my: won't work. try without the my for the previous one (i.e. just ns1:).

you shouldn't add another field to the data source created from the connection; just set a field that already exists in there and see if it works.

Patrick Halstead [InfoPath MVP]
InfoPathDev