Dropdown text to SQL

[cymek11]

Hello,

I have SQL table with couple fields, they are char(10) (I tried different data type as well: ntext,nvarchar(50))

In my app, I have a form that write/update the record in SQL.

Items for each dropdown within the form :

["N/A","1","2","3"]

The fields are used for scoring N/A used if not scored at this time, and of course 1,2,3 when there is score to be applied.

Here is my issue. When entering new record to SQL it adds N/A as the entry if that's what's chosen (also a default option). Once the field gets updated by the form to one of the numbers 1,2 or 3 it also changes the field in SQL. But when I want to change back to N/A, it does not, it just goes back to previous value.

I tried changing the "Update" value on the form item to:

Text(DataCardValue4.Selected.Value,"[$-en-US]")

I tried changing different SQL data type.

Thank you.

[v-yuxima-msft]

Hi @cymek11 ,

Do you want to write dropdown’s text to your sql?

Do you set one Primary key in your table? I think if you use sql table Primary key is needed,please check whether there is one in your sql table if not please add one and then test it.

I test on my side as next, and the test is ok, not reproduce your issue, please try the following workaround to test.

DataSource:

App Test:

Result test:

Hope this can be helpful.

Best Regards.

Yumia

[cymek11]

I have primary ID in my SQL table. It's set with automatic increment each time a record is added.

I can see you can write 'N/A" in the field. I get the same results when writing the value to SQL for the first time. The issue exist when you pull-up an existing record, and you try to change the value from 1,2 or 3 back to 'N/A', and you submit the Form to update the record. No problem when changing from 'N/A' to a number. It only doesn't work when switching back to N/A.

In your example are you using a Form to update the record in SQL?

I hope that clarifies.

Thanks.

[emh]

Hello,

If I understand correctly, the UPDATE property of the datacard should reference the dropdown. It looks like your UPDATE property is referencing the datacardvalue.

[emh]

---

@cymek11 wrote:

I have primary ID in my SQL table. It's set with automatic increment each time a record is added.

I can see you can write 'N/A" in the field. I get the same results when writing the value to SQL for the first time. The issue exist when you pull-up an existing record, and you try to change the value from 1,2 or 3 back to 'N/A', and you submit the Form to update the record. No problem when changing from 'N/A' to a number. It only doesn't work when switching back to N/A.

In your example are you using a Form to update the record in SQL?

I hope that clarifies.

Thanks.

---

[cymek11]

---

@emh wrote:

Hello,

If I understand correctly, the UPDATE property of the datacard should reference the dropdown. It looks like your UPDATE property is referencing the datacardvalue.

---

The DataCard UPDATE is set to: DataCardValue3 (DataCardValue3.Selected.Value),  which is the dropdown within the DataCard with ["N/A","1","2","3"] as items.

Thanks

[v-yuxima-msft]

Hi @cymek11 ,

Yes,you are right. I used the Eidt Form to test.

Best Regards.

Yumia

[cymek11]

---

@v-yuxima-msft wrote:

Hi @cymek11 ,

Yes,you are right. I used the Eidt Form to test.

Best Regards.

Yumia

---

Ok, great.

So now on your form pull-up the record ID=2 and set the Test1 to N/A and update the record with SubmitForm(Form1).

Did SQL side changed to N/A?

Thanks

[v-yuxima-msft]

Hi @cymek11 ,

Yes, I use Edit form. And it works ok.

Could you please try to use one edit form and then change the control type of the  (of course you need to unloack the datacard firstly) to choose allowed values as my screeshots shows  next and then change its items to what you want to write to your sql?

Best Regards.

Yumia