MySQL Non String Value Storage

I have an application that communicates with mySQL for simple data retrieval and storage. I want to save a numeric value in the database from a text field, namely

rs.field("Cost").DoubleValue=val(fldCost.text)

The value does not get stored in the database table, field type being a double with 8 numeric and 2 decimal.

Any ideas where I am going wrong.

how about supplying more code?
this just sets a value in the record set… how are you commiting it back to database?

After the text fields have been updated this writes out the contens. All the other fields update correctly.

[code] dim d as new date
dim sql as string
sql=“select * from organisation where ID_Organiser LIKE BINARY '”+fldID_Organiser.text+"’"
rs=db.SQLSelect(sql)
rs.edit
//update contents of controls to the database
//tab panel 1
rs.Field(“Organiser”).StringValue=SQLify(fldOrganiser.text)
rs.Field(“Organiser_Type”).StringValue=SQLify(popupOrganiser_Type.text)
rs.Field(“Contact_Name”).StringValue=SQLify(fldContact_Name.text)

//address details
rs.Field(“Address1”).StringValue=SQLify(fldAddress1.text)
rs.Field(“Address2”).StringValue=SQLify(fldAddress2.text)
rs.Field(“Address3”).StringValue=SQLify(fldAddress3.text)
rs.Field(“Address4”).StringValue=SQLify(fldAddress4.text)
rs.Field(“Address5”).StringValue=SQLify(fldAddress5.text)
rs.Field(“Post_Code”).StringValue=SQLify(fldPost_Code.text)
rs.Field(“PCT”).StringValue=SQLify(popupPCT.text)

rs.Field(“Run_By”).StringValue=popupRun_By.text
rs.field(“Previous_Name”).StringValue=fldPrevious_Name.text
rs.field(“Additional_Notes”).StringValue=fldAdditional_Notes.text

rs.Field(“Email”).StringValue=SQLify(fldEmail.text)
rs.field(“Additional_Notes”).StringValue=SQLify(fldAdditional_Notes.text)

//TAB3 Administration
rs.field(“Last_Updated”).StringValue=d.SQLDate
//no need to update creation date
//rs.field(“Record_Created”).StringValue=fldRecord_Created.text
rs.field(“Updated_By”).StringValue=SQLify(username)
rs.field(“Flag1”).StringValue=SQLify(fldflag1.text)
rs.field(“Flag2”).StringValue=SQLify(fldflag2.text)
rs.field(“Cost”).DoubleValue=val(fldCost.text)

//convert boolean checkbox for deleted to integer for database
if cbDiscount.value then
rs.field(“Discount”).IntegerValue=1
else
rs.field(“Discount”).IntegerValue=0
end if

if cbBiannual.value then
rs.field(“Biannual_Visits”).IntegerValue=1
else
rs.field(“Biannual_Visits”).IntegerValue=0
end if

if cbDeleted.value then
rs.field(“Deleted”).IntegerValue=1
else
rs.field(“Deleted”).IntegerValue=0
end if

//actually update the data and commit it to the database
rs.update
//##check for error here
db.commit[/code]

The more I look at this the more I think its a bug. If I enter a value into the database field using Navicat it is stored correctly. When I read the values back with relbasic it shows the correct value in my ‘window’. When I save the record however the value is reverted back to 0.00 and a check of the database back in Navicat shows that it is now 0.00. It seems that XOJO cannot write the value to a double field?

I have tracked this down. In my fldCost field I had a mask string of \\£###.00 to display the number correctly on screen as a ‘currency amount’. I guess my question is now how to display the currency number, but at the same time store the value in the database. Whats the best way of dealing with this sort of scenario.

Thats the culprit then
If the text is £123.45 the VAL of that is 0
Strip out the currency symbol

Thanks Norman, got there in the end with rs.field("Cost").StringValue=mid(fldCost.text,2)