Thank you for your prompt reply.
Unfortunately I haven't received any emails, please could you forward anything to devart AT antjones.fastmail.fm? Thanks. Also, could you let me know when the next build be available to us, either via the forum or that email address?
The scenario in which we encountered the problem is using a strongly typed dataset generated using the visual studio (2008) strongly typed dataset designer.
When generating update/delete statements using ConflictOption.CompareAllSearchableValues and when there are columns that allow NULL values, System.Data.Common.DbCommandBuilder generates update/delete statements of the form:
UPDATE TableName SET ... WHERE ... ((@IsNull_Column = 1 AND Column IS NULL) OR (Column = @Original_Column))
The IsNull_Column parameters are mapped to the underlying Column via the SourceColumn property, but with SourceColumnNullMapping set to true.
Logic in System.Data.Common.DbDataAdapter.ParameterInput() checks the value of SourceColumnNullMapping, and if it is set to true it sets the value of the parameter to 1 or 0 rather than the original column value as would happen otherwise.
However, since the SourceColumnNullMapping has had it's value reset, the @IsNull_Column paramter has it's Value property set to the original column value (a string in our case). This leads to an System.FormatException:
Code: Select all
Devart.Data.Universal.UniException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Int32.Parse(String s, NumberStyles style, IFormatProvider provider)
at System.Convert.ToInt32(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToInt32(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.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
at System.Data.SqlClient.SqlParameter.GetCoercedValue()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Devart.Data.Universal.UniCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3, Boolean A_4)
--- 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(DataSet dataSet, String srcTable)
at
...
To demonstrate this, here are the rough steps I followed:
- Create a SQL server database with a table without any RowVersion / TimeStamp columns (so that the command builder uses CompareAllSearchableValues for concurrency checking), and that contains a NVARCHAR(n) NULL column.
- Connect the the DB in VS Server Explorer using the UniConnection provider
- Using the VS dataset designer, drag and drop the table to create the typed dataset and DataAdapter.
- This should lead to a generated update query that uses a parameter of the form IsNull_XXX and that has SourceColumnNullMapping set to true in the designer code-behind.
- Using the generated dataset and adapter:
- insert a row with the nvarchar column set to a non-null value and call adapter.Update() to commit the inserted row
- make a change to the row, call adapter.Update() again. This will suceed since at the point the SourceColumnNullMapping is read the underlying SqlParameter has not yet being generated.
- Update the row once more and call adapter.Update() for a third time. This time, SourceColumnNullMapping will return false, and @IsNull_XXX will get set to a string value (the original value of the column) rather than 0 or 1. The exception should then be thrown.