[RESOLVED] Datatypes mismatch in SQL server and Infopath

swetha — Thu, 03 Jul 2008 17:18:46 GMT

Hello,

I found the problem to be with the field Service_count, which failed to convert the boolean values to a number. I retained the datatype for the 3 services in the SQL database as "bit" and service_count as "int" and in the form I have the 3 services inserted as " boolean" fields, and default values for the services to be "True/False".

I used the formula for service_count as : number(contains(service1, "true")) + number(contains(service2, "true")) + number(contains(service3, "true")) .,which works as I intended it to.

Cheers.

Datatypes mismatch in SQL server and Infopath

swetha — Wed, 02 Jul 2008 15:32:54 GMT

Hi,

I have a problem when trying to submit an Infopath form.The scenario is as follows:

I have an infopath form which talks to SQL server via a webservice to query and submit data.

I have SQL server 2005 database which has a table for services provided for a given studentID. Fileds and the data types of the table are : studentID(int) | service1(bit) |Service2(bit) | Service3(bit) | service_count (int).

The 3 services contain the value of 'True' or 'False' depending on whether they are checked or not in the infopath form .The service_count field gives the total count of services selected in each row.

The C# code for the insert looks like this:

this.sqlInsertCommand3.CommandText = "INSERT INTO [Services] ([studentID], [service1], [service2], [service3], [service" +
            "_count]) VALUES (@studentID, @service1, @service2, @service3, @service_count)";
        this.sqlInsertCommand3.Connection = this.sqlConnection1;
        this.sqlInsertCommand3.Parameters.AddRange(new System.Data.SqlClient.SqlParameter[] {
            new System.Data.SqlClient.SqlParameter("@studentID", System.Data.SqlDbType.Int, 0, "studentID"),
            new System.Data.SqlClient.SqlParameter("@service1", System.Data.SqlDbType.Bit, 0, "service1"),
            new System.Data.SqlClient.SqlParameter("@service2", System.Data.SqlDbType.Bit, 0, "service2"),
            new System.Data.SqlClient.SqlParameter("@service3", System.Data.SqlDbType.Bit, 0, "service3"),
            new System.Data.SqlClient.SqlParameter("@service_count", System.Data.SqlDbType.Int, 0, "service_count")});

Now when I create the infopath form and insert the nodes ,the service fields have the data type of "unsignedByte".

I add rule to the field service_count in the form as sum(service1|service2|service3)

Now when I submit the form I get the error as follows :

>Infopath cannot submit the form

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

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized as a valid Boolean.
   at System.Boolean.Parse(String value)
   at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
   --- End of inner exception stack trace ---
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at Service.SetDataSet(DataSet1 studentData) in c:\Users\t3mantri\Documents\Visual Studio 2005\WebSites\070108\App_Code\Service.cs:line 50
   --- End of inner exception stack trace ---

There must be some type mismatch in the datatypes i the sql server and infopath. however when I change the datatype as tinyint in SQL server and boolean in the infopath form ,I am able to submit.

How can I fix this problem. I want to retain the datatype for the service fields as bit in SQL server.

Thanks in advance.

General

[RESOLVED] Datatypes mismatch in SQL server and Infopath

Datatypes mismatch in SQL server and Infopath