Pulling Integers from Access Database with C#

Question

I am attempting to pull data from a 2010 Access database. This should be pulling the vendor number.

Though I get an "Invalid CastException was unhandled by user code. Specified cast is not valid." when I attempt to build the project. I have double checked my column VNNO in my database and it is set to an integer. I must be missing something simple.

    public SelectList GetVendors(String selected = "")
    {
        IList<SelectListItem> vendors = new List<SelectListItem>();
        vendors.Add(new SelectListItem { Text = "", Value = "- Select Vendor -" });

        vendors.Add(new SelectListItem { Text = "TO BE ASSIGNED", Value = "TO BE ASSIGNED" });

        using (OleDbConnection myConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["VendorConnection"].ConnectionString))
        {
            myConnection.Open();

            using (OleDbCommand command = new OleDbCommand())
            {
                command.CommandText = "SELECT * FROM [MY PPUR301] ORDER BY [VNNO] ASC";
                command.Connection = myConnection;
                command.Prepare();

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string text = "";
                        string value = "";

                        if (!reader.IsDBNull(reader.GetOrdinal("VNNO")))
                        {
                            text = reader.GetInt32(reader.GetOrdinal("VNNO")).ToString();
                        }

                        if (!reader.IsDBNull(reader.GetOrdinal("VNNO")))
                        {
                            value = reader.GetInt32(reader.GetOrdinal("VNNO")).ToString();
                        }

                        vendors.Add(new SelectListItem { Text = text.ToUpper(), Value = value.ToUpper() });
                    }
                }
            }
        }

        return new SelectList(vendors, "Text", "Value", selected);
    }

Here is another piece of code that works flawlessly. It pulls the actual name of the vendor.

    public SelectList GetVendors(String selected = "")
    {
        IList<SelectListItem> vendors = new List<SelectListItem>();
        vendors.Add(new SelectListItem { Text = "", Value = "- Select Vendor -" });

        vendors.Add(new SelectListItem { Text = "TO BE ASSIGNED", Value = "TO BE ASSIGNED" });

        using (OleDbConnection myConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["VendorConnection"].ConnectionString))
        {
            myConnection.Open();

            using (OleDbCommand command = new OleDbCommand())
            {
                command.CommandText = "SELECT * FROM [MY PPUR301] ORDER BY [VNNAME] ASC";
                command.Connection = myConnection;
                command.Prepare();

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string text = "";
                        string value = "";

                        if (!reader.IsDBNull(reader.GetOrdinal("VNNAME")))
                        {
                            text = reader.GetString(reader.GetOrdinal("VNNAME")).ToString().Trim();
                        }

                        if (!reader.IsDBNull(reader.GetOrdinal("VNNAME")))
                        {
                            value = reader.GetString(reader.GetOrdinal("VNNAME")).ToString().Trim();
                        }

                        vendors.Add(new SelectListItem { Text = text.ToUpper(), Value = value.ToUpper() });
                    }
                }
            }
        }

        return new SelectList(vendors, "Text", "Value", selected);
    }

Answer 1

Thanks to @JonathanAllen for his help! It is definitely on the DB side of things. I had a macro that was pulling from another system and dumping it into my Access DB that I am using. It was originally dumping decimal formatted numbers. I went in and updated the column to int, however, it wasn't working.

Therefore, I just changed my GetInt to GetDecimal and for the purpose of the program, this won't hurt a thing. This will save time in the long run so that I don't have to create another macro when pulling the original data.

Thanks to all for the help!

Answer 2

This is how I would write that code:

public static Tortuga.Chain.AccessDataSource DataSource = Tortuga.Chain.AccessDataSource.CreateFromConfig("VendorConnection");

public class Foo
{
    public int? VNNO { get; set; }
    public string VnnoString
    {
        get
        {
            if (VNNO.HasValue)
                return VNNO.ToString();
            else
                return "";
        }
    }
    public string VNNAME { get; set; }
}



var items = dataSource.From("MY PPUR301").WithSorting("VNNO").ToCollection<Foo>().Execute();

foreach (var item in items)
{
    vendors.Add(new SelectListItem { Text = item.VNNAME.ToUpper(), Value = item.VnnoString });
}