GUIDs in Excel import

[guitarhubby]

𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻

Excel file Import gives us the following SQL command:

select top 1 N0."AddressID",N0."DataQualityRate",N0."DataQuality",N0."CreatedOn",N0."ModifiedOn",N0."ModifiedBy",N0."ModifiedAddressBaseData",N0."SalutationLetterLink",N0."CRSeJHAdresseID",N0."CRSeJHOrgaID",N0."OLDOrganisation",N0."Initials",N0."Oid",N0."Name",N0."ZipPostal",N0."City",N0."Country",N0."POBox",N0."StateProvince",N0."ZipPOBox",N0."EMail",N0."Phone",N0."Fax",N0."Mobilephone",N0."NewsletterEMail",N0."AllowLetter",N0."AllowEMail",N0."AllowPhone",N0."AllowMobilePhone",N0."AllowFax",N0."AllowNewsletter",N0."Note",N0."XING",N0."Inactive",N0."SageSync",N0."Homepage",N0."SalutationLetter",N0."Longitude",N0."Latitude",N0."AddressType",N0."ZipCode",N0."DataPath",N0."Mark1",N0."Mark2",N0."PhoneFlat",N0."FaxFlat",N0."MobilephoneFlat",N0."LinkedIn",N0."StreetCode",N0."StreetName",N0."StreetNumber",N0."InactiveMarketing",N0."AddressPostalComplete",N0."NotDeletable",N0."MemberNumber",N0."MemberAdditionalNumber",N0."MemberTermination",N0."MemberLocalChapter",N0."MemberReginalChapter",N0."MemberFirstYear",N0."MemberAddressOrigin",N0."MemberAdvertising",N0."MemberZukz",N0."MemberLastSynchronisation",N0."AdditionToAddress",N0."Shortname",N0."DuplicateTo",N1."ObjectType",N0."OptimisticLockField",N0."GCRecord",N0."ObjectType",N0."Parent",N0."BusinessSector",N0."Organisationname",N0."ShortnameForPostal" from ("dbo"."Address" N0 left join "dbo"."Address" N1 on (N0."DuplicateTo" = N1."Oid")) where ((N0."ObjectType" = @p0) and N0."GCRecord" is null and (N0."Oid" = @p1))'' with parameters ''{59},{624c77a5-05b4-4e90-ae99-9ea6fc0fbe75}

As a result we get this exception:

exception ''System.InvalidCastException: Failed to convert parameter value from a String to a Guid.

Is there any solution to this issue? Do the GUIDs in Excel maybe require a certain format?

[apobekiaris]

I cannot tell whats going on but GUID conversion should be supported, deep inside the calling tree one can see

        static bool TryParseUGuid(string valueString, ref object result) {
            var tryParse = Guid.TryParse(valueString, out var guid);
            if (tryParse)
                result = guid;
            return tryParse;
        }

which is used to convert

[guitarhubby]

Hi, the problem seems to be the conversion to the sql statement. Any idea?

declare @p20 int set @p20=1 exec sp_executesql N'insert into "dbo"."LoggingMessage"("Oid","Name","EntryTime","Message","SystemUseranme","AssemblyVersion","AssemblyName","HostMachine","Username","UserID","UserToken","Object","ObjectFullName","ObjectID","Exception","StackTrace","LogLevel")values(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16) set @r=1',N'@p0 uniqueidentifier,@p1 nvarchar(4000),@p2 datetime2(7),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 nvarchar(4000),@p14 nvarchar(max) ,@p15 nvarchar(4000),@p16 int,@r int output',@p0='416E4336-8D2E-11EE-A268-3CE9F7AF1765',@p1=N'2023-11-27T15:07:11, Error, Administrator',@p2='2023-11-27 14:07:11.0813088',@p3=N'Executing Sql ''select top 1 N0."AddressID",N0."DataQualityRate",N0."DataQuality",N0."CreatedOn",N0."ModifiedOn",N0."ModifiedBy",N0."ModifiedAddressBaseData",N0."SalutationLetterLink",N0."CRSeJHAdresseID",N0."CRSeJHOrgaID",N0."OLDOrganisation",N0."Initials",N0."Oid",N0."Name",N0."ZipPostal",N0."City",N0."Country",N0."POBox",N0."StateProvince",N0."ZipPOBox",N0."EMail",N0."Phone",N0."Fax",N0."Mobilephone",N0."NewsletterEMail",N0."AllowLetter",N0."AllowEMail",N0."AllowPhone",N0."AllowMobilePhone",N0."AllowFax",N0."AllowNewsletter",N0."Note",N0."XING",N0."Inactive",N0."SageSync",N0."Homepage",N0."SalutationLetter",N0."Longitude",N0."Latitude",N0."AddressType",N0."ZipCode",N0."DataPath",N0."Mark1",N0."Mark2",N0."PhoneFlat",N0."FaxFlat",N0."MobilephoneFlat",N0."LinkedIn",N0."StreetCode",N0."StreetName",N0."StreetNumber",N0."InactiveMarketing",N0."AddressPostalComplete",N0."NotDeletable",N0."MemberNumber",N0."MemberAdditionalNumber",N0."MemberTermination",N0."MemberLocalChapter",N0."MemberReginalChapter",N0."MemberFirstYear",N0."MemberAddressOrigin",N0."MemberAdvertising",N0."MemberZukz",N0."MemberLastSynchronisation",N0."AdditionToAddress",N0."Shortname",N0."DuplicateTo",N1."ObjectType",N0."OptimisticLockField",N0."GCRecord",N0."ObjectType",N0."Parent",N0."BusinessSector",N0."Organisationname",N0."ShortnameForPostal" from ("dbo"."Address" N0 left join "dbo"."Address" N1 on (N0."DuplicateTo" = N1."Oid")) where ((N0."ObjectType" = @p0) and N0."GCRecord" is null and (N0."Oid" = @p1))'' with parameters ''{59},{a683ad0f-0420-4fd6-85d1-bd7a625270ca}'' exception ''System.InvalidCastException: Failed to convert parameter value from a String to a Guid. ---> System.InvalidCastException: Invalid cast from ''System.String'' to ''System.Guid''. at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider) at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at DevExpress.Xpo.DB.ConnectionProviderSql.<>c__DisplayClass147_0.<InternalGetData>b__0() at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler1 handler, MessageHandler1 createMessageHandler) at DevExpress.Xpo.DB.ConnectionProviderSql.SelectDataSimple(Query query, CriteriaOperatorCollection targets, Boolean includeMetadata)''',@p4=N'RDaenicke',@p5=N'',@p6=N'',@p7=N'IF-LAP17',@p8=N'Administrator',@p9='AEAA59C1-21E4-11E7-A60D-FBAF4318AA75',@p10='71FE7542-82BC-485A-A3DC-D38181F9F5D5',@p11=N'',@p12=N'',@p13=N'',@p14=N'Executing Sql ''select top 1 N0."AddressID",N0."DataQualityRate",N0."DataQuality",N0."CreatedOn",N0."ModifiedOn",N0."ModifiedBy",N0."ModifiedAddressBaseData",N0."SalutationLetterLink",N0."CRSeJHAdresseID",N0."CRSeJHOrgaID",N0."OLDOrganisation",N0."Initials",N0."Oid",N0."Name",N0."ZipPostal",N0."City",N0."Country",N0."POBox",N0."StateProvince",N0."ZipPOBox",N0."EMail",N0."Phone",N0."Fax",N0."Mobilephone",N0."NewsletterEMail",N0."AllowLetter",N0."AllowEMail",N0."AllowPhone",N0."AllowMobilePhone",N0."AllowFax",N0."AllowNewsletter",N0."Note",N0."XING",N0."Inactive",N0."SageSync",N0."Homepage",N0."SalutationLetter",N0."Longitude",N0."Latitude",N0."AddressType",N0."ZipCode",N0."DataPath",N0."Mark1",N0."Mark2",N0."PhoneFlat",N0."FaxFlat",N0."MobilephoneFlat",N0."LinkedIn",N0."StreetCode",N0."StreetName",N0."StreetNumber",N0."InactiveMarketing",N0."AddressPostalComplete",N0."NotDeletable",N0."MemberNumber",N0."MemberAdditionalNumber",N0."MemberTermination",N0."MemberLocalChapter",N0."MemberReginalChapter",N0."MemberFirstYear",N0."MemberAddressOrigin",N0."MemberAdvertising",N0."MemberZukz",N0."MemberLastSynchronisation",N0."AdditionToAddress",N0."Shortname",N0."DuplicateTo",N1."ObjectType",N0."OptimisticLockField",N0."GCRecord",N0."ObjectType",N0."Parent",N0."BusinessSector",N0."Organisationname",N0."ShortnameForPostal" from ("dbo"."Address" N0 left join "dbo"."Address" N1 on (N0."DuplicateTo" = N1."Oid")) where ((N0."ObjectType" = @p0) and N0."GCRecord" is null and (N0."Oid" = @p1))'' with parameters ''{59},{a683ad0f-0420-4fd6-85d1-bd7a625270ca}'' exception ''System.InvalidCastException: Failed to convert parameter value from a String to a Guid. ---> System.InvalidCastException: Invalid cast from ''System.String'' to ''System.Guid''. at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider) at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at DevExpress.Xpo.DB.ConnectionProviderSql.<>c__DisplayClass147_0.<InternalGetData>b__0() at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler1 handler, MessageHandler1 createMessageHandler) at DevExpress.Xpo.DB.ConnectionProviderSql.SelectDataSimple(Query query, CriteriaOperatorCollection targets, Boolean includeMetadata)''

System.InvalidCastException: Failed to convert parameter value from a String to a Guid. ---> System.InvalidCastException: Invalid cast from ''System.String'' to ''System.Guid''. at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider) at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at DevExpress.Xpo.DB.ConnectionProviderSql.<>c__DisplayClass147_0.<InternalGetData>b__0() at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler1 handler, MessageHandler1 createMessageHandler) at DevExpress.Xpo.DB.ConnectionProviderSql.SelectDataSimple(Query query, CriteriaOperatorCollection targets, Boolean includeMetadata)',@p15=N' at System.Environment.get_StackTrace() at iXISFramework.Module.Logging.LogEvent(Type obj, String objectId, String message, Exception ex, LogLevelEnum level, String assemblyName, String assemblyVersion) in C:\DevProjects\iXISnetJH\iXISFramework.Module\Class\Logging.cs:line 142 at iXISFramework.Module.Logging.LogEvent(Object obj, String message, Exception ex, LogLevelEnum level) in C:\DevProjects\iXISnetJH\iXISFramework.Module\Class\Logging.cs:line 117 at iXISFramework.Module.IFTracing.LogError(Exception exception) in C:\DevProjects\iXISnetJH\iXISFramework.Module\Class\Logging.cs:line 18 at Xpand.ExpressApp.ExcelImporter.Services.ImportExtension.Import(ExcelImport excelImport, DataTable dataTable, IObjectSpace failResultsObjectSpace, IObserver1 progress, ImportParameter[] importParameters) at Xpand.ExpressApp.ExcelImporter.Services.ImportExtension.Import(ExcelImport excelImport, IObjectSpace failResultsObjectSpace, Byte[] bytes, IObserver1 progress, ImportParameter[] importParameters) at Xpand.ExpressApp.ExcelImporter.Controllers.ExcelImportDetailViewController.<>c__DisplayClass14_0.<<ImportActionOnExecute>b__0>d.MoveNext() at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine) at Xpand.ExpressApp.ExcelImporter.Controllers.ExcelImportDetailViewController.<>c__DisplayClass14_0.<ImportActionOnExecute>b__0() at System.Reactive.Linq.QueryLanguage.<>c__1891.<ToAsync>b__189_1(ValueTuple2 state) in //Rx.NET/Source/src/System.Reactive/Linq/QueryLanguage.Async.cs:line 907 at System.Reactive.Concurrency.Scheduler.<>c__751.<ScheduleAction>b__75_0(IScheduler _, ValueTuple2 tuple) in /_/Rx.NET/Source/src/System.Reactive/Concurrency/Scheduler.Simple.cs:line 65 at System.Reactive.Concurrency.UserWorkItem1.Run() in /_/Rx.NET/Source/src/System.Reactive/Concurrency/UserWorkItem.cs:line 29 at System.Threading.QueueUserWorkItemCallback.<>c.<.cctor>b__6_0(QueueUserWorkItemCallback quwi) at System.Threading.ExecutionContext.RunForThreadPoolUnsafe[TState](ExecutionContext executionContext, Action1 callback, TState& state) at System.Threading.QueueUserWorkItemCallback.Execute() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading.PortableThreadPool.WorkerThread.WorkerThreadStart() at System.Threading.Thread.StartCallback()',@p16=2,@r=@p20 output select @p20

[apobekiaris]

this is the SQL statement which clearly states there was no conversion, as I said there should be and without a sample prpoject and data and detailed reproduction instruction, I cannot tell why this occurs. As an alternative you can create a new string Property and send the data there and overidde the OnSaving and do the manual conversion

[expand]

Closing issue for age. Feel free to reopen it at any time.

.Thank you for your contribution.