3

I have a datareader set up where it adds the Customer ID each time results are returned.

I have set up a 'next record' button which will run the same query but i want to set it so that the customer record returned does not equal the values stored in my list.

Is there a way to reference the list in a parameter for my MS Access query?

    public void LoopThroughRecs(OleDbDataReader Reader)
    {
        List<int> customer = new List<int>();

        while (Reader.Read())
        {
            int result;

            CustID.Text = Reader["CustID"].ToString();
            FirstName.Text = Reader["Initial"].ToString();
            LastName.Text = Reader["Surname"].ToString();
            Address1.Text = Reader["Address 1"].ToString();
            Address2.Text = Reader["Address 2"].ToString();
            Address3.Text = Reader["Address 3"].ToString();
            TownCity.Text = Reader["Post Town"].ToString();
            PostCode.Text = Reader["Post Code"].ToString();
            Telephone.Text = Reader["Telephone"].ToString();
            LstSvcDat.Text = Reader["LastService"].ToString();
            BoilerMan.Text = Reader["Manufacturer"].ToString();
            BoilerMod.Text = Reader["Model"].ToString();

            result = Convert.ToInt32(CustID.Text);

            customer.Add(int.Parse(CustID.Text));

        }
    }

    public void SearchButton_Click(object sender, EventArgs e)
    {

        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BoilerSvc_be.mdb";
        try
        {
            conn.Open();
            OleDbCommand command = new OleDbCommand("SELECT Contacts.CustID AS CustID,CustCode,Initial,Surname,[Address 1],[Address 2],[Address 3],[Post Town],[Post Code],Telephone,Equipment.CustID AS CustID1,Equipment.Manufacturer AS Manufacturer,Equipment.Model AS Model,Equipment.LastService AS LastService FROM Contacts LEFT OUTER JOIN Equipment ON Equipment.CustID = Contacts.CustID WHERE Archived = 0 AND ([Address 1] = '" + textBox12.Text + "' OR CustCode LIKE '" + textBox12.Text + '%' + "' OR Initial = '" + textBox12.Text + '%' + "' OR Surname = '" + textBox12.Text + '%' + "' OR Initial = '" + textBox12.Text + '%' + "' OR [Post Town] LIKE '" + textBox12.Text + '%' + "' OR [Post Code] = '" + textBox12 + '%' + "')", conn);
            OleDbDataReader Reader = command.ExecuteReader();

            LoopThroughRecs(Reader);

        }
        finally
        {
            conn.Close();
        }
    }

    public void NextRecord_Click_3(object sender, EventArgs e)
    {

        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BoilerSvc_be.mdb";
        try
        {
            conn.Open();
            OleDbCommand command = new OleDbCommand("SELECT Contacts.CustID AS CustID,CustCode,Initial,Surname,[Address 1],[Address 2],[Address 3],[Post Town],[Post Code],Telephone,Equipment.CustID AS CustID1,Equipment.Manufacturer AS Manufacturer,Equipment.Model AS Model,Equipment.LastService AS LastService FROM Contacts LEFT OUTER JOIN Equipment ON Equipment.CustID = Contacts.CustID WHERE Archived = 0 AND Contacts.CustID <> @CustID AND ([Address 1] = '" + textBox12.Text + "' OR CustCode LIKE '" + textBox12.Text + '%' + "' OR Initial = '" + textBox12.Text + '%' + "' OR Surname = '" + textBox12.Text + '%' + "' OR Initial = '" + textBox12.Text + '%' + "' OR [Post Town] LIKE '" + textBox12.Text + '%' + "' OR [Post Code] = '" + textBox12 + '%' + "')", conn);
            command.Parameters.Add(new OleDbParameter("@CustID" == Customer));
            OleDbDataReader Reader = command.ExecuteReader();

            LoopThroughRecs(Reader);

        }
        finally
        {
            conn.Close();
        }
    }
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
Lives
  • 51
  • 5
  • So actually you're trying to implement paging, with a page size of 1? – CodeCaster Aug 07 '16 at 16:17
  • Essentially yes, I just need to set the query up so that each time a record is returned, the customer ID stores in the List and the Next Record button uses a query which includes 'does not equal' the values stored in the List – Lives Aug 07 '16 at 16:25
  • Yeah so sort by some value, [and skip the number of records already read](http://stackoverflow.com/questions/758186/how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-t-sql). – CodeCaster Aug 07 '16 at 16:26
  • If you tabulate the read record keys in a (temporary?) database table as well as in program storage you can use the WHERE NOT IN (SELECT Key FROM Temp ) in your query. – Perry Sugerman Apr 26 '19 at 02:48

0 Answers0