locked
Linq changed variable name-prevents Insert RRS feed

  • Question

  • User646364117 posted

    I have a table that has a foreign key category.id

    When I dragged the table onto the dbml diagram the field name has been changed to category_id

    I have also dragged an INSERT stored procedure onto the dbml diagram

    intellisense is happy with category_id in the block below

    Using dbContext As DataClassesDataContext = New DataClassesDataContext()
                    Dim newTemplate As template = New template With {
                        .name = txtNewTemp.Text,
                        .category_id = ddlCategory.SelectedValue,
                        .text = "blank for now"
                    }
                    dbContext.templates.InsertOnSubmit(newTemplate)
                    dbContext.SubmitChanges()
                End Using

    But in execution I get an error that 

    Cannot insert the value NULL into column 'category.id

    Please advise



    Monday, January 20, 2020 2:13 PM

All replies

  • User475983607 posted

    Are you sure the error has to do with the dot?  If you feel this way then why didn't you change the column name?  I believe there is a bug in your code and you need to set a break point and debug.

    sg48asp

    I have also dragged an INSERT stored procedure onto the dbml diagram

    The function import process creates a method that matches the stored procedure not a complex type.  Did you write InsertOnSubmit?  If so, can you share the code.  Perhaps run the code through the debugger?

    dbContext.templates.InsertOnSubmit(newTemplate)

    What is the value of ddlCategory.SelectedValue?  You can find the value using the Visual Studio debugger.

    Monday, January 20, 2020 2:47 PM
  • User646364117 posted

    The value of ddlCategory.SelectedValue = 42 which is correct.

    In the table the item is 

    I am running through the debugger.

    On submitchanges I get error: Cannot insert the value NULL into column 'category.id', table 'DB_A54278_steve48.dbo.template'

    Here is the code:

            If txtNewTemp.Text = "" Then
                lblNoTempWarning.Visible = True
                Exit Sub
            Else
                lblNoTempWarning.Visible = False
    
                Using dbContext As DataClassesDataContext = New DataClassesDataContext()
                    Dim newTemplate As template = New template With {
                        .name = txtNewTemp.Text,
                        .category_id = ddlCategory.SelectedValue,
                        .text = "blank for now"
                    }
                    dbContext.templates.InsertOnSubmit(newTemplate)
                    dbContext.SubmitChanges()
                End Using
                GetData()
                txtNewTemp.Text = ""
            End If

    The stored procedure I am using is:

    CREATE procedure [dbo].[InsertTemplate2]
    @name nvarchar(50),
    @text nvarchar(max),
    @categoryID int
    as
    Begin
         Insert into Template(name,text,[category.id])
         values (@name,@text,@categoryID)
    End

    Monday, January 20, 2020 3:19 PM
  • User475983607 posted

    You did not answer all the questions above.  Share this code...

    dbContext.templates.InsertOnSubmit(newTemplate)

    ... or use the visual studio debugger to check your code.  In my experience the function import does not use a generate a complex type input.  Please debug your code.

    Again, if you feel the column name is to blame, why don't you fix the column name?

    Monday, January 20, 2020 3:59 PM
  • User-17257777 posted

    Hi sg48asp,

    When I dragged the table onto the dbml diagram the field name has been changed to category_id

    C# does not allow you to use periods in member names. When you insert category_id to database, it will not be regonized as category.id. So, as mgebhard suggested, you can change the column name.

    Best Regards,

    Jiadong Meng

    Tuesday, January 21, 2020 7:23 AM
  • User753101303 posted

    Hi,

    You are using category_id on the C# side, categoryId in your sp and [category.id] in your table. Have you done something to make sure all this maps correctly to the same information. Could it be that using a dot inside the column name is not supported ?

    IMO be consistent and use a single name all along such as CategoryId. Also Linq to Sql is outdated...

    Tuesday, January 21, 2020 7:52 AM