InfoPath Dev

My Second Post on this forum. I have InfoPath 2007 and an SQL 2005 server that I am using. I have added a table to my DB called Names which has to fields Firstname and Lastname. I have set up a Domain User Account to access this table in SQL as sysadmin and dbowner. I am using that same account to login to my XP workstation running Infopath. I Open InfoPath, design a form template, select database type, then click on select database button. Once this screen opens I selected New Source and then Microsoft SQL Server, specify my server IP Address for name,  leave it at Windows Auth - select the database and the specific table click next next next and it opens up the form for me in InfoPath. I am very Pleased!!

Now I go back to my SQL server and enter a name into the table - come back to my form and copy the query fields to query section and data fields to data section preview and run a query with firstname and it pulls the data no problem. However, I am not able to ADD any data back into the SQL table through the form. When I click the add record button, it says contents of the form will be deleted, does what it says be clearing the form and then nothing. I check the Table and no new record is added.

So someone said maybe you need to use an ODBC connection, not being too familiar I set up both a System DSN and a User DSN (with different names) and then back to InfoPath to design a form and I have the exact same results. I can pull data from the table, but I am unable to enter a new record from the form.

Can anyone tell me if I am doing something wrong or missing a step. It all "seems" relatively straight forward - I am using domain accounts that have local admin access in XP and also in SQL... just not sure what else to try and looking for some suggestions please.

Hi --

If you want to use the built in submit to SQL functionality, you took the correct initial steps (basing your form off a database).

I'm thinking you may have added controls bound to the wrong fields when you designed your form, however.

Here is a little tutorial you can walk through -- hopefully it will help you!

You should consider using a Web Service to get your data in and out of SQL. It's a better practice than a tight coupling. Lots of reasons why but I won't bore you with the details unless you're interested. :)

Hi Hilary, That site is perfect for what I have been doing up to now. What I did not realize is that you had to click file and submit - my issue is that submit is always greyed out .... After I put a name in firstname and lastname fields and goto file - submit is not available. Every step up to that point works very well and no errors and I can query a name that is in the table, I just do not seem to be able to submit one.

Patrick, I had considered a web service for its flexibility... but when I asked someone who had the skills to write one if it was a lot of work to do ... - he pointed me to a webpart and suggested I use sharepoint. I do not have the capability (yet!) to be able to write my own web service. I did do some searching but did not find anything I could download and maybe modify... so I am caught between a rock and a hard place!

Well, I'm not sure why your friend recommended a web part in place of a Web Service. Seems they don't have any clue about InfoPath solutions architecture ...

Anyway, you can download Qdabra's free trial. It's easy to install and set up and it's inexpensive and a best practice since it's the only "data-driven" Web service out there for XML.

Let us know if you have any questions or if we can help you. Connecting directly to SQL or a Web part poses many limitations...

Just to make sure that the information is in the thread (and, of course, I agree with Patrick that a web service such as DBXL is a much better idea than building a form off a database), if Submit is disabled when you walk through the main data connection wizard, it may because of the data types in your table, or your form compatibility. Fortunately, IP will give you at least some hint as to why submit is disabled. When you get to the last window for the main data connection wizard (the window where you name the data connection), at the bottom of the window will be a checkbox you can select to allow submit. If that checkbox is disabled, there will be text above it explaining why.