CODEWITHSUNDEEP
c#
Kevin Schultz
Kevin Schultz

Reputation: 884

List is empty at the return statement MVC

I have a form where the user searched for a customer's account by name, ssn, old account number, etc. When I am populating the list in the while loop, the values are present, however when it exits the loop, in the return statement the count is 0. Any idea why I am loosing the values?

Function creating the list:

public static List<Account> GetAccountNumbersFromCustomerSSN(string CustomerSSN)
        {

            List<Account> accts = new List<Account>();

            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TA_connectionstring"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGet_Account_Numbers_From_SSN", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();

                    SqlParameter P1 = new SqlParameter("@CUSTOMER_SSN", DbType.String);
                    P1 = new SqlParameter("@CUSTOMER_SSN", DbType.String);
                    P1.Value = CustomerSSN;
                    cmd.Parameters.Add(P1);

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Account acct = new Account
                        {
                            OriginalAcctNumber = dr["TABOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
                            FTBOATAcctNumber = dr["FTBOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
                            SSN = CustomerSSN.Substring(CustomerSSN.Length -4).PadLeft(CustomerSSN.Length, '*'),
                            CustomerName = dr["CUST_NAME"].ToString(),
                            ProductType = dr["TRGT_PROD"].ToString()
                        };
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(ex, "GetCustomerSSN()");

            }
            return accts;
        }

Upvotes: 0

Views: 40

Answers (3)

kemiller2002
kemiller2002

Reputation: 115538

All depending on how you want to do it you can also return IEnumerable by:

public static IEnumerable<Account> GetAccountNumbersFromCustomerSSN
 (string CustomerSSN)
        {

            List<Account> accts = new List<Account>();

            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings
                 ["TA_connectionstring"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGet_Account_Numbers_From_SSN", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();

                    SqlParameter P1 = new SqlParameter
                       ("@CUSTOMER_SSN", DbType.String);
                    P1 = new SqlParameter
                       ("@CUSTOMER_SSN", DbType.String);
                    P1.Value = CustomerSSN;
                    cmd.Parameters.Add(P1);

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        yield return new Account
                        {
                            OriginalAcctNumber = 
                  dr["TABOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
                            FTBOATAcctNumber = 
                  dr["FTBOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
                            SSN = 
                  CustomerSSN.Substring(CustomerSSN.Length -4).PadLeft(CustomerSSN.Length, '*'),
                            CustomerName = 
                  dr["CUST_NAME"].ToString(),
                            ProductType = 
                  dr["TRGT_PROD"].ToString()
                        };
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(ex, "GetCustomerSSN()");

            }
        }

Upvotes: 1

Luke Baughan
Luke Baughan

Reputation: 4686

You're not adding the acct to the accts list!

                while (dr.Read())
                {
                    Account acct = new Account
                    {
                        OriginalAcctNumber = dr["TABOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
                        FTBOATAcctNumber = dr["FTBOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
                        SSN = CustomerSSN.Substring(CustomerSSN.Length -4).PadLeft(CustomerSSN.Length, '*'),
                        CustomerName = dr["CUST_NAME"].ToString(),
                        ProductType = dr["TRGT_PROD"].ToString()
                    };
                    accts.Add(acct); // ADD THIS HERE
                }

Upvotes: 1

Brandon
Brandon

Reputation: 70032

You're not adding the acct to the list of Accounts.

  while (dr.Read())
  {
    Account acct = new Account
      {
      OriginalAcctNumber = dr["TABOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
      FTBOATAcctNumber = dr["FTBOAT_ACCOUNT_NUMBER"].ToString().TrimStart('0'),
      SSN = CustomerSSN.Substring(CustomerSSN.Length -4).PadLeft(CustomerSSN.Length, '*'),
      CustomerName = dr["CUST_NAME"].ToString(),
      ProductType = dr["TRGT_PROD"].ToString()
    };
    accts.Add(acct); //  You're missing this line.
  }

Upvotes: 3

Related Questions