cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ryosuke_I
Level: Powered On

How to delete the record from Azure SQL?

Hello.

I can add the record in Azure SQL database via Power App. But I can't delete the record via Power App.

I'm using default sample app to use SQL Server.

 

My data base is...

mydatabase.PNG

 

 

 

 

 

 

 

 

 

 

 

Adding record code is...

SubmitForm(EditForm1)

After this code execution, I can find the new record on MS SQL Server Management Studio(SSMS).

 

delete record code is...

Remove('[dbo].[mydatabase]', BrowseGallery1.Selected)

 

If I execute this remove code, apps respond...

"Can't find specified record.

Server Respond: ID '1481814000000' does not exist."

And I can find this record on SSMS...

 

How can I remove the record in SQL database via app?

 

 

 

4 REPLIES 4
Ryosuke_I
Level: Powered On

Re: How to delete the record from Azure SQL?

Update:

2016-12-15T15:00:00.000 on SQL datetime data should be "3690889200000"

Because, datetime is started from 1900-01-01T00:00:00.000 and tick time is 1msec.

 

However, in my analyzing, on PowerApp, this start datetime maybe 1970-01-01T00:00:00.000

Then, 2016-12-15T15:00:00.000 on Power App datetime data is "1481814000000"

 

So, if PK is datetime on SQL database, Power App can't find record...

 

Can someone give advice to delete record from SQL database via Power App?

 

 

Meneghino
Level 10

Re: How to delete the record from Azure SQL?

Hi @Ryosuke_I

You can remove a record by using its ID (or any other field which is your primary ID) like this:

 

Remove('[dbo].[mydatabase]', {ID: BrowseGallery1.Selected.ID})

 

This way you do not worry about the other fields

 

If you only have your UpdateDateTime field, then you can transform it like this:

 

Remove('[dbo].[mydatabase]', {UpdateDateTime: BrowseGallery1.Selected.UpdateDateTime + X})

 

where X is the difference in datetime calculation

Ryosuke_I
Level: Powered On

Re: How to delete the record from Azure SQL?

Hi @Meneghino

Thank you very much for your advice.

However, I can't solve my issue...

 

I tried some pattern for test.

1. PK is datetime "UpdateTimeDate" on database

2. PK is nvarchar(50) "RoomName" on database

 

Record Del Code for case 1-a.

RemoveIf('[dbo].[mydatabase]', UpdateDateTime=datetimevalue("2016/12/31 00:00:00.000")); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

Record Del Code for case 1-b.

RemoveIf('[dbo].[mydatabase]', RoomName="test01"); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

 

Record Del Code for case 2-a.

RemoveIf('[dbo].[mydatabase]', UpdateDateTime=datetimevalue("2016/12/31 00:00:00.000")); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

Record Del Code for case 2-b.

RemoveIf('[dbo].[mydatabase]', RoomName="test01"); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

 

Result of case 1-a and -b.

Both NG...

Result of case 2-a and -b.

Both OK!

 

So, in my understanding, PowerApps has bug for "datetime" handling if Azure SQL database PK data type is "datetime".

Can you or MS PowerApps team check this issue?

 

Meneghino
Level 10

Re: How to delete the record from Azure SQL?

hi @Ryosuke_I

Yes, I have had many problems with date and datetime fields in PowerApps and the product team seems to be aware of it.  They are making regular improvements but nothing major so far.

I usually avoid them by using an integer field for dates like this yyyymmdd and then parsing to a date if and when I need it.

I have not needed the time portion but of course you can add that too.

It's a workaround but it works.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 418 members 5,256 guests
Recent signins:
Please welcome our newest community members: