Conversion failed when converting the nvarchar value to data type int

Conversion failed when converting the nvarchar value to data type int

  

I am having a continual problem getting an error when testing a webpage and clicking save.  For going on 3 days now, no matter what I have tried, I continually get a failure and error message "Conversion failed when converting the nvarchar value '1/1/1900 12:00:00 AM' to data type int."


I made some adjustments to my Entities, changing a couple attribute data types, making some attributes mandatory that were not previously.  So I thought this might have caused the problem.  Then I thought maybe it was my use of a TimeStamp for database updates (since the error seems to want to convert DateTime to an integer, and the only DateTime I'm using is for my TimeStamp), so I removed the mandatory field and just removed the TimeStamp assign in the SAVE Screen Action....still getting the error.


Today I created brand new entities, setting them up exactly right from the start, bypassing the old ones that I had changed, then in the Web Screens I adjusted my forms, variables, etc etc etc to use the new entities.....and I am still getting the same error message.


I am, unfortunately, very stuck here.  Thought the "brand new" entities might solve the problem, but they did not.  I do not want to have to start my app from scratch again.  I could do that, but it will take me ages, and I'm not convinced it will solve the problem.  Any additional help is much appreciated.  I do have the Error Detail Log, but I wouldn't like to post all here for privacy reasons.  The only potential odd thing is see is that in "Environment Information":  "Locale: en-US, DateFormat: yyyy-MM-dd, PID (some numbers and letters followed by) "Started='8/28/2017 4:21:17 AM'"   Is there something here with the date format being different from the PID started format?  


Could there be an issue with Service Studio causing this error?  I am using 10.0.704.0

Hi,

This is probably a SQL node in your action - have you tried debugging to see exactly where it fails?

The error means that the configured output entities / structures in the SQL node don't match the actual data returned from the underlying SQL statement. In this case, the app is trying to assign a text/datetime field to an integer attribute.

A few tips:

  • Don't use select * - use select {YourEntity}.* instead. Using the latter allows the compiler to generate the SQL statement in a way that selects all available attributes in the entity (select {entity}.[col1], {entity}.[col2], etc). This is important because the physical table doesn't necessarily match the entity definition.
  • Avoid the * operator if possible, as you'll probably return more data than needed. In most cases, it's better to create a structure, assign it to the return of the SQL and select the necessary fields - by the same exact order. Meaning, what you select (in the SQL statement) must match what is defined in the SQL's output.
  • Avoid using SQL alias. Use format {Entity}.{Attribute}. This way, Service Studio keeps track of what you're using and you benefit from TrueChange.

I strongly suggest you see the chapter on data queries from the Learn section as well.

As an addition to Paulo's excelent answer, I'd like to state that if at all possible, avoid SQL in favour of using an Aggregate. Aggregates are easier to create, easier to maintain, and easier to work with, and all the (valid!) points made by Paulo on SQL are non-issues when using aggregates.

Another addition is to provide us with some detailed oml where you encounter the issue.


Hello,

All information given here is valid and you should follow the advice, but this is bugging me:

"Conversion failed when converting the nvarchar value '1/1/1900 12:00:00 AM' to data type int."

Are you using SQL to save the data? 

If this is the case, you should check if your input parameters data types are correct, and if you are using them in the right place.

It seems to me you have a "date time" parameter that is defined to be of type text and that you are trying to store it in an integer field. This will happen if you are using an insert and are not providing the field names (only the values) and they are in an incorrect order or you have missing values.

Well...

Not sure if it's this, but it is what it looks like to me by the error and your description.

Of course, if before saving you are looking for info using sql and your output structure does not match the fields being retrieved you could have this problem, but then I don't think you would receive this specific error message... (unless you are storing date time as text in the entity...

Anyway, to save a single record to database, use the actions from the entities.

Cheers

Eduardo Jauch

Thank you all for the replies.  I will dig through some suggestions today and see where I get.


I will add quickly that I am not writing my own SQL here.  Just use a preparation with an aggregate, a form on the page populated with that aggregate, the user fills in the form, clicks save, and the save action assigns current date time to the TimeStamp record of that form/aggregate, and then runs a CreateOrUpdate.  I'm guessing something is happening during the create or update...


That's the odd thing to me, is that it all is done using the Service Studio built in stuff, nothing "custom" on my part.


I'll keep trying, thank you again, if I have to start from scratch on a few web screens or entities I think I can do that as well.  Or go through attribute by attribute and see when it fails.


The odd thing as well is I tried the debugger the other day and the entire thing did not fail.  Went right on to the next webpage just as the save action says it should.  But I will certainly give this another try.

OK...typing as I go along here so I don't forget anything and hopefully this can help someone in the future.


The issue is NOT in the save function like I thought, it is in the preparation of the next Web Screen.  I have a local variable I have set to data type TIME.  I use this variable with a combo box where the user selects the time.  There are a couple things here 1) this variable previously was type IDENTIFIER.  I changed it to TIME because rather than store the HoursIdentifier, I want to store the actual time of day in the database.  2) Oddly, when I look at this in the debugger, despite the variable being of type TIME (#00:00:00#), the debugger is showing DATETIME format (#1900-01-01 00:00:00#) for this variable.  Seems odd if I have set the local variable to data type TIME.


So, I suspect that I might be doing something wrong in the combo box when trying to pass an actual TIME to the local variable instead of an identifier like I was doing before.  Maybe something is getting messed up (I still find combo boxes a bit difficult to understand) because I'm doing it wrong.  Or there was something wrong when I changed the local variable to type TIME from IDENTIFIER, the system is trying to treat it as DATETIME for some reason.


Will follow up if I come up with a solution.

Hello Rob,

Date, Time and DateTime are all stored by the system as DateTime... :)

Rob K wrote:

OK...typing as I go along here so I don't forget anything and hopefully this can help someone in the future.


The issue is NOT in the save function like I thought, it is in the preparation of the next Web Screen.  I have a local variable I have set to data type TIME.  I use this variable with a combo box where the user selects the time.  There are a couple things here 1) this variable previously was type IDENTIFIER.  I changed it to TIME because rather than store the HoursIdentifier, I want to store the actual time of day in the database.  2) Oddly, when I look at this in the debugger, despite the variable being of type TIME (#00:00:00#), the debugger is showing DATETIME format (#1900-01-01 00:00:00#) for this variable.  Seems odd if I have set the local variable to data type TIME.


So, I suspect that I might be doing something wrong in the combo box when trying to pass an actual TIME to the local variable instead of an identifier like I was doing before.  Maybe something is getting messed up (I still find combo boxes a bit difficult to understand) because I'm doing it wrong.  Or there was something wrong when I changed the local variable to type TIME from IDENTIFIER, the system is trying to treat it as DATETIME for some reason.


Will follow up if I come up with a solution.

As Eduardo said, it is always date time in database for date,time and datetime. You need to debug and check the value that you are sending to update the entry for the combo box. As the error above clearly says, you are trying to insert datetime/text value in an attribute with type integer (may be because u made it identifier from service studio).


Just a question.

A combo box uses as source a list, usually from an aggregate or an sql, or special values. From the list, by default, it will get the IDentifier of the item selected by the user. 

How are you setting up your combo box?

Cheers,

Eduardo Jauch

Hi Rob,

A Combo Box has a Source Record List, which is the input list, a Source Entity, which is mutual exclusive with the Source Record List and can be used if you just need all records of an entity (typically a Static Entity), the Source Attribute, which is the Attribute of the Source Record List or the Source Entity that you want to display in the Combo Box, and the Source Identifier Attribute, which is the Attribute that contains the value that is to be stored in the Variable:

Now the problem is, and imho this is a bug (since it was introduced relatively recently, if my memory serves me right), that the Source Identifier Attribute is read-only if the Platform thinks it can infer it from the Source Record List or Source Entity. In case of a Source Record List, the Platform will use the Id of the Entity that supplies the Source Attribute, in case of the Source Entity, the Platform will use the Id of that Entity.

Now, in your case, I assume you have an Entity or a List containing an Id and a time, the time Attribute being the Source Attribute. You also want the time Attribute be what's put in the Variable, so it needs to be the Source Identifier Attribute as well, but probably you can't set it. There's two things you can do to get the time:

  1. Use an Aggregate to query the Entity using the Variable of type Id wherever you need it (e.g. a Save action);
  2. Create an Aggregate that queries the Entity with the time values, _and add a calculated column_This can be a dummy column, e.g. just containing 0. Use that Aggregate as Source Record List. This will somehow make Service Studio think it can't know what the Source identifier Attribute is, and allow you to specify it yourself. It's a bit of a hack workaround for what I think is a bug, but it works.


SOLVED: (Maybe):  I am not sure if I ever figured if this was my fault or the system.  But below was my workaround, which now works fine, but it is very "messy" I think.


As I said earlier, I used the combo box to display a dropdown list of hours for the user to choose from (01:00, 02:00, 03:00....up to 23:30).  The Entity that stores the UserID and some other relevant info, I want to store the hours they choose in the drop down as ACTUAL time in my database.  I do not want the HoursID used in the combo box to be what gets stored in my database.  I want the associated time of that ID.


I was trying to somehow force the combobox to feed the corresponding actual TIME to my database based on the time picked in the dropdown, but this never really worked, and seemed to be where the error was coming from.


What I ended up doing was saving every chosen comboxbox time Identifier in its own local variable (of data type IDENTIFIER).  Then in the save action, I performed aggregates for each of the identifiers.  In the aggregate I said to get the record (and the ACTUAL time) where the Aggregate identifier = Combobox Identifier variable.  This left me with 1 record.  I then created another local variable of type TIME, and did an assign to assign the actual time from the aggregate to the local variable of type time.  I then did an UPDATE on the database and put the actual time into my database.


It just seems so sloppy and inefficient to me.  Perhaps there is a better way, but this is how I have gotten it to work.  The other problem is that I do 2 times of day for each day of the week (so a total of 14 combo boxes)...and I can't do a FOR EACH with my solution since I have to do each aggregate separately based on the ID variables.  So I essentially repeat the process of assigning the ID to the variable, using that ID variable to do an aggregate, assign the results of that aggregate to a TIME variable, then update the database with the TIME variable 14 times in my save action.


My only other idea (besides trying to force the combobox to feed the time directly to a variable...which seemed to have caused my error in the first place) was trying to use a STRUCTURE.  Unfortunately I couldn't seem to make this work either, but I have extremely limited experience creating and using structures, so this may be my fault I couldn't make it work.  But my thinking was that if I could somehow put the combobox IDs into a structure, I could then do some sort of FOR EACH on that structure to get the associated actual time, save it in the structure, and use that structure to update the database.  But never got that to work and gave up.  Perhaps time for further structure training...



Rob,

Why didn't you used the pattern provided by Kilian? (In the comment previous to this one tou did now)

It is the solution to your problem and much less "messy"

Cheers

Eduardo Jauch