I have a MySQL decimal(5,2) field to update. The code is:
Dim strSQL As String = "Update table Set cost=? Where recid=1"
Dim cmdSQL As New OdbcCommand(strSQL,cnDbase)
Dim prmCost As New OdbcParameter("?",OdbcType.Decimal)
Dim decCost As Decimal = Convert.ToDecimal(textbox.Value)
prmcost.Precision = 5
prmCost.Scale = 2
prmCost.Value = decCost
cmdSQL.Parameters.Add(prmCost)
cmdSQL.ExecuteNonQuery()
A server error occurs upon execution: "ERROR[07006][MySQL][ODBC 3.51 Driver][mysqld-4.0.27-max-log] Restricted data type attribute violation (SQL_C_NUMERIC)." There is no mention of this SQLSTATE error in the MySQL documentation of Appendix C.1(server) or C.2(client). One reference says that when data is retrieved from a decimal field the ODBC driver uses a SQL_C_NUMERIC data conversion type. However, if the driver does not support the type then an error may occur. ODBC 3.0 drivers are supposed to have this C data type to allow applications to directly handle numeric data. The MySQL, ODBC 3.51 driver supposedly handles these types. Your help would be much appreciated.
Bruce