Add a row to a DataTable that is product of SQL JOIN's

I have a strongly typed DataTable in my app which is generated with a couple of SQL joins. I use it to display data in a human readable way. Here's the SQL that makes it:

SELECT     tblEvents.EventName, vwAllMembers.LASTNAME AS LastName, vwAllMembers.FIRSTNAME AS FirstName, tblEventOccurences.EventOccurenceDate, 
                  tblAttendance.AttendanceID, tblAttendanceTypes.Description AS AttendanceType, tblAttendanceTypes.Abbreviation AS AttendanceTypeAbbrv, 
                  tblAttendance.EventOccurenceID, tblAttendance.StudentID, tblAttendance.AttendanceTypeID
FROM         tblAttendance INNER JOIN
                  tblEventOccurences ON tblAttendance.EventOccurenceID = tblEventOccurences.EventOccurenceID INNER JOIN
                  tblEvents ON tblEventOccurences.EventID = tblEvents.EventID INNER JOIN
                  vwAllMembers ON vwAllMembers.Id = tblAttendance.StudentID INNER JOIN
                  tblAttendanceTypes ON tblAttendance.AttendanceTypeID = tblAttendanceTypes.AttendanceTypeID

WHERE tblAttendance.EventOccurenceID=@EventOccurenceID

I want to add a new row to that DataTable in VB. If I were inserting directly to the DB in SQL, I would just add the EventOccurenceID, StudentID, and AttendanceTypeID fields to the tblAttendance table. When I try to add only those values in VB, I get an error from my DataTable that EvenName column cannot be null (and I assume every other column as well).

Here's my VB:

            Dim AttendanceTable As DAL.Attendance.AttendanceDataTable = AttendanceAdapter.ByEventOccurenceID(EventOccurenceID)
            Dim NewAttendanceRecord As AttendanceRow = AttendanceTable.NewAttendanceRow()
            NewAttendanceRecord.EventOccurenceID = EventOccurenceID
            NewAttendanceRecord.StudentID = StudentID
            NewAttendanceRecord.AttendanceTypeID = AttendanceTypeID

            AttendanceTable.Rows.Add(NewAttendanceRecord)

Answers


When you join two tables like this, its like a VIEW, not a real table. You cant add rows to a view.

Create another adapter that only returns the table you want to modify and use that to add the row.


Need Your Help

HTML Form - Fill input with value if nothing is entered

php jquery html forms

I'm creating a basic subscribe form for a system that requires both email address and last name (SugarCRM in case anyone is interested).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.