none
Multiple access data query's keeps adding data to table. RRS feed

  • Question

  • Hi,

    I have the folowing code to retrieve a index table from a access database into the datatable "Dummy" When Dummy returns the datatable is stored in a other table for use in the program.

    But when I call Query with a new querystring it add's the data to de Dummy datatable instead of returning the records selected by the query.

    What I try to achieve is read the index table which will give me a Tablename from the database with a unique ID number so I can read the single row  using the same code I posted below.

    What is does is, read the index and then add the singe row I need. It does not return the single row :-(

    DataTable Dummy = new DataTable();

    private DataTable Query(string QS2) { using (OleDbConnection conn = new OleDbConnection(connString)) { OleDbCommand cmd = new OleDbCommand(QS2, conn); conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(Dummy); conn.Close(); } return Dummy; }

    If I should use a different methode to read a single row then I would like to know how, and also how I can prevent adding rows to the result when using the other methode.

    Many thanks in advance.

    Rob.

     
    Wednesday, October 17, 2018 8:38 PM

Answers

  • [...] when I call Query with a new querystring it add's the data to de Dummy datatable instead of returning the records selected by the query.

    Indeed, the "Fill" method of the DataAdater by default adds the rows to the datatable that you pass as an argument. One easy way to clear the datatable before filling it with rows from the query is to do a "new" before filling it. You can achieve this by moving your datatable initialization to inside the procedure, instead of keeping it outside:

    private DataTable Query(string QS2) { using (OleDbConnection conn = new OleDbConnection(connString)) { OleDbCommand cmd = new OleDbCommand(QS2, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); DataTable Dummy = new DataTable(); adapter.Fill(Dummy); return Dummy; } }


    Note: I've simplified your code a little bit by removing the open and close, which are not needed when you use "Fill" (it opens and closes the connection internally).
    Wednesday, October 17, 2018 8:49 PM
    Moderator

All replies

  • [...] when I call Query with a new querystring it add's the data to de Dummy datatable instead of returning the records selected by the query.

    Indeed, the "Fill" method of the DataAdater by default adds the rows to the datatable that you pass as an argument. One easy way to clear the datatable before filling it with rows from the query is to do a "new" before filling it. You can achieve this by moving your datatable initialization to inside the procedure, instead of keeping it outside:

    private DataTable Query(string QS2) { using (OleDbConnection conn = new OleDbConnection(connString)) { OleDbCommand cmd = new OleDbCommand(QS2, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); DataTable Dummy = new DataTable(); adapter.Fill(Dummy); return Dummy; } }


    Note: I've simplified your code a little bit by removing the open and close, which are not needed when you use "Fill" (it opens and closes the connection internally).
    Wednesday, October 17, 2018 8:49 PM
    Moderator
  • Alberto,

    I can only say, this was extremely helpfull :-) Thanks for helping and correcting my code :-)

    Rob.

    Wednesday, October 17, 2018 9:30 PM
  • Rob,

    For various reasons (depending on what you're doing with your DataTable, or how you're passing it around), sometimes you don't want to create a new DataTable (it will cause problems with objects, a DataTable in this case, that are passed by reference). In that case, simply clearing the DataTable will take care of that.

    private DataTable Query(string QS2)
    {
        using (OleDbConnection conn = new OleDbConnection(connString))
        {
            OleDbCommand cmd = new OleDbCommand(QS2, conn);
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            Dummy.Clear();
            adapter.Fill(Dummy);
            conn.Close();
        }
        return Dummy;
    }

    You're not passing the DataTable into your method as a parameter, but if you did, it would look like this:

    private void Query(string QS2, DataTable Dummy)
    {
        using (OleDbConnection conn = new OleDbConnection(connString))
        {
            OleDbCommand cmd = new OleDbCommand(QS2, conn);
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            Dummy.Clear();
            adapter.Fill(Dummy);
            conn.Close();
        }
    }
    
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, October 18, 2018 1:13 AM
    Moderator
  • Hi,

    I did reply yesterday but I gues I did not submit it or it has been deleted.

    But, The dummy.clear did remove the data but left the tablenames, so when reading 5 more rows I ended up with 5 empty records and then continued with the data I queried for for the second time. So far the "new" statement gave the most reliable results.

    Anyway, thanks for reading my question and taking the time to answer.

    Regards, Rob.

    Friday, October 19, 2018 9:02 PM
  • Rob,

    Right, it *does* depend on your scenario. If you were always returning data with the same data structure, just different data, then my suggestion would have worked for you.

    But, wait ... you wouldn't have had 5 empty records. You probably meant 5 empty columns (the column names containing the data  resulting from your first query), and then data in additional columns corresponding to the columns resulting from your second query.

    I just wanted to clarify for you so that you understand what was going on (and the correct terminology).

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, October 20, 2018 12:49 AM
    Moderator
  • Sorry for the late response but you are right, it returned 5 empty columns, I stand corrected :-)

    Wednesday, November 7, 2018 10:47 PM