none
List<> Cannot work RRS feed

  • Question

  • Hi,

    I want to get all the agreement no from table leadmemberdetails. But I don't know how to return the result. Kindly help me please.

    private List<LeadMemberDetail> getAgreementsByLeadID(string LeadID)
            {

                using (UnitOfWork uow = new UnitOfWork())
                {
                    var manager = Context.GetOwinContext().GetUserManager<ApplicationUserManager>();
                    var signinManager = Context.GetOwinContext().GetUserManager<ApplicationSignInManager>();
                    string UserEmail = Context.User.Identity.GetUserName();
                    //string UserEmail = null;
                    var user = manager.FindByEmail(UserEmail);
                    var @leadid = user.LeadMember_Id.ToString();

                    SqlConnection con = new                       SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader;

                    List<LeadMemberDetail> result = new List<LeadMemberDetail>();

                    String strQuery = "Select AgreementNo From LeadMemberDetails where LeadId = @leadid";
                    cmd.Parameters.Add("@leadid", SqlDbType.NVarChar).Value = @leadid;
                    con.Open();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strQuery;
                    cmd.Connection = con;

                    reader = cmd.ExecuteReader();
                    {
                        while (reader.Read())
                        {
                            result = reader["AgreementNo"].ToString(); //Error is here cannot convert string to list
                        }
                    }
                    con.Close();

                    return result;
                }
            }

    Monday, March 25, 2019 9:40 AM

Answers

  • Okay, the following is a pattern to use where we want country names. 

    public List<string> Countries1()
    {
        var countryNameList = new List<string>();
    
        var selectStatement = "SELECT CountryName FROM dbo.Countries;";
    
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                try
                {
    
                    cn.Open();
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // the next line is fine but the alternate here 
                            // is to see if the value is an empty string.
                            //countryNameList.Add(reader.GetString(0));
                            var countryName = reader.GetString(0);
    
                            // another thing to check is if the value is null
                            // see https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.isdbnull?view=netframework-4.7.2
    
                            if (!string.IsNullOrWhiteSpace(countryName))
                            {
                                countryNameList.Add(countryName);
                            }
                            else
                            {
                                // value is null
                            }
                        }
                    }
                    else
                    {
                        // there are no rows
                    }
    
                }
                catch (Exception e)
                {
                    // decide how to handle
                }
            }
        }
    
        return countryNameList;
    }

    Version 2 is when we are not looking for empty or null strings

    public List<string> Countries1()
    {
        var countryNameList = new List<string>();
    
        var selectStatement = "SELECT CountryName FROM dbo.Countries;";
    
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                try
                {
    
                    cn.Open();
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
    
                            countryNameList.Add(reader.GetString(0));
                        }
                    }
                    else
                    {
                        // there are no rows
                    }
    
                }
                catch (Exception e)
                {
                    // decide how to handle
                }
            }
        }
    
        return countryNameList;
    }

    Last version checks for null values

    public List<string> Countries1()
    {
        var countryNameList = new List<string>();
    
        var selectStatement = "SELECT CountryName FROM dbo.Countries;";
    
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                try
                {
    
                    cn.Open();
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(reader.GetOrdinal("CountryName")))
                                countryNameList.Add(reader.GetString(0));
                        }
                    }
                    else
                    {
                        // there are no rows
                    }
    
                }
                catch (Exception e)
                {
                    // decide how to handle
                }
            }
        }
    
        return countryNameList;
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Monday, March 25, 2019 10:21 AM
    Moderator

All replies

  • Since result is of type LeadMemberDetail then you would need to add a new LeadMemberDetail to result for each iteration of the while loop.

    result.Add(new LeadMemberDetail() ...

    Where in the above for ... you set a property to reader["AgreementNo"].ToString()


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 25, 2019 9:48 AM
    Moderator
  • Hi the count retrieved is 2 which is correct, but there is no data all null :(
    Monday, March 25, 2019 9:58 AM
  • Okay, the following is a pattern to use where we want country names. 

    public List<string> Countries1()
    {
        var countryNameList = new List<string>();
    
        var selectStatement = "SELECT CountryName FROM dbo.Countries;";
    
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                try
                {
    
                    cn.Open();
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // the next line is fine but the alternate here 
                            // is to see if the value is an empty string.
                            //countryNameList.Add(reader.GetString(0));
                            var countryName = reader.GetString(0);
    
                            // another thing to check is if the value is null
                            // see https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.isdbnull?view=netframework-4.7.2
    
                            if (!string.IsNullOrWhiteSpace(countryName))
                            {
                                countryNameList.Add(countryName);
                            }
                            else
                            {
                                // value is null
                            }
                        }
                    }
                    else
                    {
                        // there are no rows
                    }
    
                }
                catch (Exception e)
                {
                    // decide how to handle
                }
            }
        }
    
        return countryNameList;
    }

    Version 2 is when we are not looking for empty or null strings

    public List<string> Countries1()
    {
        var countryNameList = new List<string>();
    
        var selectStatement = "SELECT CountryName FROM dbo.Countries;";
    
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                try
                {
    
                    cn.Open();
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
    
                            countryNameList.Add(reader.GetString(0));
                        }
                    }
                    else
                    {
                        // there are no rows
                    }
    
                }
                catch (Exception e)
                {
                    // decide how to handle
                }
            }
        }
    
        return countryNameList;
    }

    Last version checks for null values

    public List<string> Countries1()
    {
        var countryNameList = new List<string>();
    
        var selectStatement = "SELECT CountryName FROM dbo.Countries;";
    
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                try
                {
    
                    cn.Open();
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(reader.GetOrdinal("CountryName")))
                                countryNameList.Add(reader.GetString(0));
                        }
                    }
                    else
                    {
                        // there are no rows
                    }
    
                }
                catch (Exception e)
                {
                    // decide how to handle
                }
            }
        }
    
        return countryNameList;
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Monday, March 25, 2019 10:21 AM
    Moderator
  • Hi I have used the first way,

    var result = new List<string>();

    reader = cmd.ExecuteReader();
                    {
                        while (reader.Read())
                        {

                            var AgreementNo = reader.GetString(0);
                            result.Add(AgreementNo);
                        }
                    }
                    con.Close();

                    return result; // the error is here ( cannot convert string to List)

    • Marked as answer by valliammai26 Monday, March 25, 2019 10:54 AM
    • Unmarked as answer by valliammai26 Friday, May 17, 2019 6:17 AM
    Monday, March 25, 2019 10:31 AM
  • Try

    private List<string> getAgreementsByLeadID(string LeadID)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 25, 2019 10:40 AM
    Moderator
  • Hi I have used the first way,

    var result = new List<string>();

    reader = cmd.ExecuteReader();
                    {
                        while (reader.Read())
                        {

                            var AgreementNo = reader.GetString(0);
                            result.Add(AgreementNo);
                        }
                    }
                    con.Close();

                    return result; // the error is here ( cannot convert string to List)

    That is exactly what I recommended, so I gave that to you and you marked yourself as answered?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by valliammai26 Friday, May 17, 2019 6:16 AM
    • Unmarked as answer by valliammai26 Friday, May 17, 2019 6:17 AM
    Monday, March 25, 2019 11:01 AM
    Moderator