Datatypes mismatch in Sql server and Infopath

S

swethabilla

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
Click to expand...
The SOAP response indicates that an error occurred on the server:
Click to expand...

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.
swetha.
 
S

S.Y.M. Wong-A-Ton

Your boolean values in InfoPath might be set to "False/True" instead of
"0/1". And "False/True" is saved in InfoPath as the strings "false" and
"true", which might be causing your type mismatch.

Bit values in SQL Server are represented by 0 and 1. So try changing your
InfoPath boolean (checkbox) fields to "0/1" (Properties dialog box of the
field > Value when cleared || Value when checked") and see whether that helps.
---
S.Y.M. Wong-A-Ton


[email protected] said:
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
Click to expand...
The SOAP response indicates that an error occurred on the server:
Click to expand...

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.
swetha.
Click to expand...
 
S

swethabilla

HI Wong-A-Ton,

Thanks for the quick reply. I tried setting the feilds to "0/1" but it
dint work. Also the bit fields in SQL server 2005 are set to True /
False and not "0/1"

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Datatypes with InfoPath 2007 0
Help needed in saving infopath form to xml column 1
InfoPath 2003 and SQL Server 2005 1
Reporting and InfoPath 2010 1
Infopath-Webservices-SQL server 0
InfoPath Data Connection to Web Service 0
InfoPath 2007 & SQL Server 2005 1
Infopath and SQL Server 1

Top