incorrect decimal value when inserting mysql using vb.net

Question

Good Day!hope that someone can help me,tnx in advance. i want to insert 1,000,500.00(INPUT FROM TEXTBOX) to mysql database using vb.net but i get this error (incorrect decimal value'1,000,500.00' for column 'Amount at row 1')

== INSERT CODE ==

Query = "INSERT INTO tbl_bills(payee_id,DueDate,Amount,account_id) values 
('" + payee_ID_EnterBills + "','" & cmbbx_EBdueDate.Value.ToString("yyyy-MM-dd") & "','" & txtbx_EBamount.Text & " ', '" + account_ID_EnterBills + "')"

NOTE: 1,000,500.00 ---- input by user in (txtbx_EBamount) TEXTBOX

== My Create Table Statement ==

CREATE TABLE `tbl_bills` (
  `bills_id` int(6) NOT NULL AUTO_INCREMENT,
  `payee_id` int(3) DEFAULT NULL,
  `DueDate` date DEFAULT NULL,
  `Amount` decimal(10,2) DEFAULT NULL,
  `account_id` int(3) DEFAULT NULL,
  `bill_Status_id` smallint(1) DEFAULT '1',
  PRIMARY KEY (`bills_id`),
  KEY `FK_tbl_bills` (`payee_id`),
  KEY `FK_tbl_bills2` (`account_id`),
  KEY `FK_tbl_bills_2` (`bill_Status_id`),
  CONSTRAINT `FK_tbl_bills` FOREIGN KEY (`payee_id`) REFERENCES `tbl_payee` (`payee_id`),
  CONSTRAINT `FK_tbl_bills2` FOREIGN KEY (`account_id`) REFERENCES `tbl_account` (`account_id`),
  CONSTRAINT `FK_tbl_bills_2` FOREIGN KEY (`bill_Status_id`) REFERENCES `tbl_billingstatus` (`bill_status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

You can't insert commas into a numeric field. You need to strip them out of the value before you try to insert it. — Nick, May 10 '19 at 07:29
You should learn ASAP how to use a parameterized query. That's the only way to avoid this kind of errors and worse Sql Injection hacks — Steve, May 10 '19 at 07:33
You should not use the Text property (so a string) as a decimal value in your SQL query. First you have to programmaticly obtain the numeric value of your textbox, then build your query with it. (And with parameters as suggested by @Steve) — KiwiJaune, May 10 '19 at 07:53

score 0 · Accepted Answer · answered May 10 '19 at 07:57

The only correct way to execute sql commands is not through string concatenation and hoping that your server can understand the data that you pass. The correct way is through a parameterized query as shown below.

Dim amount as Decimal

if decimal.TryParse(txtbx_EBamount.Text, amount) Then

   Query = "INSERT INTO tbl_bills(payee_id,DueDate,Amount,account_id) 
            values (@identer,@duedate,@amountm @idaccount)"
   Dim cmd As MySqlCommand = new MySqlCommand(Query, connection)
   cmd.Parameters.Add("@identer", MySqlDbType.Int32).Value = payee_ID_EnterBills
   cmd.Parameters.Add("@duedate", MySqlDbType.Date).Value = cmbbx_EBdueDate.Value
   cmd.Parameters.Add("@amountm", MySqlDbType.Decimal).Value = amount
   cmd.Parameters.Add("@idaccount", MySqlDbType.Int32).Value = account_ID_EnterBills
   cmd.ExecuteNonQuery()
else
   ' Message for invalid input in txtbx_EBamount
End If

Notice that in this way you create parameters for each placeholder in the query text and these parameters specify exactly the data type. Of course you need to set the Value property of the parameter to a variable of the expected data type. So, for example, I don't know if your cmbbx_EBdueDate.Value contains a date or a string. In the latter case you should convert that string to a date and set that date as value.

Glad to be of help. Please use always parameters. The problem you were having is simple but bad things could happen with string concatenation. Check about [Sql Injection Hacks](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, May 10 '19 at 09:07

incorrect decimal value when inserting mysql using vb.net

1 Answers1