Table not getting updated while using LinQ

Question

Hello,

I am trying to update a table using LinQ. Though records are getting inserted, for some reason they are not getting updated.

what can be possible problem

Dim db as new empDataContext
Dim emptable as new employee

if update then
emptable=GetEmp(txtempID.Text)
emptable.Name="Test"
emptable.Age=11
emptable.City="NYC"
else
emptable.Name="Test"
emptable.Age=11
emptable.City="NYC"
emtable.deptID=10
db.employee.InsertOnSubmit(emptable)
end if
db.SubmitChanges()

Answer 1

Judging just from what I can see here, I'm guessing your GetEmp method is using a different data context to retreive the data than the one you're using to save it back to the DB.

When using LINQ to SQL, the context is what tracks the changes to the tables. If you're not careful and mix Contexts by accident, you can get strange behaviors like this.

You can test by chaging:

emptable=GetEmp(txtempID.Text)

to

// Returns the first matching employee with the id
emptable = (from e in db.Employees
           where e.id == txtempid.Text).FirstOrDefault()

If you find that the context is the issue, just modify your GetEmp method to accept the context as a parameter rather than creating a new one itself.

Answer 2

What does GetEmp do? In particular, as presented it appears that it does not have a reference to the empDataContext named db. DataContexts are examples of identity maps and as such they track items that have been loaded from a persistence mechanism. If you are using a different DataContext in GetEmp then the DataContext db does not know about the instance of employee with SomeID equal to the value represented by txtempID.Text.

So either pass a reference to db into GetEmp or change your code to the following:

emptable = db.Single(Function(e as employee) e.SomeID=Int32.Parse(txtempID.Text))

then your update should work.

Answer 3

If I had to guess, I would say that the GetEmp() call is not using the same database context object. Therefore, Linq-To-SQL doesn't think any changes are occuring in the "db" database context.

Randy