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. 

View solution in original post

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

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (29,237)