none
InvalidOperationException while inserting into database RRS feed

  • Question

  • Hi everyone,

     

    I'm new to LINQ to SQL but it's really awesome, so I tried to insert some data into my SQL databse. Here's the scenario:

    I have a database called "WebShopDB" with a table named "dbo.webshop_customer". In C# I create a new Customer object and want to insert it into the database via LINQ. The problem is, that there is a "InvalidOperationException" at the code line "customers.InsertOnSubmit(customer);" in LINQtoSQL.cs.

    I don't know why, can anybody help me out?

    My dbo.webshop_customer table is as follows: email, password, firstname, lastname, street, zip, city, telephone, id

     

    So here is my Customer-class:

    [Table(Name="dbo.webshop_customer")]
      public class Customer
      {
    
        private int id;
        private MailAddress email;
        private string password;
        private string firstName;
        private string familyName;
        private Address address;
        private TelephoneNumber telephoneNumber;
    
    
        public Customer() { }
    
        public Customer(int id,
                  MailAddress email,
                  string password,
                  string firstName,
                  string familyName,
                  Address adress,
                  TelephoneNumber telephoneNumber)
        {
          this.id = id;
          this.email = email;
          this.password = password;
          this.firstName = firstName;
          this.familyName = familyName;
          this.address = adress;
          this.telephoneNumber = telephoneNumber;
        }
    
    
    
        [Column(IsPrimaryKey = true)]
        public int Id
        {
          get
          {
            return id;
          }
        }
    
        [Column(Name = "email")]
        public MailAddress Email
        {
          get
          {
            return email;
          }
        }
    
        [Column(Name = "password")]
        public string Password
        {
          get
          {
            return password;
          }
        }
    
        [Column(Name = "firstname")]
        public string FirstName
        {
          get
          {
            return firstName;
          }
        }
    
        [Column(Name = "lastname")]
        public string FamilyName
        {
          get
          {
            return familyName;
          }
        }
    
        //[Column(Name = "address")]
        public Address Address
        {
          get
          {
            return address;
          }
        }
    
        [Column(Name = "telephone")]
        public TelephoneNumber TelephoneNumber
        {
          get
          {
            return telephoneNumber;
          }
        }
    
      }
    

    I commented "address" so it can't be mapped to the column of the table because it has its own class. I do this later.

     

    Here is my LINQtoSQL.cs:

    public class LINQtoSQL
      {
        public static void saveCustomer(Customer customer)
        {
          string path = System.IO.Path.GetFullPath(@"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WebShopDB.mdf");
          DataContext db = new DataContext(path);
          Table<Customer> customers = db.GetTable<Customer>();
    
          customers.InsertOnSubmit(customer);
          db.SubmitChanges();
        }
      }
    

    I don't know why there is this exception. As I said I am really new to LINQ and have few experience with .NET as I am in a learning experience at the moment.

    Sorry, if my English is not very well, it's not my mother language. Thanks a lot guys for your help!

     

    Wednesday, June 1, 2011 3:09 PM

Answers

  • Have you attached this DB to any SQL server?

    If yes then the file is used by the MS SQL server. In this case you should use a connection string to the DB instead of trying to open the mdf file.


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Friday, June 3, 2011 11:25 AM

All replies

  • Hi Gurkensalat_89,

    Could you please post the details of the exception?

    Thanks.


    Regards, Peter //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, June 1, 2011 4:39 PM
  • Here is a screen shot of the exception:

     

     

    It's german, it actually says: There is a Int32 Id-Property which is write-protected and it can't be written on it. But the customer-class has a constructor where you can give it an id. Is it maybe because of I can't write into the database column "id"? I didn't check it.

    Friday, June 3, 2011 9:28 AM
  • Hi,

     

    As this is the only int in your Customer class you should check it.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Friday, June 3, 2011 9:34 AM
  • Okay, i got it now: I had to insert set-methods into my Customer-class. I don't know why because I create a Customer object with the constructor, so usually set-methods are not necessary. But I added them and now the exception didn't show up any more.

    But another exception showed up:

     

     

    Again the translation: The database file ...\WebShopDB.mdf couldn't be opened because the operation system denied it (system error 5). Error while adding an automatic named databse to the file ...\WebShop.mdf. There already is a databse with this name, the file couldn't be opened or it hasn't the UNC approval.

    I googled, and i couldn't find anything for this exception. I guess it has something to do with my Microsoft SQL Server? Maybe some rights settings or something? I don't know.

    Friday, June 3, 2011 10:42 AM
  • Have you attached this DB to any SQL server?

    If yes then the file is used by the MS SQL server. In this case you should use a connection string to the DB instead of trying to open the mdf file.


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Friday, June 3, 2011 11:25 AM
  • Hi,

    I think you're right. I changed the DataContext code row as follows:

    public static void saveCustomer(Customer customer)
        {
          //string path = System.IO.Path.GetFullPath(@"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WebShopDB.mdf");
          DataContext db = new DataContext("Data Source=localhost;" + "Initial Catalog=WebShopDB;" + "User ID=WebShop_LOGIN;" + "Password=test;");
          Table<Customer> customers = db.GetTable<Customer>();
    
          customers.InsertOnSubmit(customer);
          db.SubmitChanges();
        }
    
    I just want to know if the connection string in the DataContext constructor is correct? I can't test it, because for unknown reasons LINQ can't login with the given user and password. But I really checked a thousand times the SQL server: the login data is correct! I even deleted the user and created a new one, nothing worked. But that is another problem. First I want to know, if the connection string is correct.

    Friday, June 3, 2011 12:18 PM
  • Hi,

     

    It looks fine for me.

    Have you tried to log in to the DB with MS SQL Management studio? If you can connect to the DB with the same username/password and select the Customer table with that connection then it should be OK.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Friday, June 3, 2011 12:55 PM
  • No, that doesn't work either. I always connect via Windows Authentication. But I didn't set up the database, so I will ask the guy who set it up, maybe he knows. Thanks a lot crick3t.
    Saturday, June 4, 2011 11:41 AM