cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ryosuke_I
New Member

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
New Member

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
Community Champion
Community Champion

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

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
Community Champion
Community Champion

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (24,784)