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