[sela007]

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.

[Momerath]

Decimal is the right type to use according to MSDN.

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

[sela007]

Momerath, 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

[Momerath]

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

[Curtis Rutland]

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.

[Momerath]

Curtis 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

[Curtis Rutland]

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

[sela007]

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.

[sela007]

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;