Form does not submit to SQL Server DB

[Gellai_Tamas]

Hey,

I have the following issue:

I created a form in PowerApps. When trying to submit data, it says that the field "ProductID" is required - though I added it.

I'm suspicious that it is because I use a drop-down here and somehow I added the items wrongly. Although - based on the DB it should work well:

I'm applying exactly the same method for the "Country" field (see 1. image, top left corner of the form) and there it works.

Is it a bug maybe? Or am I missing some necessary setting here?

Thanks,

Tamás

[Meneghino]

Here is the error:

[Gellai_Tamas]

No success, @Meneghino.

I deleted that comma - error message didn't change.

I've added the only DB column that was missing (InsertDate) - nothing.

I changed the datepicker function - nothing.

This is what I use now:

Appreciate your support, it should really be easy and be done in minutes - instead, I'm struggling with it for 2-3 days..

[Meneghino · Accepted solution]

Hi @Gellai_Tamas, absolutely no problem, we will get it working.

I just noticed that you have date columns, these are currently problematic in PowerApps.

Here is a copy of another post of mine...

There SQL Server / Azure SQL DB connector currently has some bugs, including the following:
1) After a field contains a value, you cannot set the value back to null from PowerApps
2) There are intermittent issues writing numbers with more than 7 significant figures to the database
3) Date type columns cannot be filtered using > or < operators
4) Writing to date type columns is problematic
 
As a work-around for 3) and 4) I now store all date information as an integer of value yyyymmdd.  This allows me to filter using > and < operators as well as avoid all pitfalls with time zones.
 
I know that the PowerApps team is working to resolve all the above, and there should be a new connector out in July sometime.
 
Hope this helps.
 
PS I have many posts on all the above and more if you want to search the community

PPS Since this is a problem with the connector, I imagine it is the same whether you use a standard form or the individual controls

[Gellai_Tamas]

I had the gut feeling that it might be the date columns. Now I get another error message saying DateValue function has inappropriate arguments.

Alright, I give your suggestion a shot.

NB. weird thing that when I use an edit form and no dropdowns, data gets submitted. Problem started when I applied dropdowns and their values didn't record in my database - but dates were working well. Tricky situation...

Thanks for all your help, @Meneghino, highly appreciated!

[kbirstein2]

Hi,

I would really like to know whether it is POSSIBLE to write a date to an Azure SQL Server database. I've read several articles about how it is "problematic" and using Patch and using TEXT to convert the values to yyyymmdd but no complete example of how to write a date into a "datetime" field in Sql Server Azure.

Is it possible? Am I just wasting my time? If possible I would really appreciate someone giving a clear example of how to do this. I will post it if I find an answer.

[Meneghino]

Hi @kbirstein2

Why would you want to write a date into a datetime field?  If you have a date type of data then the Azure SQL DB colum's data type should also be date to avoid issues with time zones.

[kbirstein2]

You misunderstood me. I wanted to write data into any type of date field in SQL Server. I have now been playing with Azure SQL Server and PowerApps almost full time over the last week and now it is working to use either "Submit Form" or a PATCH command (using the standard Update value, i.e:

DateValue4.SelectedDate + Time(Value(HourValue4.Selected.Value), Value(MinuteValue4.Selected.Value), 0)

to write into Azure SQL Server table. However, it was not working at first so it seems like they've been working on the SQL Server connection over the last several weeks. I am finalizing a video on all of my findings tonight.

[Meneghino]

Understood @kbirstein2, you may want to have a look at this if you work a lot with Azure:

https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/

[kbirstein2]

I'm familiar with that list and don't use all of the things listed on it but I'm not sure what you  mean by 4. "Fields cannot be updated with null/blank values." In my testing I have a date that is not required, so it can be NULL, and I can save a new record with SubmitForm or PATCH and the record will save and the value recorded for that date field is NULL in the new record when I look in SQL Server Management Studio. Or, conversely, if I supply a date, it is saved in that field.

The one problem I have around required/not required (NULL) fields is that if I save a new record and do not supply a value for even one field that is a required field (NOT NULL),  PowerApps will look like it's saving the record (little dots go across) but nothing happens, the record is not saved. So, in other words,  in my PATCH command for the required field I'm giving the UPDATE value of the card, however, it's blank because I haven't supplied a value, but rather than giving a warning, PowerApps just pretends to save the record! 

So the workaround is to wrap the PATCH command or SubmitForm in an IF statement that tests for IsBlank() for all of the required fields.

[Meneghino]

Hi @kbirstein2

Regarding your first paragraph, you are correct in what you say.  However, the problem is that once you have supplied a date when creating or editing a record, there is no way to reset that date field to a NULL value for that record.  You can supply a different date, but not patch a null value.  This is true for any type of field, not just dates.  I hope the issue is now clearer.

Regarding your second issue, I had not come across that, interesting.