I have a problem writing multiple records to a SQL DB via Linq to SQL.
I have a list object that stores all the pages a user visits into a session variable. At the end of the session I write the contents out to sql. The sub is called from global.asax:
tracker.AddPage(Request.Url.ToString())
This calls the sub Addpage:
Public Sub AddPage(ByVal pageName As String)
'create a new page tracker item
Dim tp As New TrackerPages
tp.pageurl = pageName
tp.pagedate = Now
_tpages.Add(tp)
End Sub
OK all that works fine.
I can see all the object pages and the pageurl and pagedate values during debug.
The problem is when I try writing this at the end of the session. Only the last page record is ever written.
Here is the code:
If Not IsNothing(tracker.TPages) Then
Try
Dim db As New trackerDCDataContext ' get reference to DB
Dim trackerPage As New trackerPage_tbl ' get reference to DB table
Dim tpages As List(Of TrackerPages) = tracker.TPages
Dim tp As TrackerPages
For Each tp In tpages
'trackerPage.trackerPageGuid = trackerPageID.ToString
trackerPage.trackerPageURL = tp.
'trackerPage.trackerPageDate = tp.pagedate
'trackerPage.trackerPageID = pageid
db.trackerPage_tbls.InsertOnSubmit(trackerPage)
Next
db.SubmitChanges()
Catch ex As Exception
End Try
End If
If I look at the table in sql only the last record is written.
The table in question (trackerPage_tbl) has an ID field (trackerPageID) which is the primary key and has identity enabled.
I am rather lost on this so would appreciate some wisdom. Ta
Please move the db.SubmitChanges() into the For Each statement——
For Each tp In tpages
trackerPage.trackerPageGuid = trackerPageID.ToString
trackerPage.trackerPageURL = tp.
trackerPage.trackerPageDate = tp.pagedate
trackerPage.trackerPageID = pageid
db.trackerPage_tbls.InsertOnSubmit(trackerPage)
db.SubmitChanges()
Next
Yes I tried that days ago and it made no difference.
I have a testbed of pages, Deafault, Contact, Services etc. If I run it and click on all three pages and examine the list I can see 3 instances. However when I write only the first (initial) page is written.
I have a testbed of pages, Deafault, Contact, Services etc. If I run it and click on all three pages and examine the list I can see 3 instances. However when I write only the first (initial) page is written.
If I look at the autos running the code I have noticed that the Identity field (not touched in the code) is being assigned the value &H0. That might explain why I am getting only one record inserted.
The session variable stores the URL of each page a user visits.
Therefore to test I need to simulate the actions of a visitor to visit separate pages so that the session variable (tracker) is updated and incremented with page urls.
I am confirming that the session object does indeed have this data stored in memory.
I am establishing that the data is indeed present and available to be written.
Unfortunatley when I apply the code to write to SQL only the first record in the session variable is infact written to the DB table.
Darrenst
Member
42 Points
86 Posts
Write records from List(of T) to Linq Problem
Aug 15, 2012 04:12 PM|LINK
I have a problem writing multiple records to a SQL DB via Linq to SQL.
I have a list object that stores all the pages a user visits into a session variable. At the end of the session I write the contents out to sql. The sub is called from global.asax:
This calls the sub Addpage:
Public Sub AddPage(ByVal pageName As String) 'create a new page tracker item Dim tp As New TrackerPages tp.pageurl = pageName tp.pagedate = Now _tpages.Add(tp) End SubOK all that works fine.
I can see all the object pages and the pageurl and pagedate values during debug.
The problem is when I try writing this at the end of the session. Only the last page record is ever written.
Here is the code:
If Not IsNothing(tracker.TPages) Then Try Dim db As New trackerDCDataContext ' get reference to DB Dim trackerPage As New trackerPage_tbl ' get reference to DB table Dim tpages As List(Of TrackerPages) = tracker.TPages Dim tp As TrackerPages For Each tp In tpages 'trackerPage.trackerPageGuid = trackerPageID.ToString trackerPage.trackerPageURL = tp. 'trackerPage.trackerPageDate = tp.pagedate 'trackerPage.trackerPageID = pageid db.trackerPage_tbls.InsertOnSubmit(trackerPage) Next db.SubmitChanges() Catch ex As Exception End Try End IfIf I look at the table in sql only the last record is written.
The table in question (trackerPage_tbl) has an ID field (trackerPageID) which is the primary key and has identity enabled.
I am rather lost on this so would appreciate some wisdom. Ta
Darren
http://www.darrenstreet.co.uk
http://www.nine9design.co.uk
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 01:27 AM|LINK
Hi,
Please move the db.SubmitChanges() into the For Each statement——
For Each tp In tpages trackerPage.trackerPageGuid = trackerPageID.ToString trackerPage.trackerPageURL = tp. trackerPage.trackerPageDate = tp.pagedate trackerPage.trackerPageID = pageid db.trackerPage_tbls.InsertOnSubmit(trackerPage) db.SubmitChanges() NextDarrenst
Member
42 Points
86 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 08:31 AM|LINK
Yes I tried that days ago and it made no difference.
I have a testbed of pages, Deafault, Contact, Services etc. If I run it and click on all three pages and examine the list I can see 3 instances. However when I write only the first (initial) page is written.
The trackPageID increments as expected.
Odd!
Darren
http://www.darrenstreet.co.uk
http://www.nine9design.co.uk
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 08:39 AM|LINK
What does this mean?
And where have you written?
Darrenst
Member
42 Points
86 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 08:54 AM|LINK
If I look at the autos running the code I have noticed that the Identity field (not touched in the code) is being assigned the value &H0. That might explain why I am getting only one record inserted.
Darren
http://www.darrenstreet.co.uk
http://www.nine9design.co.uk
Darrenst
Member
42 Points
86 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 09:12 AM|LINK
The session variable stores the URL of each page a user visits.
Therefore to test I need to simulate the actions of a visitor to visit separate pages so that the session variable (tracker) is updated and incremented with page urls.
I am confirming that the session object does indeed have this data stored in memory.
I am establishing that the data is indeed present and available to be written.
Unfortunatley when I apply the code to write to SQL only the first record in the session variable is infact written to the DB table.
Darren
http://www.darrenstreet.co.uk
http://www.nine9design.co.uk
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 09:36 AM|LINK
Hi again
To be honest your codes look right……Maybe you can use the traditional ADO.NET first to have a try with.
Reguards!
Darrenst
Member
42 Points
86 Posts
Re: Write records from List(of T) to Linq Problem
Aug 17, 2012 10:05 AM|LINK
I am going to try the SP method first then trad ADO.
I suspect its at the DB that's the issue.
Thanks for looking.
Darren
http://www.darrenstreet.co.uk
http://www.nine9design.co.uk
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Write records from List(of T) to Linq Problem
Aug 18, 2012 12:48 AM|LINK
Welcome back to chat with us:D
Darrenst
Member
42 Points
86 Posts
Re: Write records from List(of T) to Linq Problem
Aug 20, 2012 01:43 PM|LINK
I figured it out and its a DOH moment I am afraid.
I needed to move the new object declaration within the for each loop.
Simples when you think it through.
Darren
http://www.darrenstreet.co.uk
http://www.nine9design.co.uk