[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to insert decimal number with "INSERT INTO"

Posted on 2011-10-20
11
Medium Priority
?
259 Views
Last Modified: 2012-05-12
Fellow Experts,

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)
%>

Open in new window


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 :(
0
Comment
Question by:matija_
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:celazkon
ID: 37000267
I would recomend to either change the datatype in the DB or to use the replace function for the discount variable:
discount = Replace(discount,",",".")
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37000276
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"

0
 
LVL 4

Expert Comment

by:Slim81
ID: 37000653
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:matija_
ID: 37000725
@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.
0
 
LVL 17

Accepted Solution

by:
jrm213jrm213 earned 2000 total points
ID: 37000826
Hi,

unfortunately no, mysql needs it to be a decimal point "."
0
 
LVL 6

Author Comment

by:matija_
ID: 37000853
@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?
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37000867
You will need to have functions similar to

function translate_decimal_for_display(val)
{
      //format as needed for display and return
}

function traslate_decimal_for_storage(val)
{
     //format as needed for mysql storage and return
}

and use them as needed
0
 
LVL 6

Author Comment

by:matija_
ID: 37000878
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
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37000894
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
0
 
LVL 6

Author Comment

by:matija_
ID: 37000967
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?
0
 
LVL 7

Expert Comment

by:celazkon
ID: 37010939
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question