Submitting to a Database (via Web Services) in InfoPath Forms Services

InfoPath 2007 - submiting to SQL Server via Web Services

VB-tech weblog — Tue, 06 Feb 2007 02:55:22 GMT

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

helloitsliam — Tue, 06 Feb 2007 23:01:54 GMT

Loving this post. Keep up the good work!! :-)

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

rgardner — Wed, 07 Feb 2007 00:45:48 GMT

Great post. Everything works great in the InfoPath client, but not from Forms Services. The form is full trust and is an administrative form. Both of the data connections are UDC files and I enabled cross-domain data access. The query works great. The submit is giving me errors. Any ideas? Here is what I see in the SharePoint logs:

Unhandled exception when rendering form on postback System.IndexOutOfRangeException: Index was outside the bounds of the array. at Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.HandleSubmitDataSetInput(XPathNavigator inputSubDOM, Boolean[] useDataSets, XmlDocument soapRequest, Solution solution, String name) at Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.PrepareSoapRequest(XPathNavigator inputSubDOM, Boolean[] inputUseDataset, Solution solution, String name, Boolean useSelf) at Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.ExecWebRequestSync(XPathNavigator inputSubDOM, Boolean[] inputUseDataset, XPathNavigator resultsSubDOM, Boolean resultUseDataset, XPathNavigator errorsSubDOM, Uri serviceUrl, Uri soapAction, Int...

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Sat, 10 Feb 2007 01:39:33 GMT

Thanks for your response! I'm glad to see someone's trying this out!

Anyway, as for the error you see on postback, I couldn't reproduce it with a simple test, so it looks like there's something about your DataSet that the Forms Server doesn't like. It's possible this has to do with the values you entered into the dataFields, but I'd have to see your source files to diagnose it and see if I could replicate it locally. On your side, I'd try tweaking the data types of the database column (and modifying the corresponding web service code) to find out if serialization is working correctly.

I'd love to investigate your failure. It would be great if you could send me:

1. A copy of your form template

2. Your web service code

3. The asmx file

4. The 2 sql scripts you used to create your DB table and the DML trigger

5. Version/SKU information about your Forms Services installation

6. A description of the data you entered into the form template prior to submitting

7. Any other environment-specific detail you think is relevant.

My (clumsily encoded) e-mail is “For[DELETEME – fighting spam]rest[dot]dilla[DELETEME – fighting spam]way [AT] MICROSOFT [DOT] COM”

Thanks!

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

JSeeders — Mon, 05 Mar 2007 18:26:28 GMT

I am having trouble getting VS 2005 to publish the site. I keep getting the error "js-forms-webservice:8080/" is not a valid virtual path. After initially setting the port number to 8080 that path should connect to the IPFSDiffGram directory under wwwroot. I assume VS connected sucessfully to it when creating the new project.

I did a little research and found a similar issue noting a VS bug prior to SP1 so I have now updated to SP1 but am still getting the error. Any ideas?

Thanks

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Tue, 13 Mar 2007 02:02:39 GMT

I'm not sure how to reproduce the error you're seeing, but could you elaborate on the steps you're following?

Following these steps, I don't see any failures:

1. Create the web site in IIS, with local path set to "C:\Inetpub\wwwroot\IPFSDiffGram" and port 4712.

2. Create a separate app pool for the web site.

3. Launch VS and create a new web site on local IIS under the site you just created.

4. Add the cs and asmx files.

5. Build the project.

At this point, I can access the web service via the URL: http://localhost:4712/DiffgramService.asmx.

Are you seeing different results in following those steps? "Publishing" the web site isn't strictly necessary when you're designing the web site on your local server.

Thanks,

Forrest

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Shuwi — Mon, 26 Mar 2007 14:07:18 GMT

Great post, great blog !

Finally some good / useful information about InfoPath!! :)

I'm running the sample (in exact the same configuration as the sample; same db names etc).

But I keep getting 'The exeption: The select command string must start with the 'SELECT' keyword!

I've added the selectCommandString to the exeption string so I can see what the commandString actualy is.. and that is: empty..

throw new Exception("The, " + selectCommandString + ", select

I've looked in the code where the selectCommandString is beeing filled.. but I can's seem to find it ...!

Thanks in advance!!

Shuwi

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

cterry — Wed, 25 Apr 2007 15:46:02 GMT

Great - I'm almost there, but getting the same error as rgardner i.e. Index out of bounds when submitting via a browser. Works fine in rich-client!

I have copied exactly what you had i.e. same db, table, username, password (although I had to change your code which had the columns called data1 etc - changed them to Column1 etc).

I'm guessing it's some configuration issue with SharePoint server. Any ideas?

Regards,

Colin

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Terry G Phillips — Thu, 03 May 2007 06:46:48 GMT

Thank you for the great info.

If the submit process is an SQL insert, is there a way to return the ID column from the insert procedure and place it in a field on the form?

TIA,

Terry

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Fri, 11 May 2007 23:45:02 GMT

After long absence...

Shuwi, it looks like you're using the "DBDataDynamic" WebMethod. This was my best attempt on short notice to provide a scalable WebMethod that could accept an arbitrary connection string and command, and then query and submit valid recordsets to the database table. However, for most purposes, it's a lot easier to just use the "DBData" WebMethod, which takes a static number of query parameters corresponding to fixed columns in your DB table. Try that one before you dive too deeply...

Cterry and Rgardner, we've achieved an internal repro environment for the IndexOutOfRangeException, so we'll take a look and see what we can find out. For now, I don't have any further info.

Terry G Phillips, you can get the updated ID by simply executing the query data connection, constraining the recordset by passing queryFields that will return the record you're looking for. Then you copy the ID value, using Rules or custom code in your form template, to move it to a target field.

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Fri, 11 May 2007 23:52:38 GMT

Oh, and Shuwi, the strings passed to the web service are populated with the values of the "dfs:queryFields" xml nodes in your form template. It's a good idea, when using the dynamic WebMethod, to set a default value for the queryFields to specify the connection string, command, etc.

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Sat, 12 May 2007 00:29:16 GMT

Okay, Cterry and Rgardner, I think we've narrowed down the issue on our side. Shuwi, this affects you as well.

SHORT ANSWER:

Map all submit parameters, not just the DataSet:

Revisit step 15 for designing your submit data connection: If your web service takes parameters to constrain the select command, map these parameters to the queryFields generated during the creation of the query connection.

LONG ANSWER:

In the screenshot of the submit data connection parameter mapping page in the data connection wizard, I only show the DataSet parameter for the web service. That screenshot is actually out of date relative to the source for the web service.

In the attached files, you'll see that the "UpdateDBData" WebMethod actually takes query parameters, in addition to the data being submitted. This is to allow the web service to restrict the result set, being queried and successively submitted, to a subset of the data in the table.

Now, practically, that means you'll actually see "Column1", ..., "Column5" in the data connection wizard. For browser-enabled form templates rendered in the browser, you must map the other (non-dataset) parameters to their corresponding queryFields. The rich client can handle it if you don't map the parameters, and empty strings are sent in place of the queryFields values. IPFS, on the other hand, requires that the mappings be defined.

Thanks,

Forrest

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

xjmmn — Mon, 28 May 2007 13:08:23 GMT

Great post. Everything works well!

spent a lot of time:)

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

xjmmn — Tue, 05 Jun 2007 12:15:42 GMT

I receive an error message when I change the data type with int.

error :"The form can not be submitted because it contains validation errors. Errors are marked with either a red asterisk required fields or a red dashed border(invalid values) "

Fiedld or group :tns supplier_id

error: only integers allowed.

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Tue, 03 Jul 2007 02:01:28 GMT

Excellent responses, everyone!

1. xjmmn, string fields in InfoPath do not, by default, require a non-empty value. When you change the data type to int, you'll need to guarantee that the fields contain an integer value. Otherwise, you'll see the data validation errors at submit time.

2. Bjoern Thomsen and Paresh also pointed out another corner case that needs to be addressed: After step 2.6, you need to set the default "Choice" for the DataSet to use the "Time" table. Follow these steps:

a. In the InfoPath Designer, click "Tools" >> "Default Values..."

b. Expand the nodes until you see the "DBTable" and "Time" group nodes under the repeating "(Choice)" node.

c. Select the "Time" radio button to include the timestamp in the form by default.

NOTE: After fixing your form template as above, you'll have to replace line 897 in "DiffGramService.cs" with the following code:

object maxPrimaryKeyObject = dbPrimaryKeyValuesReader.GetValue(0);

if (maxPrimaryKeyObject is System.DBNull)

{

// No primary key values exist in the table. This must mean that there are no records in

// the table. Therefore, we can safely assume this submit operation will create the first

// primary key in the table.

maxKey = 0;

}

else

{

maxKey = (int)maxPrimaryKeyObject;

}

Thanks again!

Forrest

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Kayday — Fri, 20 Jul 2007 08:28:53 GMT

Hi Forrest,

Can you give me some guidance on how to do this for multiple tables that have parent-child relationship please?? Also, what if my table does not contain LastTimeStamp, how would I check if the data were changed? Where should the modifications be? I'm not really good at C# .... :(

Thanks a lot for any help.

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

forrestd — Mon, 23 Jul 2007 20:01:04 GMT

Hi Kayday,

I don't have a code sample to offer, so I'll just throw out some ideas for logic you might follow to accommodate child tables. There is a lot of room for personal preference when determining how tables should interact, but I'll offer a very simple way.

For the sample code I provided, I assume that you've setup your table with a surrogate primary key. Now, assuming that you link your child table to your parent table, you would probably modify my merging code to do something like this (I'll treat the specific case of 1 parent, 1 child):

1. Query the latest DataSet from the parent table.

2. Loop through the parent DataSet using the logic provided in the code sample to flag inserts/updates/deletes.

3. In the loop body, after you've flagged the change for the current record in the parent's DataSet; query the child table, constraining the query to return only those records in the child table that match the parent record's surrogate key.

4. Loop through the resultant child table recordset and diff the child DataSet rows against those queried from the DB (using the same logic provided in the original code sample.)

So the pseudo-code would look something like this:

Query Parent table

Loop through parent table recordset to compare against submitted parent DataSet

Mark the current parent record as insert/update/delete

Query child table records that correspond to the current parent records surrogate primary key value.

Loop through child table recordset to compare against submitted child DataSet

Mark the current child record as insert/update/delete

End loop

End Loop

That pseudo-code lends itself to recursing through multiple levels of a parent-child hierarchy.

You'll need to decide whether records in the child table should be deleted whenever the corresponding record in the parent table is deleted. In other words, is the parent-child relationship a true foreign-key relationship, or is the relationship purely virtual, for the purpose of convenience?

Hope that helps!

Thanks, Forrest

walmart back to school supplies

walmart back to school supplies — Mon, 10 Sep 2007 19:02:16 GMT

walmart back to school supplies

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

alex350r — Tue, 11 Sep 2007 18:03:24 GMT

Great Post!

Im having a problem though:

Ive managed to configure the Web Service for my Database.

When i create the infopath form i can only put the 'data' controls from the dbdataset into a 'repeating' section.

The form i wish to design should just have one set of fields (non repeating) that submit to a database.

How can i change this?

Thanks, Alex

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

mkoslof — Thu, 20 Sep 2007 02:07:48 GMT

I'm sorry to cross post, and I mentioned this in another blog entry. I am using SQL Server Native XML Web Services to act as a secondary datasource to InfoPath. While this works great in the InfoPath rich client, it fails in InfoPath Forms Services and everything I have tried has led me no closer to a solution.

I am puzzled by this and I'm not sure why this is a problem, is this not just SOAP and standard wsdl at play here.

At this point, I am actually looking for a easy answer :). Meaning, does anyone know if this is even possible through InfoPath Form Services? Or is this a current limitation? I have done tons with Web Services before and I've never had an issue like this.

If the answer is, no, its not possible, are there any alternatives, besides writing my own web service to feed into InfoPath.

Please note at this point, just confirmation (either way, yes its possible, no its not supported) would be very helpful. Thanks again

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Matt Faus — Thu, 22 Nov 2007 06:54:51 GMT

Hey guys,

This is a cool example! Writing a web service to submit data to the SQL backend is a great idea, and that's why we created our Database Accelerator product. All you have to do is install it once, and then create a mapping from XML to SQL for each of the new form templates that you create. This is a much better model than creating a new custom web service with every new form template. We have used it on some very large projects, and it works great.

Take a look here: http://www.qdabra.com/proddetail.asp?prod=QDBXL1

If you have any questions, drop us a line in the Contact Us page! I hope this makes your developments easier!

Matt Faus

Qdabra Software

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Johnk — Fri, 18 Jul 2008 22:02:41 GMT

Ok, I know how far behind I am and how new I am to development based on the age of the blogs I use to solve my issues.

I have set up MOSS 2007 Enterprise on Windows Server 2003 with SQL 2005 Advanced as the Database for the whole shebang. All is good. I discovered Infopath and have written a form that works fine with the infopath client: on to submitting via web services through a browser enabled form ( no infopath client. ) In order to get the concept, I created a local database called test with your SQL scripts, populated a few rows and with Visual Studio 8 set up the web service, compiled it ( no changes ) and set off to design a test form.

I got to step 10 ( my url http://infincomd30/infopathwebservice/infopathwebservice.asmx?WSDL came up fine (I changed IPFSDiffGram to infopathwebservice throughout the webservice ) and gave me the 4 operation choices. When I pick the DBData operation and click next, I get the 5 parameters tns:data1 - 5, but when I poulate them I get the following SOAP error "unable to create a schema ...

The SOAP response indicates that an error occurred on the server:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)

at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at infopathwebservice.GetDBData(Object[] queryParameters, SqlDbType[] parameterTypes)

at infopathwebservice.DBData(String data1, String data2, String data3, String data4, String data5)

--- End of inner exception stack trace ---

My limited experience fails me on customizing the Instance Variables and the Webmethod query parameters ( which i did not do hoping that I was ok with supplied code just the test database ). I am familiar with connection strings etc.

Could you please give me an example of declaring and/or using the Instance variables and Webmethod query parameters? Thanks, John Koether

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Gandalf — Thu, 05 Feb 2009 20:29:02 GMT

Hy, great exemple !!

But I have a problem, i lauch my template InfoPath in my web browser (Internet Explorer) , the sumit data to my database SQL Server generata this message :

"L'index se trouve en dehors des limites du tableau.

System.IndexOutOfRangeException: L'index se trouve en dehors des limites du tableau.

à Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.HandleSubmitDataSetInput(XPathNavigator inputSubDOM, Boolean[] useDataSets, XmlDocument soapRequest, Solution solution, String name)

à Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.PrepareSoapRequest(XPathNavigator inputSubDOM, Boolean[] inputUseDataset, Solution solution, String name, Boolean useSelf)

à Microsoft.Office.InfoPath.Server.SolutionLifetime.WebServiceHelper.ExecWebRequestSync(XPathNavigator inputSubDOM, Boolean[] inputUseDataset, XPathNavigator resultsSubDOM, Boolean resultUseDataset, XPathNavigator errorsSubDOM, Uri serviceUrl, Uri soapAction, Int64 timeOutMillisec, Solution solution, Document document, String name, Boolean isQuery, DataAdapterTimer dataAdapterTimer, DataAdapterCredentials credentials, Boolean useDcl, Boolean useProxy, Boolean useSelf)

à Microsoft.Office.InfoPath.Server.SolutionLifetime.DataAdapterWebServiceSubmit.Execute(Document document, Uri soapAction, Uri serviceUrl, XPathNavigator querySubDOM, XPathNavigator resultsSubDOM, XPathNavigator errorsSubDOM, Int64 timeout, DataAdapterCredentials credentials, Boolean useDcl, Boolean useProxy, Boolean useSelf)

à Microsoft.Office.InfoPath.Server.DocumentLifetime.DataAdapterWebServiceSubmit.ExecuteInternal(XPathNavigator queryFields, XPathNavigator resultFields, XPathNavigator errors)

à Microsoft.Office.InfoPath.Server.DocumentLifetime.DataAdapterWebServiceSubmit.Execute()

à Microsoft.Office.InfoPath.Server.SolutionLifetime.RuleAction.EvaluateExpression(Document document, XPathNavigator currentTarget)

à Microsoft.Office.InfoPath.Server.SolutionLifetime.RulesRuleSet.<>c__DisplayClass7.<EvaluateExpression>b__4()

à Microsoft.Office.InfoPath.Server.SolutionLifetime.RulesRuleSet.EvaluateExpression(Document document, XPathNavigator targetNavigator)

à Microsoft.Office.InfoPath.Server.SolutionLifetime.ButtonFormCode.<>c__DisplayClass2.<Click>b__0()

à Microsoft.Office.InfoPath.Server.SolutionLifetime.ButtonFormCode.Click(Document document, XPathNavigator container)

à Microsoft.Office.InfoPath.Server.DocumentLifetime.EventClick.Play(Document document, BindingServices bindingServices, EventLogProcessor eventLogProcessor)

à Microsoft.Office.InfoPath.Server.DocumentLifetime.Event.PlayEvent(Document document, BindingServices bindingServices, EventLogProcessor eventLogProcessor)

à Microsoft.Office.InfoPath.Server.DocumentLifetime.EventLogProcessor.<>c__DisplayClass1.<ExecuteLog>b__0()

à Microsoft.Office.InfoPath.Server.DocumentLifetime.EventLogProcessor.ExecuteLog(Int32 expectedEventLogID)

à Microsoft.Office.InfoPath.Server.DocumentLifetime.Document.<>c__DisplayClass6.<PlayEventLog>b__3()

à Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)"

I hope you can help my...

Friendly,

Gandalf

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

infopath — Fri, 06 Feb 2009 01:43:38 GMT

Hi Gandalf,

A question: are there currently any records in your SQL Server table? If not - for testing purposes please add a record to the table, test again and let us know if that worked or you still received the error.

Thanks!

Scott

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Gandalf — Fri, 06 Feb 2009 12:23:55 GMT

Thanks Scott for your answer !

Yes i have 3 record in my sql table.

When i use the receive methode, the 3 records appears.

I have drop en recreate my table, but i have always the errors message.

(Just a another question, with this example, is it possible to write directly in my table ?)

Have a nice day,

Gandalf

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

infopath — Wed, 11 Feb 2009 18:38:08 GMT

Hi Gandalf,

Sorry for the delay...

By chance, have you walked through creating this process using the *exact* steps/samples/files, etc. in this blog post? Do these steps work for you?

In regard to your other question of writing directly to your table, I am not sure I understand the question.

Scott

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

fencehead2 — Wed, 25 Feb 2009 09:41:50 GMT

Hi Guys,

Great example.

I have followed through your instructions closely however when I reach step 6 InfoPath throws an error message "cannot use the web service because the method does not provide valid XML data". I am using the method UpdateDBData.

I have made all the appropriate changes in the .cs file and if I build the solution in VS2005 it does run so I don’t think it’s an issue with the code. Also the database is created and the DML trigger is working.

So everything seems to be in place for this example to work, just InfoPath seems to think it is not receiving valid XML.

It would be cool if you could shed some light on this for me.

Thanks,

Matt.

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Gandalf — Fri, 06 Mar 2009 17:51:23 GMT

Hy,

Infopath thanks for your anwser, but i have resolve this probleme.

But, i have another probleme, in the exemple it is just nvarchar in table type. And in my utilisation i have float and int in table type.

View my code with int :

[WebMethod(Description = "Retrieve a DataSet containing timestamped data from a hard-coded database and table.", MessageName = "DBData-Static")]

public DataSet DBData(string DESCRIPTION, Int32 CODE_CATEGORIE1, Int32 LIVRE1, Int32 PAPETERIE1, Int32 JEU1)

{

// Create an array to contain the parameters. These should include all the arguments for this WebMethod.

object[] parameters = new object[] { DESCRIPTION, CODE_CATEGORIE1, LIVRE1, PAPETERIE1, JEU1 };

// Create an array to indicate the SqlDbTypes of the columns for which the parameter values are specified.

// parameterTypes[i] is the SqlDbType of the database column whose parameter value is specified by parameters[i]

SqlDbType[] parameterTypes = new SqlDbType[] { SqlDbType.NVarChar, SqlDbType.Int, SqlDbType.Int, SqlDbType.Int, SqlDbType.Int };

// Get the DataSet from the database.

return GetDBData(parameters, parameterTypes);

}

View my error message :

La réponse SOAP indique une erreur :

System.Web.Services.Protocols.SoapException: Le serveur n'a pas pu lire la demande. ---> System.InvalidOperationException: Il existe une erreur dans le document XML (1, 445). ---> System.FormatException: Le format de la chaîne d'entrée est incorrect.

à System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

à System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)

à System.Xml.XmlConvert.ToInt32(String s)

à Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read3_DBDataStatic()

à Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer4.Deserialize(XmlSerializationReader reader)

à System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

--- Fin de la trace de la pile d'exception interne ---

à System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

à System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

à System.Web.Services.Protocols.SoapServerProtocol.ReadParameters()

--- Fin de la trace de la pile d'exception interne ---

à System.Web.Services.Protocols.SoapServerProtocol.ReadParameters()

à System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()

Je pense que c’est un problème de typage des données.

Le matching entre le type base de données et type .NET doit pas être correct.

I have see in this page the type .net to Sqldbtype

http://msdn.microsoft.com/fr-fr/library/system.data.sqldbtype(VS.80).aspx

I think it is a error in my type, are you any idea please ?

Have a nice day,

Gandalf

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

Rick315 — Fri, 24 Apr 2009 01:25:39 GMT

Hi,

I've tried this and I get to the point where I try to open the form in the InfoPath rich client. I get the following error when trying to do so:

The SOAP response indicates that an error occurred on the server:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.IndexOutOfRangeException: There is no row at position 0.

at System.Data.RBTree`1.GetNodeByIndex(Int32 userIndex)

at System.Data.RBTree`1.get_Item(Int32 index)

at System.Data.DataRowCollection.get_Item(Int32 index)

at DiffgramService.MergeAndUpdateDBData(DataSet updatedDBDataSet, Object[] queryParameters, SqlDbType[] parameterTypes) in c:\Inetpub\wwwroot\IPFSDiffGramRick\App_Code\DiffgramService.cs:line 788

at DiffgramService.UpdateDBData(DataSet updatedDBDataSet, String data1, String data2, String data3, String data4, String data5) in c:\Inetpub\wwwroot\IPFSDiffGramRick\App_Code\DiffgramService.cs:line 756

--- End of inner exception stack trace ---

Ideas?

Thanks,

Rick

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

infopath — Fri, 24 Apr 2009 15:43:59 GMT

Hi Rick,

Please accept our apologies as there is a slight error in the web service code. If you look in the "MergeAndUpdateDBData" function, you will see this line of code:

DateTime timeStampFromTimeTable = (DateTime)updatedDBDataSet.Tables[TimeTable].Rows[0][QueryTimeColumn];

When there are now rows in this table, this will generate the error you referenced. You need to comment that line of code and replace it with the following:

DateTime timeStampFromTimeTable = Convert.ToDateTime("1/1/1900 12:00:00 AM");

if (updatedDBDataSet.Tables[TimeTable].Rows.Count > 0)

{

timeStampFromTimeTable = (DateTime)updatedDBDataSet.Tables[TimeTable].Rows[0][QueryTimeColumn];

}

Scott

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

patrick1628 — Mon, 15 Jun 2009 22:53:23 GMT

Hi Guys,

I am a newbie doing this. How do I customize the web service for my database? Which form do I go to? What do I put?

Please help.. or if you have any tutorials, please help!

Thanks,

re: Submitting to a Database (via Web Services) in InfoPath Forms Services

patrick1628 — Tue, 16 Jun 2009 18:41:50 GMT

Hi Pls Help.

I got " Could not open web service" error when trying to configure infopath form.

it says not enugh space is available to process the command.