cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to use Execute SQL action to send an int value

Jump to solution

I'm new to the nintex workflow game but I have a good amount of SQL experience. Currently I am trying to use a nintex workflow to create a row in a SQL table. Here is the statement I am trying to use.

Insert into Opportunities(OrgCompany, OrgGlobalPractice, OpportunityName, OpportunityLaborHours, OpportunityStart, OpportunityEnd)

values ('{WorkflowVariable:Co}', '{WorkflowVariable:GP}', '{ItemPropertySmiley Tongueroject_x0020_Name}', cast('{ItemPropertySmiley SurprisedpportunityHours}' AS int), '{ItemPropertySmiley Frustratedtart_x0020_Date}', '{ItemProperty:End_x0020_Date}')

I'm currently getting this error every time I try to run it using the run now feature of nintex designer.

The execution returned an unexpected error.

Conversion failed when converting the varchar value '{ItemPropertySmiley SurprisedpportunityHours}' to data type int.

Whether I use convert, cast, or just send the value I get the same error. The column is a calculated value but it's still a number that is retrieved. I've used the log in history list to see what value is output and it is a number. I'm sure I'm just missing something small but I've been working on this for several days now and have not been able to get anything to work.

Thank you for your help,

Daniel L Davis

0 Kudos
Reply
8 Replies
mmatsako
Nintex Newbie

Re: How to use Execute SQL action to send an int value

Jump to solution

Are you able to change the DB column to type varchar?   If so, then no conversion would be needed and most likely it'll update properly.  I see this whenever there is a type mismatch between sql and workflow.

0 Kudos
Reply
Not applicable

Re: How to use Execute SQL action to send an int value

Jump to solution

I am not unfortunately. Is it not possible to send an int with a nintex workflow? I have some ideas of work arounds if that is the case but wanted to be sure the was the answer.

Thanks,

Daniel

0 Kudos
Reply
dan_stoll
Nintex Newbie

Re: How to use Execute SQL action to send an int value

Jump to solution

Haven’t tested this, but does converting the value to an INT value using the convert action and then use the converted variable rather than the metadata work ?

Dan Stoll | Nintex<http://www.nintex.com/> – Workflow for Everyone | Technical Evangelist – APAC | dan.stoll@nintex.com<mailto:dan.stoll@nintex.com>

0 Kudos
Reply
Not applicable

Re: How to use Execute SQL action to send an int value

Jump to solution

I tried that as well. I assign the value to a workflow variable and it got the same error. Or do you mean to use a local sql variable?

0 Kudos
Reply
dan_stoll
Nintex Newbie

Re: How to use Execute SQL action to send an int value

Jump to solution

Can you post the DB structure ? I’ve never run into this, that I can remember and there shouldn’t be as far as I know any restrictions of posting INT values.

Dan Stoll | Nintex<http://www.nintex.com/> – Workflow for Everyone | Technical Evangelist – APAC | dan.stoll@nintex.com<mailto:dan.stoll@nintex.com>

0 Kudos
Reply
mmatsako
Nintex Newbie

Re: How to use Execute SQL action to send an int value

Jump to solution

I ran into this before when dealing with an decimal column in SQL - the trick I had to do was first turn my workflow variable type into type "Number" and then it inserted properly into the DB.  Can you try the same? So in other words, store your ItemProperty that is failing into a variable of type Number and then try to insert it. 

0 Kudos
Reply
mlauer
Nintex Newbie

Re: How to use Execute SQL action to send an int value

Jump to solution

Have you tried the inline round function:

Rounds a decimal value to the nearest integer.
fn-Round(number to round)

...,fn-Round({ItemPropertySmiley SurprisedpportunityHours}),...

0 Kudos
Reply
burkslm
Nintex Newbie

Re: How to use Execute SQL action to send an int value

Jump to solution

Can't you set the calculated value = to a workflow variable of the data type in the database? Then use the variable when executing the SQL statement.

0 Kudos
Reply