cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charlied_mc
Frequent Visitor

Upload image to OnPrem SQL Server

I'm trying to upload an image file to my on-premise SQL Server instance. Table schema is as follows:

 

CREATE TABLE [Attachment]
(
ID INT IDENTITY(1,1)
, ParentType NVARCHAR(20)
, ParentID INT
, Content VARBINARY
, CONSTRAINT PK_Attachment PRIMARY KEY (ID)
)

 

Using VARBINARY since the IMAGE data type will be deprecated. The tutorial I'm following is here:

 

https://powerapps.microsoft.com/en-us/blog/upload-files-from-powerapps-to-sql-server/

 

I think the "Add Picture" control has changed, because the ".Image" property is no longer available. I'm using ".Media" instead but still getting an error. Here's my OnSelect formula for the submit button:

 

Patch('[dbo].[Attachment]', Defaults('[dbo].[Attachment]'), {ParentType:"Label", ParentID:1, Content:AddMediaButton1.Media})'

 

Error Message:

 

The requested operation is invalid. Server Response: Microsoft SQL: String or binary data would be truncated.

 

What am I missing?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @charlied_mc ,

Could you please share a bit more about the error message within your app?

Based on the SQL syntax that you mentioned, I think there is something wrong with the data type you set (VARBINARY) for the Content column in your SQL table.

If you want to store image file within your SQL table, you must create Image type column, or varchar(max) type column or varbinary(max) type column to store the captured image data.

 

In addition, you could still use the UploadedImage1.Image formula to get captured picture from the Add Picture control.

Please check my response within the following thread for more details:

https://powerusers.microsoft.com/t5/General-Discussion/images-from-azure-sql/m-p/236994#M69602

 

I have made a test on my side, please take a try with the following workaround:1.JPG

PowerApps's configuration as below:3.JPG

Set the OnSelect property of the "Upload Pic" button to following formula:

Patch(
'[dbo].[Images]',
Defaults('[dbo].[Images]'),
{
ImageName: "Beauty,jpg",
ImageData: UploadedImage1.Image /* <-- ImageData column is a varbinary(max) type column */
}
)

2.JPG

Please take a try to re-add a varbinary(max) type column or varchar(max) type column in your SQL table to store the captured  image data. Or you could consider take a try to re-create a new SQL table on your side, then take a try with above solution I provided, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-xida-msft
Community Support
Community Support

Hi @charlied_mc ,

Could you please share a bit more about the error message within your app?

Based on the SQL syntax that you mentioned, I think there is something wrong with the data type you set (VARBINARY) for the Content column in your SQL table.

If you want to store image file within your SQL table, you must create Image type column, or varchar(max) type column or varbinary(max) type column to store the captured image data.

 

In addition, you could still use the UploadedImage1.Image formula to get captured picture from the Add Picture control.

Please check my response within the following thread for more details:

https://powerusers.microsoft.com/t5/General-Discussion/images-from-azure-sql/m-p/236994#M69602

 

I have made a test on my side, please take a try with the following workaround:1.JPG

PowerApps's configuration as below:3.JPG

Set the OnSelect property of the "Upload Pic" button to following formula:

Patch(
'[dbo].[Images]',
Defaults('[dbo].[Images]'),
{
ImageName: "Beauty,jpg",
ImageData: UploadedImage1.Image /* <-- ImageData column is a varbinary(max) type column */
}
)

2.JPG

Please take a try to re-add a varbinary(max) type column or varchar(max) type column in your SQL table to store the captured  image data. Or you could consider take a try to re-create a new SQL table on your side, then take a try with above solution I provided, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you Kris! Adding the (MAX) to the VARBINARY column allowed the upload to complete - we are looking good. Now to figure out how to display that image from the table back to PowerApps - any recommendations on that?

 

Thanks!

Charlie

Hi @charlied_mc ,

Do you want to display your image data from your SQL table within your PowerApps app?

Based on the needs that you mentioned, I think the Gallery control with Image layout could achieve your needs.

You could consider take a try to add a Gallery control within your app, set the Items property to your SQL Table data source ('[dbo].[Attachment]'). Then within the Gallery control, add a Image control, set the Image property of the Image control to following:

ThisItem.ImageField

On your side, it is:

ThisItem.Content

Please also check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/upload-files-from-powerapps-to-sql-server/

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I am attempting to utilize this solution on a Canvas app I'm developing and it is working fine, except that when using the app on the mobile platform, it is not sending over the full quality version of the photograph or at least what the phone is capable of.

 

When using the app in the development environment, it's obviously grabbing whatever image file I browse to upload and it is a duplicate copy bit for bit. So works there.

 

But on mobile devices, it's only uploading about a 250kb file when I'm sure that the phone would normally be generating at least a 1.5MB or more file. I can't figure out how to fix or change this.

 

The test Canvas app I created as optimized for Tablet just for testing, but I don't see why that would change this.

Nevermind, I'm stupid. I found that the iOS app has a default setting turned on to "Optimize images for upload" My bad.

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

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (41,462)