How to insert decimal number with "INSERT INTO"
Fellow Experts,
I'm using the following SQL query for inserting into MySQL database:
How to insert decimal number into database? The script returns "Column count doesn't match value count at row 1" (it obviously thinks I'm trying to insert another value after comma in decimal number)
Notes:
- I don't want to use RS.Open, RS.AddNew, RS.Update procedure due to speed benefits of INSERT method.
- "Id" and "Discount" values are populated from another table and I have thousands of those values which I want to insert into this "tbl" so they need to stay in variables.
- "fld1" is VARCHAR(50), "fld2" is DECIMAL(18,4)
Any help appreciated, Google was not my friend this time :(
I'm using the following SQL query for inserting into MySQL database:
<%
Id = "8059"
Discount = "1,234"
SQL = "INSERT INTO tbl (fld1, fld2) VALUES ('" & Id & "', " & Discount & ")"
Conn.Execute(SQL)
%>How to insert decimal number into database? The script returns "Column count doesn't match value count at row 1" (it obviously thinks I'm trying to insert another value after comma in decimal number)
Notes:
- I don't want to use RS.Open, RS.AddNew, RS.Update procedure due to speed benefits of INSERT method.
- "Id" and "Discount" values are populated from another table and I have thousands of those values which I want to insert into this "tbl" so they need to stay in variables.
- "fld1" is VARCHAR(50), "fld2" is DECIMAL(18,4)
Any help appreciated, Google was not my friend this time :(
my guess would be, numbers don't contain commas.
Discount = "1234"
If other country formatting and that comma is supposed to be a decimal,
Discount = "1.234"
Discount = "1234"
If other country formatting and that comma is supposed to be a decimal,
Discount = "1.234"
Looks like you are missing your other set of apostrophes around your Discount variable.
VALUES ('" & Id & "','" & Discount & "')"
As for forcing your database to display 2 decimal places, you need to make your field a decimal field. Something like this would work: decimal(10,2) --10 is the total length of the field and the 2 is the number of decimal places to show. This may seem a bit confusing, depending on how you are working with your MySQL DB. Are you using MyPHPAdmin?
If your field is a varchar (text), then you can format your number before inserting: FormatNumber(variable, 2).
For example: VALUES ('" & Id & "','" & FormatNumber(Discount, 2) & "')"
FormatNumber is a ASP function.
VALUES ('" & Id & "','" & Discount & "')"
As for forcing your database to display 2 decimal places, you need to make your field a decimal field. Something like this would work: decimal(10,2) --10 is the total length of the field and the 2 is the number of decimal places to show. This may seem a bit confusing, depending on how you are working with your MySQL DB. Are you using MyPHPAdmin?
If your field is a varchar (text), then you can format your number before inserting: FormatNumber(variable, 2).
For example: VALUES ('" & Id & "','" & FormatNumber(Discount, 2) & "')"
FormatNumber is a ASP function.
ASKER
@celazkon and @jrm213jrm213
is there a way not to replace with comma? I really don't want to use dots. My locale is always set up for "," to be decimal separator, no matter of country setting.
@Slim81
fields are already formatted: "fld1" is VARCHAR(50), "fld2" is DECIMAL(18,4)
Placing another set of apostophes would return an error.
is there a way not to replace with comma? I really don't want to use dots. My locale is always set up for "," to be decimal separator, no matter of country setting.
@Slim81
fields are already formatted: "fld1" is VARCHAR(50), "fld2" is DECIMAL(18,4)
Placing another set of apostophes would return an error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@jrm213jrm213
Hm, how come when I update the decimal field with RS.Update, it writes number with comma to MySQL into the same field where INSERT INTO doesn't?
Hm, how come when I update the decimal field with RS.Update, it writes number with comma to MySQL into the same field where INSERT INTO doesn't?
You will need to have functions similar to
function translate_decimal_for_disp
{
//format as needed for display and return
}
function traslate_decimal_for_stora
{
//format as needed for mysql storage and return
}
and use them as needed
function translate_decimal_for_disp
{
//format as needed for display and return
}
function traslate_decimal_for_stora
{
//format as needed for mysql storage and return
}
and use them as needed
ASKER
I just tested with "INSERT INTO tbl (fld1, fld2) VALUES ('" & Id & "', " & Replace(Discount, ",", ".") & ")" and it seems MySQL transforms it automatically to "," again when inserted :) Great. Thanks
does it store it in the database that way? As far as I know MySQL requires it to be a period.
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
ASKER
It stores it in database with comma, although, it is being replaced with dot prior inserting. I check the values with MySQL Administrator. Maybe because of cp1250 database locale?
generally, the decimal value needs to be always with . sign
The stored data are always like that, but since you use different formating for displaying the data, what you see displayed is converted according to the locale settings.
e.g. the number is in database table's column stored lik 1.2345 but when you select the column, it's displayed according to the locale, in your case as 1,2345
The stored data are always like that, but since you use different formating for displaying the data, what you see displayed is converted according to the locale settings.
e.g. the number is in database table's column stored lik 1.2345 but when you select the column, it's displayed according to the locale, in your case as 1,2345
discount = Replace(discount,",",".")