Question Unable to Cast Object Type in 3-Layer Architecture

[andrewmanuja]

Hi All,
I am trying to implement the update data record(s) from a data grid view to the database table.
I got three Layers implemented and need to update changes to the data grid view data using a data table into my database table.
In my Data Access Layer, the respective code looks like below;

 public int Update(string textFirstName, string textLastName, int textAge)
        {
            var returnValue = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))

            {
                using (var dCmd = new SqlCommand(@"UPDATE tbl_Persons SET LastName = @aLastName, Age = @aAge
                                                            WHERE FirstName = @aFirstName", conn))     
                
                using (var adapter = new SqlDataAdapter { UpdateCommand = dCmd })

                {
                    conn.Open();
                    SqlParameter[] prms = new SqlParameter[3];
                    prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);
                    prms[0].Value = textFirstName;
                    prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);
                    prms[1].Value = textLastName;
                    prms[2] = new SqlParameter("@Age", SqlDbType.Int);
                    prms[2].Value = textAge;

                    dCmd.Parameters.AddRange(prms);

                    returnValue = dCmd.ExecuteNonQuery();
                    dCmd.Parameters.Clear();

                    adapter.Update(table);
                }

                foreach (DataRow row in table.Rows)
                {
                    if (row["Select Item"] as bool? == true)
                    {
                        table.AcceptChanges();
                        row.SetAdded();
                    }
                }
            }
            return returnValue;
        }

My Business layer logic looks like below;

public int Update(string textFirstName, string textLastName, int textAge)
        {           
            return new PersonDAL().Update(textFirstName, textLastName, textAge);
        }

And finally, my presentation layer respective data update button event looks like below;

var result = 0;
            try
            {               
                result = new PersonBAL().Update(textFirstName.ToString(), textLastName.ToString(), Convert.ToInt32(textAge));
              
                if (result > 0)
                {
                    MessageBox.Show("Record updated successfully !");
                }
                else
                {
                    MessageBox.Show("No record Identified.");
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show("Error occured. " + ee.Message);
            }

I am successfully loading the data into the dataGridView in the form load event.

However, when running the program, I am getting the error, "Unable to cast object of type 'System.Windows.Forms.DataGridViewTextBoxColumn' to type 'System.Iconvertible'.

Appreciate your feedback to correct the issue please.

Kind regards,

Andrew

 

[jmcilhinney]

What line does the exception get thrown on? When posting code snippets, the code editor lets you specify one or more line numbers to highlight, which makes it much easier to identify the location of problem lines in your code.

 

[jmcilhinney]

Based on the error message, I would assume that the issue is in Convert.ToInt32(textAge). That seems to suggest that textAge is a column in the grid, rather than a value from a cell in a row in the grid. Presumably textFirstName and textLastName are in the same boat. If you want to do it like that then you need to loop through the rows of the grid, get the relevant data for each row and save that data. You can't just get the columns and expect the data to be magically extracted, especially given that the columns don't actually contain any data. Just like in database tables, the columns describe the data (type, format, etc) while it's the rows that actually contain the data. More specifically in this case, the rows contain the cells and the cells contain the data.

 

[jmcilhinney]

On a different note, doesn't it seem a bit strange that, based on your SQL, you are allowing the LastName to be changed but not the FirstName while also using the FirstName as a unique identifier?

 

[NoUserHere]

I am successfully loading the data into the dataGridView in the form load event.

That's a terrible idea, and if you need to ask questions why; you're already in trouble. Aside from that, your SQL looks funky and it looks like you have a bad database design. Why do I say that?

 public int Update(string textFirstName, string textLastName, int textAge)

What is the point in passing in a first name and last name if you are only going to update the last name and not both names, and only do so if the first name meets the condition of your where clause?

using (var dCmd = new SqlCommand(@"UPDATE tbl_Persons SET LastName = @aLastName, Age = @aAge
                                                            WHERE FirstName = @aFirstName", conn))

What if there are many people with the same first name? You should give this a little more thought.

I hope your plan isn't to then update the first name with the last name. It would be better if you would explain what you're trying to do here. Also, why is your public int not public void?

You're performing an update statement, so you shouldn't be returning anything. Least not : returnValue = dCmd.ExecuteNonQuery();. And where exactly are you handling errors when the statement fails to update?

Looks like a bad model to me.

 

[andrewmanuja]

Hi Sheepings,

I understand that it is not a good database design. My objective is not to make a good database design at this stage instead, I want to understand how the CRUD operations works in a 3-tier architecture and if I understand the programming concepts properly, I want to migrate an application I have developed using a single tier to the 3-tier architecture.

Thus, the corrected "btn_Update" event code looks like below ( I have added a new field named "PersonID" to my database.;

  var result = 0;
            try
            {               
                int age = 0;
                int.TryParse(txtAge.Text, out age);

                int personID = 0;
                int.TryParse(txtPersonID.Text, out personID);

                result = new PersonBAL().Insert(personID, txtFirstName.Text.Trim(), txtLastName.Text.Trim(), age);
                if (result > 0)
                {
                    MessageBox.Show("Record inserted successfully !");
                }
                else
                {
                    MessageBox.Show("No record afftected.");
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show("Error occured. " + ee.Message);
            }

The corrected Business Logic Layer code to pass the parameter values to the DataBase layer looks like below;

public int Update(int personID, string textFirstName, string textLastName, int age)
        {           
            return new PersonDAL().Update(personID, textFirstName, textLastName, age);
        }

I didn't use void because I am collecting some parameter values from the presentation layer and passing them to the database layer. Am I wrong here?

Finally, the DataBase layer relevant update code,

 public int Update(int personID, string textFirstName, string textLastName, int age)
        {
            var returnValue = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))

            {
                using (var dCmd = new SqlCommand(@"UPDATE Persons SET FirstName = @aFirstName, LastName = @aLastName, Age = @aAge
                                                            WHERE PersonID = @aPersonID", conn))     
                
                using (var adapter = new SqlDataAdapter { UpdateCommand = dCmd })

                {
                    conn.Open();
                    SqlParameter[] prms = new SqlParameter[4];
                    prms[0] = new SqlParameter("@aPersonID", SqlDbType.Int);
                    prms[0].Value = personID;
                    prms[1] = new SqlParameter("@aFirstName", SqlDbType.VarChar, 50);
                    prms[1].Value = textFirstName;
                    prms[2] = new SqlParameter("@aLastName", SqlDbType.VarChar, 50);
                    prms[2].Value = textLastName;
                    prms[3] = new SqlParameter("@aAge", SqlDbType.Int);
                    prms[3].Value = age;

                    dCmd.Parameters.AddRange(prms);

                    returnValue = dCmd.ExecuteNonQuery();
                    dCmd.Parameters.Clear();

                    adapter.Update(table);
                }

                foreach (DataRow row in table.Rows)
                {
                    if (row["Select Item"] as bool? == true)
                    {
                        table.AcceptChanges();
                        row.SetAdded();
                    }
                }
            }
            return returnValue;
        }

However, the issue now is, the code in the presentation layer does not execute as intended.

result = new PersonBAL().Insert(personID, txtFirstName.Text.Trim(), txtLastName.Text.Trim(), age);

Appreciate your feedback please.

Kind regards,

Andrew

 

[jmcilhinney]

I understand that it is not a good database design. My objective is not to make a good database design at this stage instead, I want to understand how the CRUD operations works in a 3-tier architecture

That's bad priorities. If you start out with a bad database design then you may have to do unnecessarily funky things in your application code to deal with it. You should build lower layers first and don't move up until the layer you're working on is sound. Design a good database first, then design a good DAL to work with it, then build a good BLL to access the data, then build a PL to use that business logic. When you become more experienced you should be able to build them all at the same time but, for now, you're just going to end up compounding errors.