SqlNonQuery: Value does not fall within the expected range

Sean McClurkan

May 05, 2022 20:45

VertiGIS Studio Workflow 5.26

I'm trying to do a simple INSERT statement in an Oracle database using the SQLNonQuery workflow activity.

Here is the SQLCommand in the activity:

insert into pnts_gis_trans  (gis_trans_id, notification_id, foldertype, folderrsn, propertyrsn, transaction_status, action_code, buffer, call_instance) values  (:gistransid, :notificationid, ':foldertype', :folderrsn, :propertyrsn, 'NEW', ':actioncode', :buffer, 'GISAPP')

This statement succeeds when run at the database itself with the corresponding values, but the SQLNonQuery errors on "Value does not fall within the expected range"

What value? What range?

Any help would be appreciated.

0

• 

  Berend Veldkamp

  June 08, 2022 11:26

  For one, you shouldn't need quotes around values if they are parameters to your query (foldertype and actioncode). Unless of course those values actually are :foldertype and :actioncode. I don't know if this causes the error though.

  0
• 

  Sean McClurkan

  June 08, 2022 16:37

  Thanks, Berend.

  Have you had any success executing a SELECT statement with an IN value in the WHERE clause? Particularly where the IN clause is a string of comma-delimited strings?

  0
• 

  Berend Veldkamp

  June 09, 2022 06:37

  Yes, for SQL Server I use this. I suppose other types of database will have functions similar to string_split

  select * from myTable where myField in (select value from string_split(@values, ','))

  0

Please sign in to leave a comment.