none
Insert decimal(MS Access) RRS feed

  • Question

  • MS Access, C#

    1. Insert decimal value to table.

    2. Exception: "Type mismatch".

    3. Current culture Windows: decimal separator in currency values = ","      decimal separator in numeric values = ","

    4. In my mind  solution is change locale of ms access by connectionString.

    5.Code:

    string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"test.mdb\";Locale Identifier = 1033";

    OleDbConnection connection = new OleDbConnection(ConnectionString);

    string insertcommand = "INSERT INTO Test (DecimalField, Id) VALUES (?, ?)";

    OleDbCommand command = new OleDbCommand(insertcommand, connection);

    OleDbParameter param = new OleDbParameter("@DecimalField", 999.99M);

    param.DbType = DbType.Currency;

    command.Parameters.Add(param);

    command.Parameters.Add(new OleDbParameter("@Id", Guid.NewGuid()));

    try

    {

    connection.Open();

    command.ExecuteNonQuery();

    }

    catch (Exception ex)

    {

    string exep = ex.Message;

    }

    finally

    {

    connection.Close();

    }

     

    6. Write any information, please.

    Saturday, April 26, 2008 4:15 PM

All replies

  • Write any information, please.

     

    What is your problem with this code? You should explain why you posted it.

    Saturday, April 26, 2008 11:10 PM
  • EXCEPTION: "TYPE MISMATCH"

    1. Insert decimal value to table.

    2. Exception: "Type mismatch".

    3. Current culture Windows: decimal separator in currency values = ","      decimal separator in numeric values = ","

    Sunday, April 27, 2008 4:36 AM
  •  Valadimir wrote:

    EXCEPTION: "TYPE MISMATCH"

    1. Insert decimal value to table.

    2. Exception: "Type mismatch".

    3. Current culture Windows: decimal separator in currency values = ","      decimal separator in numeric values = ","

     

    I've never done Access OleDb but there is a chance that you are using the wrong OleDb Data Type.

     

    According to this article

     

    INFO: OleDbType Enumeration vs. Microsoft Access Data Types

    List of the Most Common Data Type Mappings

    You should use OleDbType.Numeric, not decimal for currency.

     

    Look at this line:

    Access       DB Type      OleDbType                NET Type       Member Name

    Currency Decimal DBTYPE_NUMERIC System.Decimal OleDbType.Numeric

     

    Perhaps this is your problem.

     

    Sunday, April 27, 2008 3:59 PM
  • Thanks, but in database Decimal Field(not currency).

    I use OleDbType.Decimal and DbType.Decimal.

     

    Probably problems, because In VBA not exist decimal field.

     

    I want set separator for Access to '.' , but don't know how do it

    Change data type to double is probably only solution.

     

    schema.ini for text files, i can use this technologies for resolve my problem?

    Sunday, April 27, 2008 7:12 PM
  •  Valadimir wrote:

    Thanks, but in database Decimal Field(not currency).

    I use OleDbType.Decimal and DbType.Decimal.

     

    Probably problems, because In VBA not exist decimal field.

     

    I want set separator for Access to '.' , but don't know how do it

    Change data type to double is probably only solution.

     

    schema.ini for text files, i can use this technologies for resolve my problem?

     

    Why don't you try this routine. I use it to create OleDbParameters. This one for string type. I have other overloads but none for currency. It is more straightforward in terms of clarity of the process.

     

    public static OleDbCommand createOleDbParameter ( OleDbCommand cmdm, string parName, string parValue )

    { // overload: type string

    OleDbParameter par2 = cmdm.CreateParameter ( );

    par2.SourceVersion = DataRowVersion.Proposed;

    par2.ParameterName = parName;

    par2.DbType = DbType.String;

    par2.Direction = ParameterDirection.InputOutput;

    par2.SourceColumn = parName;

    par2.Value = parValue;

    par2.Size = parValue.Length;

    cmdm.Parameters.Add ( par2 );

    return cmdm;

    } // createOleDbParameter

    Monday, April 28, 2008 2:37 AM
  • Thanks, but i do that say me my chief.

    Monday, April 28, 2008 5:18 AM
  •  Valadimir wrote:

    Thanks, but i do that say me my chief.

     

    Hi,

     

    I cannot understand clearly what you are trying to say? You mean you are referring to your original post?

     

    Anyhow, I modified that routine for you and at least it compiled. I think there is a 95% chance it will work. Just ttry it.

     

    You should call it with your decimal number 999.99M as a parameter. The routine follows:

     

    public static OleDbCommand createOleDbParameter ( OleDbCommand cmdm, string parName, System.Decimal parValue )

    { // overload: type Decimal

    OleDbParameter par2 = cmdm.CreateParameter ( );

    par2.SourceVersion = DataRowVersion.Proposed;

    par2.ParameterName = parName;

    par2.OleDbType = OleDbType.Numeric;

    par2.Direction = ParameterDirection.InputOutput;

    par2.SourceColumn = parName;

    par2.Value = parValue;

    par2.Size = 8;

    cmdm.Parameters.Add ( par2 );

    return cmdm;

    } // createOleDbParameter

     

    I think I followed the mapping guidelines ( OleDb ==> .NET ==> Access ) in making the changes. Note that the OleDbType is Numeric

    Monday, April 28, 2008 2:33 PM
  •  

    I'm have the exact same problem only difference is my default setting is US-EN and I'm trying to run on a machine with German locale...

     

    This is a cultureinfo issue as much as I can tell... here is a link to my post

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3343441&SiteID=1

     

    I have a app that updates an Access Database via a  datagrid view. I'm updating two column of type Single. The code works perfectely when the regional settings are default US-EN.

     

    For computers running in germany an update to the backend database throw a datatype mismatch error.

     

    I change the decimal separator on the computer with 'German' for region settings from Comma (,) to (.) period and everything works fine.

     

    I add thread culture to US-ENG at the startup of the application. Now the error disappears but then 2.5 is now stored as 25 in the access database.

     

    Any pointers what is missing......

    I add these lines at the beginning of the application soon after the designer code

            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
            System.Threading.Thread.CurrentThread.CurrentUICulture = New System.Globalization.CultureInfo("en-US")

     

    This is a MDI app and this code is in the container window...

    • Proposed as answer by BatoFS Thursday, December 11, 2008 3:34 PM
    Wednesday, May 14, 2008 10:31 PM