SQL Non Query Activity 'Insert Into' only works using Tab button

Permanently deleted user

June 08, 2016 15:23

Good Morning!

 

  I have a workflow that presents a display form to the users to enter information, and inserts that information into a SQL table. The workflow works perfectly, except that one of the fields will only  be inserted into the table if I use the Tab button, instead of manually clicking into the next text box...

 

Below is a screen shot of my workflow...

 

 

This is the form that the user fills out...

 

 

And here are some results in our SQL table...

 

 

As you can see in Row 3 and Row 6, the 'name' field was not captured. These forms were filled out my manually clicking in each  box. For all of the other rows, the tab button was used to get from one text box to another. Any ideas why this is happening? And why only the 'name' field is affected like this?

 

Thanks!

 

 

0

• 

  Tom Neer

  June 08, 2016 15:47

  Lisa, how are you passing the dialog parameters to the SQL Execute? Can you post the Execute SQL command?

  0
• 

  Permanently deleted user

  June 08, 2016 15:50

  Hi Tom,

   

     The SQL command...

   

  INSERT INTO Lead.dbo.BLL_Inspection (inspDate, name, phone, BLL, details, Link) VALUES (@p_date, @p_name, @p_phone, @p_bll, @p_details, @id);

   

  and my parameters...

   

  

   

   

  0
• 

  Tom Neer

  June 08, 2016 15:56

  And in the DisplayForm, you assign the user overides to these variables?

  0
• 

  Permanently deleted user

  June 08, 2016 15:59

  I'm not sure what you mean by 'overides'...but here is my Display FOrm...

   

  

  0
• 

  Tom Neer

  June 08, 2016 17:49

  You will want to try the SQL below (for test purposes). You will probably need to quote appropriately, I would also recommend using code to prevent any SQL injection.  INSERT INTO Lead.dbo.BLL_Inspection (inspDate, name, phone, BLL, details, Link) VALUES (outDate, outName, outNumber, outBLL, outDetails, @id);

  0
• 

  Permanently deleted user

  June 08, 2016 18:18

  Hi Tom, Using your SQL query, I get the same results...the value in the 'name' field does not get inserted when I click in each text box when entering data in the form, but it does get inserted when I tab to each text box.

  0
• 

  Tom Neer

  June 08, 2016 21:04

  Lisa, thought I replied. When we get to this point, we usually pass the SQL command either into a log or into an Alert to see what is happening. Create a string variable "sqlQuery" and put in the SQL command. Then you can test it against your databases command line. My assumption is that you have not put quotes around the text fields and it is erroring out.

  0
• 

  Permanently deleted user

  June 08, 2016 21:39

  I'm sorry Tom, I still figuring out workflows so I dont know what you mean exactly. So when you say, pass the SQL command into an Alert, how exactly is that done? I understand when you say 'create a string variable'...but you lose me when you say 'put in the SQL command'. Put it where?

  0
• 

  Tom Neer

  June 08, 2016 22:05

  Don't be sorry. I am sending you down the wrong road. Had to crack open an workflow and double-check. We get around this a variety of ways. This is how an example using the UPDATE command in the SQLNonQuery. I used "?" instead of parameter names. <Activity mc:Ignorable="sap sap2010 sads" x:Class="ActivityBuilder" mva:VisualBasic.Settings="{x:Null}" xmlns="http://schemas.microsoft.com/netfx/2009/xaml/activities" xmlns:eac="clr-namespace:ESRI.ArcGIS.Client;assembly=ESRI.ArcGIS.Client" xmlns:eacg="clr-namespace:ESRI.ArcGIS.Client.Geometry;assembly=ESRI.ArcGIS.Client" xmlns:eact="clr-namespace:ESRI.ArcGIS.Client.Tasks;assembly=ESRI.ArcGIS.Client" xmlns:gce="clr-namespace:Geocortex.Core.Extensions;assembly=Geocortex.Core" xmlns:gfc="clr-namespace:Geocortex.Forms.Client;assembly=Geocortex.EssentialsWpfApi" xmlns:gfci="clr-namespace:Geocortex.Forms.Client.Items;assembly=Geocortex.EssentialsWpfApi" xmlns:gr="clr-namespace:Geocortex.Reporting;assembly=Geocortex.Reporting" xmlns:gwa="clr-namespace:Geocortex.Workflow.Activities;assembly=Geocortex.Workflow" xmlns:gwa1="clr-namespace:Geocortex.Workflow.Activities;assembly=Geocortex.Workflow.Activities" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mva="clr-namespace:Microsoft.VisualBasic.Activities;assembly=System.Activities" xmlns:s="clr-namespace:System;assembly=System.Core" xmlns:s1="clr-namespace:System;assembly=System" xmlns:s2="clr-namespace:System;assembly=mscorlib" xmlns:sa="clr-namespace:System.Activities;assembly=System.Activities" xmlns:sads="http://schemas.microsoft.com/netfx/2010/xaml/activities/debugger" xmlns:sap="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation" xmlns:sap2010="http://schemas.microsoft.com/netfx/2010/xaml/activities/presentation" xmlns:scg="clr-namespace:System.Collections.Generic;assembly=mscorlib" xmlns:sco="clr-namespace:System.Collections.ObjectModel;assembly=mscorlib" xmlns:sl="clr-namespace:System.Linq;assembly=System.Core" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> <TextExpression.NamespacesForImplementation> <sco:Collection x:TypeArguments="x:String"> <x:String>System.Activities.XamlIntegration</x:String> <x:String>System.Activities.Validation</x:String> <x:String>System.Activities</x:String> <x:String>System.Activities.Statements</x:String> <x:String>System.Activities.Expressions</x:String> <x:String>ESRI.ArcGIS.Client</x:String> <x:String>ESRI.ArcGIS.Client.Geometry</x:String> <x:String>ESRI.ArcGIS.Client.Tasks</x:String> <x:String>Geocortex.Core.Extensions</x:String> <x:String>Geocortex.Workflow.Activities</x:String> <x:String>Geocortex.Forms.Client</x:String> <x:String>Geocortex.Forms.Client.Items</x:String> <x:String>Geocortex.Reporting</x:String> <x:String>System</x:String> <x:String>Microsoft.VisualBasic.Activities</x:String> <x:String>System.Collections.Generic</x:String> <x:String>System.Linq</x:String> <x:String>System.Windows.Markup</x:String> </sco:Collection> </TextExpression.NamespacesForImplementation> <TextExpression.ReferencesForImplementation> <sco:Collection x:TypeArguments="AssemblyReference"> <AssemblyReference>System.Activities</AssemblyReference> <AssemblyReference>ESRI.ArcGIS.Client</AssemblyReference> <AssemblyReference>Geocortex.Core</AssemblyReference> <AssemblyReference>Geocortex.Workflow</AssemblyReference> <AssemblyReference>Geocortex.EssentialsWpfApi</AssemblyReference> <AssemblyReference>Geocortex.Reporting</AssemblyReference> <AssemblyReference>System.Core</AssemblyReference> <AssemblyReference>System</AssemblyReference> <AssemblyReference>mscorlib</AssemblyReference> <AssemblyReference>PresentationFramework</AssemblyReference> <AssemblyReference>WindowsBase</AssemblyReference> <AssemblyReference>PresentationCore</AssemblyReference> <AssemblyReference>System.Xaml</AssemblyReference> </sco:Collection> </TextExpression.ReferencesForImplementation> <Sequence mva:VisualBasic.Settings="Assembly references and imported namespaces serialized as XML namespaces"> <gwa1:SqlNonQuery CommandText="UPDATE jurisdictions SET email = ? WHERE objectid = ?" ConnectionString="Data Source=C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\Default\REST Elements\Sites\UDFCD_StormwaterDetentionFacilities\Data\cswdif.sqlite" ProviderName="Devart.Data.SQLite" RowsAffected="[rowsAffected]"> <gwa1:SqlNonQuery.Parameters> <InArgument x:TypeArguments="x:String" x:Key="email">[usrEmail]</InArgument> <InArgument x:TypeArguments="x:String" x:Key="objectid">[usrJurisdiction.ToString()]</InArgument> </gwa1:SqlNonQuery.Parameters> <sap2010:WorkflowViewState.IdRef>SqlNonQuery_1</sap2010:WorkflowViewState.IdRef> </gwa1:SqlNonQuery> <sap2010:WorkflowViewState.IdRef>Sequence_1</sap2010:WorkflowViewState.IdRef> <sads:DebugSymbol.Symbol>dzFDOlxVc2Vyc1x0bW5lZXIuU0VSVkVSMDVcRGVza3RvcFxTcWxOb25RdWVyeS54YW1sBUADSg4CAQFBBUcYAgECQb0CQc0CAgEFQz5DSAIBBERBRF0CAQM=</sads:DebugSymbol.Symbol> </Sequence> <sap2010:WorkflowViewState.IdRef>ActivityBuilder_1</sap2010:WorkflowViewState.IdRef> <sap2010:WorkflowViewState.ViewStateManager> <sap2010:ViewStateManager> <sap2010:ViewStateData Id="SqlNonQuery_1" sap:VirtualizedContainerService.HintSize="225,136"> <sap:WorkflowViewStateService.ViewState> <scg:Dictionary x:TypeArguments="x:String, x:Object"> <x:Boolean x:Key="IsExpanded">True</x:Boolean> </scg:Dictionary> </sap:WorkflowViewStateService.ViewState> </sap2010:ViewStateData> <sap2010:ViewStateData Id="Sequence_1" sap:VirtualizedContainerService.HintSize="247,260"> <sap:WorkflowViewStateService.ViewState> <scg:Dictionary x:TypeArguments="x:String, x:Object"> <x:Boolean x:Key="IsExpanded">True</x:Boolean> </scg:Dictionary> </sap:WorkflowViewStateService.ViewState> </sap2010:ViewStateData> <sap2010:ViewStateData Id="ActivityBuilder_1" sap:VirtualizedContainerService.HintSize="287,340" /> </sap2010:ViewStateManager> </sap2010:WorkflowViewState.ViewStateManager> </Activity>

   

   

   

  0
• 

  Tom Neer

  June 08, 2016 22:06

  You cannot input sql statements into SQLNonQuery. That is why LG released the SQLUnsafeQuery module in the latest version. Can't keep track.

  0
• 

  Permanently deleted user

  June 10, 2016 21:47

  Hi Tom, Thank you for the sample workflow. I guess I'm still stuck as to what is going on. I believe my SQL Command is correct (INSERT INTO Lead.dbo.BLL_Inspection ("inspDate", "name", "phone", "BLL", "details", Link) VALUES (@p_date, @p_name, @p_phone, @p_bll, @p_details, @id);) because everything gets inserted into the SQL table correctly when the tab key is used.. And when I move the Date Picker box to the bottom of the form, then both tabbing and manually clicking into each text box will insert data correctly. There is something with having the Date Picker at the top of the form where the next text box below isn't registering...

  0
• 

  Permanently deleted user

  June 10, 2016 22:56

  Hi Lisa,

   

  I just did some tests, and I think this is a bug.  I am able to reproduce this using the following steps:

   

  1) Add a date picker to a form

   

  2) Add a text box to the form

   

  3) Set the text box to output to an alert box

   

  If I press tab to get to the text box, my text shows up in the alert.  If I click, it does not.  However, if I explicitly open the date picker, and choose a date, then click into the text box, it works.

   

  I'm going to do a few more tests before filing the issue, and I will let you know if I find a workaround.  Thanks for bringing this to our attention (And to you too Tom for helping with the troubleshooting!)

   

  Cheers,

   

  Danny

  0
• 

  Permanently deleted user

  June 10, 2016 23:10

  The tracking number for this issue is GE-8147

  0
• 

  Permanently deleted user

  June 13, 2016 21:12

  Hi Lisa,

   

  I just finished a test with this, can you please try replacing your Text box in your display form with an Autocomplete box, and let me know if you're still having troubles?

   

  Thanks!

   

  Danny

  0
• 

  Permanently deleted user

  June 13, 2016 22:37

  That seemed to fix it Danny! You are a Super Hero! Now the data gets entered into the table no matter how I get to the box. Thank you so much!

  0
• 

  Permanently deleted user

  June 13, 2016 22:43

  No problem Lisa, glad it worked!

  0
• 

  Permanently deleted user

  August 17, 2016 19:34

  Quick update on GE-8147:  It's been reported fixed in Essentails 4.7 (In case anyone else stumbles on this thread)

  0

Please sign in to leave a comment.