How to use "ObjectDataSource control" with customized Insert/Update/Delete operation?

Question

User172691333 posted

Well,

It is almost a month that I’m struggling to correctly access and update my data from my .net application but I am not successful and very upset.

I have followed many different tutorials and red several articles, It looked fairly easy, but apparently something is wrong with me, am I getting old?

 

Here the scenario and the steps that I took (failed of course):

I have several tables in my MSSQL database and each table has a Shadow table which is used to log the Insert/Update/Delete operation audits.

When insert a record in a table, a record will be created in the shadow table containing the original table information plus some other fields like “AuditAction”, “UserID”, AuditDateTime”, So I have to customize the insert operation to do so.

Step1:

I have added a Linq To SQL class “LOG.dbml” to my application.

Then I added the following tables to it using the ORM designer (VS2008).

Logbook, Logbook_shd, Tests and Tests_shd

The Test table has a foreign key to Logbook.LogbookID

I also linked each table to its shadow within the designer using the table unique ID field.

Step2:

<div>

I added a C#Class “BAL” to my application. And created the following function:

 public int insertLogbook()
        {
            using (var Context = new LOGDataContext())
            {
                String UserID= System.Environment.UserName;
                DateTime TransDateTime = DateTime.Now;
 
                // Add a new logbook record
                Logbook l = new Logbook();               
                l.CreatedBy = UserID;
                l.CreatedDate = TransDateTime;
                l.Stat = "New";
                l.Ver = 0;
                Context.Logbooks.InsertOnSubmit(l);

                //Add a new Shadow record
                Logbooks_shd ls = new Logbooks_shd();
                ls.LogBookID = l.LogBookID;
                ls.CreatedBy = l.CreatedBy;
                ls.CreatedDate = l.CreatedDate;
                ls.Stat = l.Stat;
                ls.Ver = l.Ver;
                ls.AuditAction = "Create";
                ls.AuditDateTime = TransDateTime;
                ls.AuditUser = UserID;
                ls.AuditApp = "APS Electronic Log";
                Context.Logbooks_shds.InsertOnSubmit(ls);
                l.Logbooks_shds.Add(ls);
                Context.SubmitChanges();
                return l.LogBookID;
            }
        }

</div>

Step3:

I added a webform to my application and throw the following controls into it.

Button1: To insert a new record to the logbook table and perform the shadow operation on Click.

Label1: To display the LogbookID of the inserted record.

GridView1: To display the tests related to the LogbookID (Lable1) and perform insert and update operations on Test table.

<div>

Then I added the following code to the click event handler of Button1.

</div>

I ran the form on the browser (IE) and Clicking on Button1, everything goes well and the  insert operation performed correctly and the new logbookID shown by Label1. Yayyyyy

But wait a minute here is the problem coming…..

<div>

I created two other functions within “BAL” class to get tests by LogbookID, and also Insert Test records:

public List<Test> getTestsByLogbookIDs(string LogBookID)
        {
            using (var context = new APSELOGDataContext())
            {
                return (from T in context.Tests
                        where T.LogbookID == int.Parse(LogBookID)
                        select T).ToList();
            }
        }

       public int InsertTest(Test pt )
        {
            using (var Context = new LOGDataContext())
            {
                String UserID = System.Environment.UserName;
                DateTime TransDateTime = DateTime.Now;
                // Add a new Test record
                Test t = new Test();
                t.CreatedBy = UserID;
                t.CreatedDate = TransDateTime;
                t.Stat = "New";
                t.Ver = 0;
                t.LogbookID = pt.LogbookID;
                t.TestName = pt.TestName;
                Context.Tests.InsertOnSubmit(t);

                //Add a new Test hadow record
                Tests_shd ts = new Tests_shd();
                ts.TestID = t.ID;
                ts.CreatedBy = t.CreatedBy;
                ts.CreatedDate = t.CreatedDate;
                ts.Stat = t.Stat;
                ts.Ver = t.Ver;
                ts.LogbookID = t.LogbookID;
                ts.TestName = t.TestName;
                ts.AuditAction = "Create";
                ts.AuditDateTime = TransDateTime;
                ts.AuditUser = UserID;
                ts.AuditApp = "APS Electronic Log";
                Context.Tests_shds.InsertOnSubmit(ts);
                t.Tests_shds.Add(ts);
                Context.SubmitChanges();
                return t.ID;
            }
        }

Then I created an ObjectDataSource, “ObjectDataSource1” using the smart tag of the gridview1 and it has thrown the following aspx code in my form.

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
            onselecting="ObjectDataSource1_Selecting" SelectMethod="getTestsByLogbookIDs" 
            TypeName="ElogObjectBoundingTest.Bal" 
            DataObjectTypeName="ElogObjectBoundingTest.Test" InsertMethod="InsertTest">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" DefaultValue="Null" Name="LogBookID" 
                    PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>

When I ran my form on the browser again I got "Uh-Oh Something went wrong! Error Code:500"

Is there anybody who can help me out of my misery?????