Dapper, MS Access, Integers and "no value given for one or more required parameters"

c# dapper ms-access oledb visual-studio-2012

Question

I keep getting the error "no value given for one or more required parameters" on the third attempt to insert in the code below. The first two attempts work fine. I'm running in .NET 4.0, against MS Access in VS 2012. And this problem appears to be consistent whether I'm using a 2007 accdb or a 2000-2003 mdb. I'm using the following code to test:

using Dapper;

using System.Data.OleDb;

namespace DapperTest {
    class Program {
        static string accdb = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=W:\Projects\CSharp\DapperTest\DuhData.accdb;";
        static string mdb = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\Projects\CSharp\DapperTest\DuhData.mdb";

        static void Main(string[] args) {
            var dis = new DisMember { FName = "Heza", LName = "Dufus", Initials = "", HighestGrade = 1 };

            using (OleDbConnection conn = new OleDbConnection(mdb)) {
                try {
                    conn.Execute("DROP TABLE DisMember;");
                }
                catch (System.Exception) { }

                conn.Execute("CREATE TABLE DisMember ( "
                            +     "FName CHAR, "
                            +     "Initials CHAR, "
                            +     "LName CHAR, "
                            +     "HighestGrade Number "
                            + "); "
                );

                // First attempt: This works in Dapper but does not include HighestGrade
                conn.Execute( "INSERT INTO DisMember(FName, Initials, LName) "
                            + "VALUES (@FName, @Initials, @LName);", dis
                            );

                // Second attempt: This works without Dapper and includes HighestGrade
                using (OleDbCommand cmd = conn.CreateCommand()) {
                    conn.Open();
                    cmd.CommandText = "INSERT INTO DisMember(FName, Initials, LName, HighestGrade) VALUES (?, ?, ?, ?); ";
                    cmd.Parameters.AddWithValue("FName", dis.FName);
                    cmd.Parameters.AddWithValue("Initials", dis.Initials);
                    cmd.Parameters.AddWithValue("LName", dis.LName);
                    cmd.Parameters.AddWithValue("HighestGrade", 1);
                    cmd.ExecuteNonQuery();
                }

                // Third attempt: This does not work with Dapper because of HighestGrade
                conn.Execute("INSERT INTO DisMember(FName, Initials, LName, HighestGrade) "
                            + "VALUES (@FName, @Initials, @LName, @HighestGrade);", dis
                            );
            }
        }
    }

    public class DisMember {
        public DisMember( ) { }
        public string FName { get; set; }
        public string LName { get; set; }
        public string Initials { get; set; }
        public int HighestGrade { get; set; }
    }
}

So, is this a bug in Dapper? OleDB? Access 2013? Or something else?

Any and all clues greatly appreciated.

Accepted Answer

Alright, I figured it out. Playing a hunch on another answer I found elsewhere that indicated there was a bug in Dapper that had to do with the order of the columns/parameters, I modified the code alphabetically like this:

conn.Execute( "INSERT INTO DisMember(FName, HighestGrade, Initials, LName) "
            + "VALUES (@FName, @HighestGrade, @Initials, @LName);", dis
            );

And it works. According to the article I found, this bug had been fixed in Dapper, but... apparently not.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why