I have a form with several radio buttons that are not required fields. When the form is submitted with no selection made the value inserted into the MySQL database is “UL” for the radio button field. I want the field to be blank if there is no selection made. I verified using debug mode that the UL value is coming from Scriptcase. The insert statement being generated contains that value for each blank radio button. Is there any way to work around this so the inserted value is either null or an empty string?
Check your data in onBeforeInsert or onBeforeUpdate
Replace it with the value you want if it’s wrong
I was thinking of running a SQL statement in the afterinsert, but the beforeinsert makes a lot more sense. Now I have to figure out if there is an efficient way to replace the data in dozens of fields without explicitly writing an if/then statement for each field.
Time to use the forum search function to see if I can find a way to loop through all the fields and check each one.
So I figured out how to loop through the fields by querying the column names from the MySQL database, but when I did that in the BeforeInsert the value of each field is still blank, not “UL”. That value must get put into the SQL statement somewhere else. It isn’t actually the value of the field in the form. If I want to fix it, it looks like I’ll have to let SC insert the record and then update the values in the AfterInsert event.
I’ve seen this before, but I don’t remember where anymore.
I do know, however, that it was associated with a field in the MySQL database.
When I changed the field, this “UL” also disappeared.
You can write how you have a defined field in the database
and how you have it defined in Scriptcase.
Maybe it helps me remember in which project I had this and find a solution.
@rik,
The field was initially set as varchar(1) as I was storing a single letter for each radio button option. If it matters, the radio buttons have three options. P/F/C (Pass/Fail/Corrected). The values for the radio button come from an automatic lookup: “select pass_fail_value, pass_fail_text from pass_fail_values order by pass_fail_text desc”. It is defined in SC as a radio button, HTML type radio, SQL type varchar.
I was getting errors when submitting the form so I used debug mode in SC to look at what the insert statement said. When I saw that it was trying to insert ‘UL’ into the field I changed the field to be varchar(2) to get rid of the error.
I just tried making the field an integer since you mentioned it had something to do with the field type in MySQL. That seems to allow a workaround to the problem.
When I set it up as an integer and left it blank, if the field was set to have a value on insert of “Calculated by the database if empty” then the SQL insert statement didn’t include the field at all. It defaulted to null in the database.
If the field was set up as an integer and left blank but did NOT have any default value on insert then it was still included in the SQL insert statement but had a value of zero. That would be a problem if I had used zero as one of my values for a radio button selection.
So I guess I’m going to change all of these fields to integer with ‘calculated by the database if empty’ to get the desired result of null when no selection is made.
I’m still not sure if this is a bug or some sort of intentional design on the part of Scriptcase.
I’ve come across this before… I usually fix it by forcing a valid response by defaulting the radio button. Even if the default value is ‘not submitted’.
Its much simpler as all you need to do is set one of the radio button fields as default and gives you an explicit value in the database