none
new/update/delete record RRS feed

  • Question

  • I have had a look at other posts but I have yet to understand the code I need to create a new record. The principle table "Items" has 5 tables with foreign keys, Country, Make, Category, Status and Type. All but one do not allow nulls. To create a new record I do this:

     

                    using ( MaterialEntities me = new MaterialEntities() )
                    {
                        item newItem = new item();
                        newItem.name = txtItemName.Text.Trim();
                        newItem.model = txtModel.Text.Trim();
                        newItem.serialNo = txtSerialNo.Text.Trim();
                        newItem.aiNumber = txtAirbusNo.Text.Trim();
                        newItem.cost = decimal.Parse( txtCost.Text ); // text field allows only 1 - 9
                        newItem.FCC = txtFCCNo.Text.Trim();
                        newItem.info = txtInfo.Text;
                        
                        // no idea how to use the selected item from comboboxes
                        // to insert into the new item
                        ........
                   }

    The user fills in textboxes to provide the above information. However, I have comboboxes filled with the tables Country, Make, Category, Status and Type so the user only has to select the corresponding (fixed) information from the combobox. I have ordered the comboboxes alphabetically for ease of use so I need to use cboCategory.SelectedItem.ToString(),  cboType.SelectedItem.ToString()  ...etc to pass to the new record. 

    I am totally lost so any guidelines will be most helpful. I will also have to work out update and delete!

    Thanks


    :-( Still trying to program
    Monday, October 24, 2011 5:32 PM

Answers

  • Okay yeah I forgot about that, that Linq can't handle having a reference to a control in it like a combobox.  In that case, you'll need to assign the result of each combobox to a string variable first and then use the variable in the expression:

    string CategoryName = cboCategory.SelectedItem.ToString();
    
    Category c = me.Categories.Where( p => p.categoryName == CategoryName).Single();
    

     


    Tom Overton
    • Proposed as answer by Alan_chenModerator Thursday, October 27, 2011 8:47 AM
    • Marked as answer by Jonsey Thursday, October 27, 2011 10:50 AM
    Tuesday, October 25, 2011 5:26 PM

All replies

  • Hi Jonsey,

    If I understand your code correctly, I think you will need get the entity associated with the combo box, and add it to the newItem instance before you insert it into the database.  Try something like this (only did code for 2 of the combo boxes):

    using ( MaterialEntities me = new MaterialEntities() )
                    {
                        item newItem = new item();
                        newItem.name = txtItemName.Text.Trim();
                        newItem.model = txtModel.Text.Trim();
                        newItem.serialNo = txtSerialNo.Text.Trim();
                        newItem.aiNumber = txtAirbusNo.Text.Trim();
                        newItem.cost = decimal.Parse( txtCost.Text ); 
                        newItem.FCC = txtFCCNo.Text.Trim();
                        newItem.info = txtInfo.Text;
    
                        Country country = me.Countries.Where(c => c.CountryName == cboCountry.SelectedItem.ToString()).Single();
                        
                         Category cat = me.Categories.Where(c => c.CategoryName == cboCategory.SelectedItem.ToString()).Single();
                        
                         newItem.Country = country;
                         newItem.Category = cat;
                         
                         me.items.Add(newItem);
                         me.SaveChanges();                     
                       
                   }
    

     


    Tom Overton
    • Edited by Tom_Overton Monday, October 24, 2011 6:00 PM
    Monday, October 24, 2011 5:59 PM
  • Thanks for your help Tom. Using what you have given me I get the following error:

    with 
    Category cat = me.Categories.Where(c => c.CategoryName = cboCategory.SelectedItem.ToString()).Single(); 
    error:
    Cannot convert lambda expression to delegate type 'System.Func<Material.Category,bool>' because some of the return types in the block are not
    implicitly convertible to the delegate return type

    I have tried messing about with what you gave me but I still cannot insert the record into the database.

    The reason I have used comboboxes and non nullable fields is so keep consistency within the database i.e. category "Laptop" can only be selected from the combobox for a laptop item and not the user typing Laptops or PC. Surely a lot of people do this too so for me a novice, although LINQ makes a lot of things simple it appears that what I am doing is a little tricky?

    Do you have any further ideas? 

    Many thanks.


    :-( Still trying to program
    Tuesday, October 25, 2011 3:33 PM
  • Jonsey,

    First of all make sure your Category identifier is the one that matches your entity.  I just used CategoryName as an example. 

    But the main problem is that you need to use double "=" for testing equality:

    Category cat = me.Categories.Where(c => c.CategoryName == cboCategory.SelectedItem.ToString()).Single(); 
    
    

     


    Tom Overton
    Tuesday, October 25, 2011 3:44 PM
  • Yes Tom, I see, my mistake, thanks. This is where I am though. (The identifier is categoryName as it happens, good guess :-))

                        item newItem = new item();
                        newItem.name = txtItemName.Text.Trim();
                        newItem.model = txtModel.Text.Trim();
                        newItem.serialNo = txtSerialNo.Text.Trim();
                        newItem.aiNumber = txtAirbusNo.Text.Trim();
                        newItem.cost = decimal.Parse( txtCost.Text.Trim() );
                        newItem.FCC = txtFCCNo.Text.Trim();
                        newItem.info = txtInfo.Text;                      
                        Category c = me.Categories.Where( p => p.categoryName == cboCategory.SelectedItem.ToString() ).Single();
                        Brand b = me.Brands.Where( p => p.brandName == cboBrand.SelectedItem.ToString() ).Single();
                        Origin o = me.Origins.Where( p => p.originCountry == cboCountry.SelectedItem.ToString() ).Single();
                        Status s = me.Status.Where( p => p.currentStatus == cboStatus.SelectedItem.ToString() ).Single();
                        Type t = me.Types.Where( p => p.typeName == cboType.SelectedItem.ToString() ).Single();
                        newItem.Category.categoryName = c.categoryName;
                        newItem.Brand.brandName = b.brandName;
                        newItem.Origin.originCountry = o.originCountry;
                        newItem.Status.currentStatus = s.currentStatus;
                        newItem.Type.typeName = t.typeName;
                        me.items.AddObject( newItem );
                        me.SaveChanges();
    

     and this is the error as soon as it hits the "Category c = me.Categories.Where( p => p.categoryName == cboCategory.SelectedItem.ToString() ).Single();"

    ERROR - LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

    I suppose that the server doesn't use the ToString method so I have tried replacing the combobox.SelectedItem.ToString() with a string variable but then get a cast error which I have been unable to solve using Cast<>.

    Thanks for your patience.


    :-( Still trying to program
    Tuesday, October 25, 2011 5:00 PM
  • Okay yeah I forgot about that, that Linq can't handle having a reference to a control in it like a combobox.  In that case, you'll need to assign the result of each combobox to a string variable first and then use the variable in the expression:

    string CategoryName = cboCategory.SelectedItem.ToString();
    
    Category c = me.Categories.Where( p => p.categoryName == CategoryName).Single();
    

     


    Tom Overton
    • Proposed as answer by Alan_chenModerator Thursday, October 27, 2011 8:47 AM
    • Marked as answer by Jonsey Thursday, October 27, 2011 10:50 AM
    Tuesday, October 25, 2011 5:26 PM
  • Done that Tom 

    string selCategory = cboCategory.SelectedItem.ToString();
    ........
    Category c = me.Categories.Where( p => p.categoryName == selCategory ).Single();
    ........
    newItem.Category.categoryName = c; // produces error 
    
    ERROR Cannot implicitly convert type 'Material.Category' to 'string'	
    
    IF I USE THIS
    newItem.Category.categoryName = c.categoryName;
    
    ERROR
    NullReferenceExeption was unhandled
    Object reference not set to an instance of an object.
    
    


    I suppose that the fact that newItem is of type Item, the relationship to the Category Entity (table) is through the FK which is an "int". It works if I simply use "newItem.CategoryID = 1" but "newItem.Category.categoryName = selCategory" fails. Am I missing a join do you think?

    If all else fails, I suppose I will have to find the categoryID from the selected categoryName in the combobox and use that. Unfortunately I cannot use the index of the combobox as I have sorted the categories alphabetically.

    Thanks again.


    :-( Still trying to program
    Wednesday, October 26, 2011 10:10 AM
  • Hi,

    Thanks @Tom!

    >>newItem.Category.categoryName = c; // produces error
    newItem.Category= c; ---c is a instance of  Category type.

    If there are collection properties for  newItem, you should  new a list<> instance then add item into the list.

    Have a nice day.

     

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, October 27, 2011 8:54 AM
    Moderator
  • Thanks Alan, 

    Following Tom's very useful help and me messing about for a few hours I have done this:

    string selCategory = cboCategory.SelectedItem.ToString();
    .......

    This gets me the selected category name

    Then

    ......
    var catID = me.Categories.Where( p => p.categoryName == selCategory ).Single();
    ......

    This gets me the entity back so I can get the categoryID

    Then 

    newItem.categoryID = catID.categoryID;


    It works but I am not sure that this is the most efficient or best way of doing it.

    Many thanks to both of you for your input. At least now I can go to the next stage.


    :-( Still trying to program
    Thursday, October 27, 2011 9:44 AM