InfoPath Dev

I am a little confused on how to solve this problem I have. I created an XSD schema from a sql server database using VS2005. The infopath form is based on this schema and we have many fields that are short data type (mostly lookup values in drop down lists). My issue is that infopath warns you that "only integers allowed" in these fields but many times we want to keep the field blank (null). How do we tell infopath that nulls are allowed?

thanks

Hi:

I think you'll need to modify your XSD so that the elements that you want to allow nulls on have the attribute xsi:nil = "true". While this post is not entirely to your issue, it does have some information on the xsi:nil attribute: http://kjellsj.blogspot.com/2005/04/submitting-xsiniltrue-values-from.html as does this one: http://www.sharepointblogs.com/dooke/archive/2007/08/14/the-xsi-nil-attribute-the-correct-way.aspx

Thanks for your reply. Wow! It seems like a pain to do this. The problem is that the infopath form has a couple of hundred fields that are integers but are not required in the sql server database.

 First thing is that it is strange that the elements do not have the nil attribute, even though in the Visual Studio schema project the property AllowDBNull is true for these fields. Looks like the saved xsd file does not look at this.

Second, the site you posted said that web services does not support nil and there are all these tricks to finally get the data correct in the database. That is too much work. You would think this is a common problem submitting integers that are not required in the database. Either I have to not have nulls allowed (which I don't like) or find another solution. How about the Qdabra software? Does this help in anyway?

Sorry for all the questions, but I am new to this.

thanks

Did you look at the xsd you created with Visual Studio to see if the xsi:nil attribute was on the elements that you'd like to be able to leave blank? I'm just curious if that is indeed what happened in the creation of the schema. Keep in mind, the blog post I referenced is from 2005, things may have changed since then. Is your intent to submit data from your form to a web service?

Yes, I looked at the XSD from VS 2005 and there are no nil attributes. The problem is that a contractor was assigned to do this project and he had to leave, so I had to complete it. He used VS 2005 to create the XSD from the sql server database. I do not even know if that is the best way. Is that what most people use when they need to create an xsd based on the data? The pain is that every time we add fields, we have to update the VS project, etc.

About your other question concerning web service - This is another issue about saving the data back to the sql database. Looks like the standard is to use web services but I will need to learn about this. I noticed on some sites that people use Biztalk to map the xml fields to the database, so I thought that might be an option. Also, I was wondering about sql integration services that can process xml files. I asked about the best way to do this and Clay Fox said web services is the easiest.

I am still concern about the fields that are not required. It seems strange that we have to go through so many loops to get this working. That is, web services do not support the nil. Does that mean when we use infopath, we have to make sure every field has a value to use web services?

I am so confused on this project.

thanks

It is always really hard to pick up and come to terms with a project someone else has left behind -- I understand your frustration. I guess I am kind of curious why the form wasn't just based on the db to begin with -- when you create a new InfoPath form, you have the option to base it off a db and then (depending on the types of fields in the db -- it won't allow submit under certain conditions) a lot of times your form is already all 'plumbed' to recieve and submit data from that database. Is there a particular reason (that you know of) that your form wasn't developed this way? I realize since you inherited it, you may not know the answer to that....

When I've developed forms based off the database, I've not had any troubles with nulls -- InfoPath will create the xsd correctly.

That is a good question. I wish I knew more about Infopath to answer why he did that. I tell you what we are doing and see if that helps. The form is going to be put on laptops and the users will enter the data outside the office. They won't have a direct connection to the sql server database that is located here in the office. They will enter data offline and then save the forms on the hard drive as xml files. Later when they go to the office, I will need to find the best way to import the xml form data into the sql server database. (That was my original question on the best practices and Fox said that the Qdabra software can do that - not sure if that is true or not.)  Also, the database will change many times with new fields added, so I have to update the XSD schema for the new fields.

So if I do not need to use an XSD file, then maybe I can change the database connection based on sql. I do not know if that is an easy process without losing all my work. Any thoughts on this?  

thanks

Qdabra's DBXL can handle your scenario. It would also mean you wouldn't have to redesign your form. I do not know of a way in InfoPath to take a form that has been based off an XSD and change it to using a database as its main data connection -- I think in order to do that, you would need to start over on your form.

May I make a suggestion? Perhaps you should try creating a new, extremely simple form based off your database, just one where you drop a couple of controls onto a single view. Then you could get a feel for the functionality that would provide you without dumping a lot of time into it.... When you open InfoPath and select Design a Form Template, you can select Database from the Based On window, and create a connection to your database. Maybe use a table that has some of the non-required fields that your Studio-generated XSD had some problems with... then you can try out some of your scenarios, such as being offline. I tend to find that creating a very small, simple form to test a piece of functionality lets me focus exclusively on the thing I need to figure out...

As far as the database changes, again DBXL can be a great way to go, since you'll be able map or remap your data, but if new db columns will require new fields on your form, you'll still need to modify your form on a regular basis to add new controls. If the form is based off the database, I think you simply need to modify the data connection and select the new columns when your db changes to add those to the data connection inside your form -- this, too, would be a great thing for you to try on the small test form I suggested above -- after you have a test form based off your db, add a column to the table your form is connected to and modify your data connection so that you can see the new field in your schema...

Oh, and for more information about offline capabilities in InfoPath, please see this link at the MS Office site: http://office.microsoft.com/en-us/infopath/HA102117391033.aspx

OK thanks for the info. I will look into Qdabra DBXL to see if that helps me.