Hello

I have a class with an sql statement to return a list of customers from a postcode search from a database.

Then on my form I am attempting to return a sorted list of these customers into a list box for selection.

Could someone please tell me how to use sorted lists:-/

Thank you ... John.

This is my class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;

namespace TheatreTicketBooking
{
    class Customer
    {
        private string m_CustomerNumber;
        private string m_CustomerName;
        private string m_Address1;
        private string m_Address2;
        private string m_Town;
        private string m_County;
        private string m_PostCode;
        private DateTime m_CreationDate;
        private string m_FriendMarker;



        // Declare an ScheduledEventID property of type string:
        public string CustomerNumber
        {
            get
            {
                return m_CustomerNumber;
            }

            set
            {
                m_CustomerNumber = value;
            }
        }

        // Declare a CustomerName property of type string:
        public string CustomerName
        {
            get
            {
                return m_CustomerName;
            }
            set
            {
                m_CustomerName = value;
            }
        }

        // Declare an Address1 property of type string:
        public string Address1
        {
            get
            {
                return m_Address1;
            }
            set
            {
                m_Address1 = value;
            }
        }

        // Declare an Address2 property of type string:
        public string Address2
        {
            get
            {
                return m_Address2;
            }
            set
            {
                m_Address2 = value;
            }
        }

        // Declare a Town property of type string:
        public string Town
        {
            get
            {
                return m_Town;
            }
            set
            {
                m_Town = value;
            }
        }

        // Declare a County property of type string:
        public string County
        {
            get
            {
                return m_County;
            }
            set
            {
                m_County = value;
            }
        }

        // Declare a PostCode property of type string:
        public string PostCode
        {
            get
            {
                return m_PostCode;
            }
            set
            {
                m_PostCode = value;
            }
        }


        // Declare a CreationEvent property of type DateTime:
        public DateTime CreationEvent
        {
            get
            {
                return m_CreationDate;
            }
            set
            {
                m_CreationDate = value;
            }
        }

        // Declare a FriendMarker property of type string:
        public string FriendMarker
        {
            get
            {
                return m_FriendMarker;
            }
            set
            {
                m_FriendMarker = value;
            }
        }

        //For a given Postcode, retrieve a Customer object
        public void GetCustomersByCustomerNumber(string customerNumber)//being passed this ID  (intCutomer No oncustomers
        {
            string TBConnectionString = ConfigurationManager.ConnectionStrings["TicketBookingConnectionString"].ConnectionString;

            SqlConnection cnTB = new SqlConnection(TBConnectionString);
            cnTB.Open();
            SqlCommand cmCust = new SqlCommand();
            cmCust.Connection = cnTB;
            cmCust.CommandType = CommandType.Text;
            cmCust.CommandText = "Select * from Customers where CustomerNumber " + customerNumber;//star - read it all from customers table Post Code field (variablePCode)
            SqlDataReader drCustomer = cmCust.ExecuteReader();
            drCustomer.Read();// go and read the data

            //the variables
            m_CustomerNumber = drCustomer[0].ToString();
            m_CustomerName = drCustomer[1].ToString();
            m_Address1 = drCustomer[2].ToString();
            m_Address2 = drCustomer[3].ToString();
            m_Town = drCustomer[4].ToString();
            m_County = drCustomer[5].ToString();
            m_PostCode = drCustomer[6].ToString();
            m_CreationDate = (DateTime)drCustomer[7];
            m_FriendMarker = drCustomer[8].ToString();

            drCustomer.Close();
            cnTB.Close();//txtCustomerNumber

        }

        //For a given CustomerNumber, retrieve a Customer object
        public SortedList ListCustomerByPostcode(string PostCode)
        {
            string TBConnectionString = ConfigurationManager.ConnectionStrings["TicketBookingConnectionString"].ConnectionString;

            SqlConnection cnTB = new SqlConnection(TBConnectionString);
            cnTB.Open();
            SqlCommand cmCustList = new SqlCommand();
            cmCustList.Connection = cnTB;
            cmCustList.CommandType = CommandType.Text;
            cmCustList.CommandText = "Select CustomerNumber, CustomerName, Address1, Address2, Town, County, PostCode, CreationDate, FriendMarker from Cutomers where PostCode Like '" + PostCode + "%'";
            
            //cmCustList.CommandText = "Select CustomerName  from Cutomers WHERE PostCode = '" + PostCode + "%'";
            SqlDataReader drCustList = cmCustList.ExecuteReader();
            SortedList CustList = new SortedList();//creating a new sorted list

            while (drCustList.Read())// while it reads the data rows one at a time
            {

                CustList.Add(drCustList[0], drCustList[1] + " , " + drCustList[2] + " , " + drCustList[3] + " , " + drCustList[4] + " , " + drCustList[5] + " , " + drCustList[6] + " , " + drCustList[7].ToString() + " , " + drCustList[8]);// list the fields
               
            }

            drCustList.Close();
            cnTB.Close();
            return CustList;
                  
        }


        
    }
}

And this is my form code with my useless attempt at trying to use a sorted list!

private void btnListCustomerByPostcode_Click(object sender, EventArgs e)
        {
            try
            {
                
                Customer cust = new Customer();
                SortedList custList = cust.ListCustomerByPostcode(ECenterPostcodeTextBox.Text);
                lstCustomerSelect.Items.Add(custList);

            }
            catch
            {
                MessageBox.Show("Please Enter a Postcode");

            }

        }

The code looks fine. Where should be the problem?
Into this code you got the customers with their data:

Customer cust = new Customer();
SortedList custList = cust.ListCustomerByPostcode(ECenterPostcodeTextBox.Text);
//custList hold now customers data!!

//now you want to put them to listBox!
//you will have to loop through the list and add them one by one:
for(int i = 0; i< custList.Count; i++) //instead of count, cound be Lenght property! not sure atm
{
   lstCustomerSelect.Items.Add(custList[i]);
}

But it might work even without any loop, just to use AddRange method

lstCustomerSelect.Items.AddRange(custList[i]); //not sure, try it out

I hope i didnt do a lot of mistakes, I wrote this by heart.

Edited 5 Years Ago by Mitja Bonca: n/a

Comments
Big thankyou

Hi John

For sorting you can use List<T>.Sort() or the sort of an ArrayList. You also can put your data into a DataTable and sort the .DefaultView of it.

Using the List<T> or List<string> you can use the .Add method (in stead of CustList.Add). when you have added all items to the list, you call the method .Sort() and after that you can return the List<> or .ToArray() (to return an array of T or string, which is alphabetically sorted

Regards, Jaap

Comments
Thanks for the help

Hi Mitja ... thank you

I am sure you've not made any mistakes but I am having trouble finding out as every time I run it I get this error saying:
SqlException was unhandled
Invalid object name 'Customers'
It is this line that it has a problem with in the class:

SqlDataReader drCustList = cmCustList.ExecuteReader();

I think that 'Customers' comes form this line of code in the class

cmCustList.CommandText = "Select CustomerNumber, CustomerName, Address1, Address2, Town, County, PostCode, CreationDate, FriendMarker from Cutomers where PostCode Like '" + PostCode + "%'";

The table that I want is called Customers, and the colum that I want form it is PostCode - so I'm confused.

Thanks again John.

Hi Jaap

I am going to stick with the way I am more familiar with at the moment because I am against time. But I am very grateful for your explanation and will try to understand this way when I have more time.

Thank you ... John.

This question has already been answered. Start a new discussion instead.