Linq changed variable name-prevents Insert

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

Answer 1

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.

Answer 2

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

Answer 3

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?

Answer 4

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

Answer 5

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...