April 28, 2011 at 9:57 am
ALTER trigger [dbo].[InsteadUpdateSocial] on [dbo].[viewSocialAttendance]
instead of insert
as
begin
update tblsocialattendance
set starttime = i.starttime,
endtime = i.endtime,
typeofsocial = i.typeofsocial
from tblsocialattendance s, inserted i
where s.socialattendanceid = i.socialattendanceid
end
this is the instead trigger that I created on the view. When I change data in my datagridview in vb.net 2008 the change is not reflected in the underlying table. the code on the dataviewgrid that is executed when a change to data occurs is:
Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
If e.ColumnIndex = 5 Then
Dim changedstarttime = (From a In db.viewSocialAttendances _
Where a.SocialAttendanceID = Me.DataGridView1.CurrentRow.Cells(0).Value.ToString _
Select a).Single
changedstarttime.StartTime = DataGridView1.CurrentRow.Cells(5).Value
db.SubmitChanges()
ElseIf e.ColumnIndex = 6 Then
Dim changedendtime = (From a In db.viewSocialAttendances _
Where a.SocialAttendanceID = Me.DataGridView1.CurrentRow.Cells(0).Value.ToString _
Select a).Single
changedendtime.EndTime = DataGridView1.CurrentRow.Cells(6).Value
db.SubmitChanges()
ElseIf e.ColumnIndex = 7 Then
Dim changedtypeofsocial = (From a In db.viewSocialAttendances _
Where a.SocialAttendanceID = Me.DataGridView1.CurrentRow.Cells(0).Value.ToString _
Select a).Single
changedtypeofsocial.TypeOfSocial = DataGridView1.CurrentRow.Cells(7).Value.ToString
db.SubmitChanges()
End If
End Sub
so what am I missing. I can run update query against view and it updates the underlying table. So Is it the vb component that is causing the issue?Thank you
April 28, 2011 at 1:13 pm
I am really confused by a lot of things here. First of all you can't put a trigger on a view. You can't insert or update a view. You can put a trigger and perform other dml against tables. Secondly, your post is largely nothing to do with sql server but rather vb.net. That being said, is your table data getting updated and the grid on screen is not current? I suspect that is what is happening. If so, simply rebinding the grid should take care of it.
DataGridView1.DataBind
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 1:23 pm
by taking the risk of being presumptuous I was following this article from microsoft.
http://msdn.microsoft.com/en-us/library/ms175521(v=SQL.90).aspx
April 28, 2011 at 1:29 pm
LOL. :blush: I was not aware that you could do that.
Back to your situation, did it in fact update the underlying data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 1:38 pm
when i run an update against the view in sql server the field is updated.
"update viewsocialattendance
set typeofsocial = 'ran to the mall'
where membernumber = '1649' and dateofsocial = '2/15/11'
the field in tblsocialattendance is updated.
When I run a debug in my vb and change the field data and run throught the code and then go back to sql server and run a select against the actual table tblsocialattendance ran to the mall does not exist in the field typeofsocial. I have a post on a linq forum but have not gotton a response yet. Thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply