none
Linq to Microsoft Access RRS feed

  • Question

  • I created a Access database, and added a table named "Person". the definition of this table is:

    Person

    ID: auto number

    Name: text(20)

    Address: text(50)

     

    In c#, I mapped this table to

    [Table]

    class Person

    {

    [Column(IsPrimaryKey = true)]

    public long ID {get; set;}

    [Column]

    public string Name{get; set;}

    [Column]

    public string Address{get; set;}

    }

     

    And wrote such code:

    string connectionString = "Provider=.....";

    OleDbConnection con = new OleDbConnection(connectionString);

    DataContext ctx = new DataContext(con);

    Table persons = ctx.GetTable<Person>;

    var query = from p in persons select p;

    foreach (Person p in query)

    {

    Console.WriteLine(p.Name);

    }

     

    I got an error says "Specified cast is not valid", why? and how to resolve it?

     

    Another question is if I want to insert a new record using linq, how do I deal with "ID"(auto number) column?

     

    Many thanks for your advice.

    Tuesday, February 26, 2008 1:48 AM

Answers

  • Hi liuflash2000

     

    Try to use the code below to map your table, hope fully that will resolve the error.

     

    [Table(Name = "Person")]

        class Person

        {

            private int Id;

            private string name;

            private string address;

     

            public Person() { }

     

            [Column(Id = true, AutoGen = true)]

            public long ID

            {

                get { return Id; }

                set { Id = value; }

            }

            [Column]

            public string Name

            {

                get { return name; }

                set { name = value; }

            }

            [Column]

            public string Address

            {

                get { return address; }

                set { address = value; }

            }

        }

     

    To add person should be simple, don’t worry about the Id it will auto generate while inserting data

     

            private void AddNewPersonToDB()

            {

                string connectionString = "Provider=.....";

                OleDbConnection con = new OleDbConnection(connectionString);

                DataContext db = new DataContext(con);

                Table<Person> PersonTable = db.GetTable<Person>();

                Person p1 = new Person();

                p1.Name = "Name";

                p1.Address = "Address";

                PersonTable.Add(p1);

                db.SubmitChanges();

            }

     

    Hope this helps.

    Tuesday, February 26, 2008 7:28 AM

All replies

  • Hi liuflash2000

     

    Try to use the code below to map your table, hope fully that will resolve the error.

     

    [Table(Name = "Person")]

        class Person

        {

            private int Id;

            private string name;

            private string address;

     

            public Person() { }

     

            [Column(Id = true, AutoGen = true)]

            public long ID

            {

                get { return Id; }

                set { Id = value; }

            }

            [Column]

            public string Name

            {

                get { return name; }

                set { name = value; }

            }

            [Column]

            public string Address

            {

                get { return address; }

                set { address = value; }

            }

        }

     

    To add person should be simple, don’t worry about the Id it will auto generate while inserting data

     

            private void AddNewPersonToDB()

            {

                string connectionString = "Provider=.....";

                OleDbConnection con = new OleDbConnection(connectionString);

                DataContext db = new DataContext(con);

                Table<Person> PersonTable = db.GetTable<Person>();

                Person p1 = new Person();

                p1.Name = "Name";

                p1.Address = "Address";

                PersonTable.Add(p1);

                db.SubmitChanges();

            }

     

    Hope this helps.

    Tuesday, February 26, 2008 7:28 AM
  • Hi Bulldog.NET (South Africa) ,

     

    I used visual studio 2008, and can not find some keywords you provided(AutoGet,Table.Add...), I think you use an earlier version.

     

    But with your help I resolved the issue, by changing the long id to int id.

     

     

    many thanks!

    Wednesday, February 27, 2008 4:39 AM