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

Alphanumeric value not updating in SQL.

Hello Community,

 

I am trying to build an App which updates one alphanumeric value and integer value in SQL. 

When i am trying to update integer value is works fine, but the alphanumeric value is not updating. When i give integer like '2368' in alphanumeric value it gets updated, but when i give alphanumeric value like 'DR0977'  the value is storing as empty in SQL record.

 

cv2qm_0-1596476751926.png

 

 

Patch(
'[dbo].[Table]',
{
STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
AWARD: Value(TextInput1_3.Text),
AMOUNT: Value(TextInput1_2.Text)
}

 

Any suggestions. 

Thanks !

 

1 ACCEPTED SOLUTION

Accepted Solutions
cv2qm
Frequent Visitor

Patch(
    '[dbo].[FINAid_PowerApps_Dev]',
    {
        STUDENT_SYSTEM_ID: ComboBox1.Selected.STUDENT_SYSTEM_ID,
        AWARD: Text(TextInput1_3.Text),
        OFFER_AMOUNT: Value(TextInput1_2.Text)
    }
)

I resolved it with small fix.

I used Text for Alphanumeric value. 

View solution in original post

3 REPLIES 3
wyotim
Resident Rockstar
Resident Rockstar

Hi @cv2qm, I think the issue is that you are using the Value function on what you are trying to write to SQL. That function makes a text value into a numerical value, so any non-numerical values would return nothing (essentially an error state). If you try changing your Patch code to the following, it should work:

Patch(
    '[dbo].[Table]',
    {
        STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
        AWARD: TextInput1_3.Text,
        AMOUNT: Value(TextInput1_2.Text)
    }
) 

 

If that doesn't sort things out, feel free to @ me. I'm more than happy to follow up!

Mr-Dang-MSFT
Power Apps
Power Apps

Hi @cv2qm ,

It sounds like you want to write a value back to your SQL table and something is not working right for you. I pasted your formula here using the </> button in the toolbar:

 

 

Patch(
    '[dbo].[Table]',
    {
        STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
        AWARD: Value(TextInput1_3.Text),
        AMOUNT: Value(TextInput1_2.Text)
    }
) 

 

 

 

The patch function attempts to convert whatever is in the label into a number as best as it can. In the case of 2368, the conversion is easy because it's already a number. In the case of DR0977, the number is not picked out as well. 

 

You can test this out. Insert a label and set its text as below to see what would return:

 

 

Value("DR0977")

 

 

 

Instead, it may be most accurate to retrieve the digits by using the Match() function. It uses regex which is very powerful, and there are some out of the box patterns you can use without need to memorize any regex. Here's an example that would retrieve the digits:

 

 

 

Value(Match("DR0977",Match.MultipleDigits).FullMatch)

 

 

This means, "Look for a pattern of multiple digits in the string and return a record containing the full match, sub matches, and an integer representing where the match was found. But from that record, only return the fully matched string. Then convert that match, which is a string, to a value instead."

 

In context, this is how it might look:

 

 

Patch(
    '[dbo].[Table]',
    {
        STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
        AWARD: Value(Match(TextInput1_3.Text,Match.MultipleDigits).FullMatch),
        AMOUNT: Value(TextInput1_2.Text)
    }
) 

 

 

 

Let me know if this helps. 👍

cv2qm
Frequent Visitor

Patch(
    '[dbo].[FINAid_PowerApps_Dev]',
    {
        STUDENT_SYSTEM_ID: ComboBox1.Selected.STUDENT_SYSTEM_ID,
        AWARD: Text(TextInput1_3.Text),
        OFFER_AMOUNT: Value(TextInput1_2.Text)
    }
)

I resolved it with small fix.

I used Text for Alphanumeric value. 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,171)