1

Created two tables: Members and Contracts.

Contracts have a foreign key, the ID of Members.

I also have two classes: Member and Contract.

My main static class Club has an ArrayList of Contract, and each Contract has a field Member.

Now, in my Club class I wanna populate in my ArrayList every Contract in the DB with the correct Member field.

For some reason, this does not work: (This is a method Club class)

public static void Populate()
        {
            string connStr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Contribution;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
            SqlConnection con = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Contracts", con);
            SqlDataReader dr;
            try
            {
                con.Open();
                dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    Member newMember = null;
                    int memberID = dr.GetInt32(dr.GetOrdinal("memberID"));
                    MessageBox.Show(memberID.ToString());
                    SqlCommand cmd2 = new SqlCommand("SELECT * FROM Members WHERE ID = @memberID", con);
                    cmd2.Parameters.AddWithValue("@memberID", memberID);
                    cmd2.ExecuteNonQuery();
                    SqlDataReader dr2;
                    dr2 = cmd2.ExecuteReader();
                    while (dr2.Read())
                    {
                        string name = dr2.GetString(dr2.GetOrdinal("name"));
                        DateTime birthDate = DateTime.ParseExact(dr2.GetString(dr2.GetOrdinal("birthDate")), "dd/MM/yyyy", null);
                        DateTime joinDate = DateTime.ParseExact(dr2.GetString(dr.GetOrdinal("joinDate")), "dd/MM/yyyy", null);
                        bool isPlaying = (dr2.GetInt32(dr2.GetOrdinal("isPlaying")) == 1 ? true : false);
                        newMember = new Member(name, birthDate, joinDate, isPlaying);
                        MessageBox.Show(name);
                    }
                    dr2.Close();
                    Club.AddContract(new Contract(newMember));
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

When testing, I do not get the expected results.

EDIT:

I get no results. I have methods that calculate the total Contribution and/or get the youngest member etc. All of those fields are either empty or null.

EDIT 2:

Switched up my code a bit: Used parameters, fixed some small mistakes. Still not working.

ASh
  • 34,632
  • 9
  • 60
  • 82
DaviHlav
  • 17
  • 3
  • `SqlCommand cmd2` followed by `dr2 = cmd.ExecuteReader();` followed by `dr.GetString`. This looks messy. And please start to use [parameters](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements), for safety, ease and performance – HoneyBadger Jun 10 '22 at 08:04
  • 1
    Instead of expected results, what did you get? Exceptions? – Xiang Wei Huang Jun 10 '22 at 08:06
  • Also, should `dr2` run `cmd`, or should it run `cmd2`? Bit confusing here. Btw, I recommend using frameworks like EF, so you don't need to handle SQLs like this anymore. – Xiang Wei Huang Jun 10 '22 at 08:11
  • @XiangWeiHuang No Exceptions. Also, it was a mistake, changed it to `cmd2` but to no avail. – DaviHlav Jun 10 '22 at 08:12
  • 1
    Okay, if there's no exceptions, what exactly happened when you tested? Also, I think the problem might lies in `AddContract()`, so if it's ok you'd probably want to check it again or edit it in for people to check. And what happens if `new Contract(null)`? As `newMember` here can be null. – Xiang Wei Huang Jun 10 '22 at 08:20
  • @XiangWeiHuang In other parts of the code `AddContract()` works fine, I create a new `Member` based on what the user filled in the Windows Form, assign it to a new `Contract` and then use the method `AddContract()` and it works just fine. So, I think I'm doing something wrong when trying to get to the other table using the `foreign key` in the first table. – DaviHlav Jun 10 '22 at 08:22
  • 1
    "I do not get the expected results"; What results did you expect? And what results did you get? Please share some sample data so we don't have to guess. – HoneyBadger Jun 10 '22 at 08:25
  • @XiangWeiHuang Actually, I added the line `MessageBox.Show(memberID.ToString());` in the first `while` loop. It prints the ID number 4 multiple times. So it does not go into the second `while` loop at all. Also, it's weird it prints the same ID multiple times. Each ID is unique.. – DaviHlav Jun 10 '22 at 08:26
  • 1
    `cmd2.ExecuteNonQuery();` Why do you ExecuteNonQuery? – HoneyBadger Jun 10 '22 at 08:45
  • @HoneyBadger I thought that's what you gotta do when using `Parameters`. – DaviHlav Jun 10 '22 at 09:09

0 Answers0