8 Replies - 2893 Views - Last Post: 09 March 2012 - 04:46 AM Rate Topic: -----

#1 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

OleDb DataTypes Problem

Posted 08 March 2012 - 05:32 PM

I have problem with my parameters and data types. when I call ExecuteNonQuery i get error: "Data type mismatch in criteria expression." I am using MS Access database. Please help me find the error.
here is the code. In the end of line is comment with original data type from database.
      cmd.Parameters.AddWithValue("@pDatumUplate",Convert.ToDateTime( dtUplate.Value)); //DateTime
            cmd.Parameters.AddWithValue("@pUlog",Convert.ToDecimal( txtUlog.Text) );//Currency
            cmd.Parameters.AddWithValue("@pDobitak", Convert.ToDecimal(txtDobitak.Text));//Currency
            cmd.Parameters.AddWithValue("@pDobitan",Convert.ToBoolean( chDobitan.Checked) );//Boolean
            cmd.Parameters.AddWithValue("@pBrojParova",Convert.ToInt16( txtBrojParova.Text ));//Number
            cmd.Parameters.AddWithValue("@pKoeficijent",Convert.ToDecimal( txtKFuk.Text) );//Currency
            cmd.Parameters.AddWithValue("@pOpis", txtOpis.Text );//text
            cmd.Parameters.AddWithValue("@pKomentar", txtKomentar.Text );//text
            cmd.Parameters.AddWithValue("@pMt",Convert.ToDecimal( txtMT.Text) );//Currency
            cmd.Parameters.AddWithValue("@pDatumUnosa", DateTime.Now );//DateTime

I don't know which is right equivalent to oledb currency in c#.I was try with double and decimal,but nothing works.

Is This A Good Question/Topic? 0
  • +

Replies To: OleDb DataTypes Problem

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1012
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: OleDb DataTypes Problem

Posted 08 March 2012 - 06:00 PM

Decimal is the right type to use according to MSDN.

You didn't post all the code, where is the query?
Was This Post Helpful? 0
  • +
  • -

#3 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

Re: OleDb DataTypes Problem

Posted 08 March 2012 - 06:06 PM

View PostMomerath, on 08 March 2012 - 06:00 PM, said:

Decimal is the right type to use according to MSDN.

You didn't post all the code, where is the query?

here is the query:
 string str = "INSERT INTO Listic (DatumUplate,Ulog,Dobitak,Dobitan,BrojParova,Koeficijent,Opis,Komentar,Mt,DatumUnosa) VALUES (@pDatumUplate,@pUlog,@pDobitak,@pDobitan,@pBrojParova, @pKoeficijent,@pOpis,@pKomentar,@pMt,@pDatumUnosa)";


i have exclude first column because it's auto number,is that ok?

This post has been edited by sela007: 08 March 2012 - 06:12 PM

Was This Post Helpful? 0
  • +
  • -

#4 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1012
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: OleDb DataTypes Problem

Posted 08 March 2012 - 06:47 PM

OleDB (Check the Remarks section) doesn't use the @param method for paramters, it uses the ? (and only a ?). You need to change your query to
string str = "INSERT INTO Listic (DatumUplate,Ulog,Dobitak,Dobitan,BrojParova,Koeficijent,Opis,Komentar,Mt,DatumUnosa) VALUES (?,?,?,?,?,?,?,?,?,?)";


You also need to make sure you add the parameters in the order that they should replace the ?. So you add what you want to be DatumUplate first, Ulog second, etc.

This post has been edited by Momerath: 08 March 2012 - 06:48 PM

Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4498
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: OleDb DataTypes Problem

Posted 08 March 2012 - 08:15 PM

That's actually only half true. From experience with Access (which uses OleDb), you can use standard @-prefaced parameters, but the names don't matter. It behaves the same way as it does with question marks. First parameter added to the command object matches the first parameter in the query, regardless of the text of the parameter. Example that I just tested this with:

const string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\dev\Database1.accdb;Persist Security Info=False;";
const string query = "select * from People where FirstName = @firstname and LastName = @lastname";
using(var conn = new OleDbConnection(connStr))
using(var cmd = new OleDbCommand(query, conn)) {
    cmd.Parameters.AddWithValue("@firstname", "Curtis");
    cmd.Parameters.AddWithValue("@lastname", "Rutland");
    conn.Open();
    var reader = cmd.ExecuteReader();
    while(reader != null && reader.Read()) {
        Console.WriteLine("{0} {1}", reader["LastName"], reader["FirstName"]);
    }
}



But it works the same regardless of what I name the parameters, and regardless if the first string in AddWithValue matches one of them. You can even do this:

cmd.Parameters.AddWithValue(null, "Curtis");
cmd.Parameters.AddWithValue(null, "Rutland");



And it works the same.

Sorry for side tracking the discussion.
Was This Post Helpful? 0
  • +
  • -

#6 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1012
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: OleDb DataTypes Problem

Posted 08 March 2012 - 08:59 PM

View PostCurtis Rutland, on 08 March 2012 - 08:15 PM, said:

That's actually only half true. From experience with Access (which uses OleDb), you can use standard @-prefaced parameters, but the names don't matter. It behaves the same way as it does with question marks

Interesting. I don't use Access, I just know that 99% of all problems with OleDb comes from people placing the parameters in the wrong order :)
Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4498
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: OleDb DataTypes Problem

Posted 08 March 2012 - 09:46 PM

I don't use it when I can avoid it, but in my previous job that was completely impossible (as a core system of record was a big Access application).
Was This Post Helpful? 0
  • +
  • -

#8 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

Re: OleDb DataTypes Problem

Posted 09 March 2012 - 04:06 AM

i am always using parameter names with access, it works. I also try with ? but still i get same error. Unfortunately I don't know in which line. But I know it's data type mismatch in criteria expression.
Was This Post Helpful? 0
  • +
  • -

#9 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

Re: OleDb DataTypes Problem

Posted 09 March 2012 - 04:46 AM

I've try in the other way. Now it works. But I still don't where was the problem.
cmd.Parameters.Add("@pDatumUplate", OleDbType.Date);//DateTime
            cmd.Parameters[0].Value = dt;

            cmd.Parameters.Add("@pUlog", OleDbType.Currency );
            cmd.Parameters[1].Value = Convert.ToDecimal(txtUlog.Text);

            cmd.Parameters.Add("@pDobitak", OleDbType.Currency );
            cmd.Parameters[2].Value = Convert.ToDecimal(txtDobitak.Text); 

            cmd.Parameters.Add("@pDobitan", OleDbType.Boolean );
            cmd.Parameters[3].Value = chDobitan.Checked ;

            cmd.Parameters.Add("@pBrojParova", OleDbType.Integer);
            cmd.Parameters[4].Value = Convert.ToInt16 (txtBrojParova.Text);

            cmd.Parameters.Add("@pKoeficijent", OleDbType.Currency);
            cmd.Parameters[5].Value = Convert.ToDecimal(txtKFuk.Text);

            cmd.Parameters.Add("@pOpis", OleDbType.VarChar);
            cmd.Parameters[6].Value = txtOpis.Text;

            cmd.Parameters.Add("@pKomentar", OleDbType.VarChar);
            cmd.Parameters[7].Value = txtKomentar.Text;
            
            cmd.Parameters.Add("@pMt", OleDbType.Currency);
            cmd.Parameters[8].Value = Convert.ToDecimal(txtUlog.Text);

            cmd.Parameters.Add("@pDatumInosa", OleDbType.Date);
            cmd.Parameters[9].Value = DateTime.Now;

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1