Dataset not Clearing

[tshad]

I am trying to clear my dataset each time I read a new csv file but it seems
to keep the data from the previous time.

If I do:
********************************************************************************
foreach (string strFile in strFiles)
{
ds.Clear();
da = new OleDbDataAdapter("SELECT * FROM " +
Path.GetFileName(strFile),csvConnection);
da.Fill(ds);

foreach (DataTable dt in ds.Tables)
{
for (int i=0;i<dt.Columns.Count-1;i++)
{
switch(i)
{
case 0:
formName = dt.Columns.ColumnName.Replace("Form ","");
dt.Columns.ColumnName = "FieldName";
break;
*********************************************************************************

The second time I read a file (after the ds.Clear() I find that my
dt.Columns.ColumnName will be equal to "FieldName", which is what I
change the column to the first time around!!!

Why is that?

It should be what is in the first row, first column of the table, since I
wiped out the table. If not, how do I get it to work correctly?

Thanks,

Tom

 

[Michael Nemtsev [MVP]]

Hello tshad,

Seems that it's code optimization
Try to trace the dataSet content after clearing it in release mode. Because
I assume that in debug it just don't clear it when u ask and keep it till
u either override it or exit the function

---
WBR,
Michael Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo


t> I am trying to clear my dataset each time I read a new csv file but
t> it seems to keep the data from the previous time.
t>
t> If I do:
t> *********************************************************************
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t> *********************************************************************
t> ************
t> The second time I read a file (after the ds.Clear() I find that my
t> dt.Columns.ColumnName will be equal to "FieldName", which is what
t> I change the column to the first time around!!!
t>
t> Why is that?
t>
t> It should be what is in the first row, first column of the table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t>
t> Thanks,
t>
t> Tom
t>

 

[tshad]

Hello tshad,

Seems that it's code optimization
Try to trace the dataSet content after clearing it in release mode.
Because I assume that in debug it just don't clear it when u ask and keep
it till u either override it or exit the function

That doesn't appear to be the case, because it is getting cleared.

Only the column names are not getting cleared.

If I trace it through, before my da.Fill() I get:
ds.Tables[0].Rows[0][0] 'ds.Tables[0]' threw an exception of type
'System.IndexOutOfRangeException' object {System.IndexOutOfRangeException}

This is the same on both passes (which is expected).

For column names it is the same:
ds.Tables[0].Columns[0].ColumnName 'ds.Tables[0]' threw an exception of
type 'System.IndexOutOfRangeException' string
{System.IndexOutOfRangeException}

But it is only the same on the first pass. On the second pass, the rows
show as out of range, but the column names are the same as I set them on the
first pass.

The problem is that on the first pass, the column names are what is in the
1st column of row 0 of the csv file (which is what I want). But on the 2nd
pass, it stays what I changed it to and never picks up the 1st column of the
2nd file. The problem is that this column tells me what type of file I am
dealing with.

Why doesn't it read the first column here?

My connection string says to use the headers from the 1st row, which it does
on the first file:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");

"HDR=Yes;"

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo

t> I am trying to clear my dataset each time I read a new csv file but
t> it seems to keep the data from the previous time.
t> t> If I do:
t> *********************************************************************
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t> *********************************************************************
t> ************
t> The second time I read a file (after the ds.Clear() I find that my
t> dt.Columns.ColumnName will be equal to "FieldName", which is what
t> I change the column to the first time around!!!
t> t> Why is that?
t> t> It should be what is in the first row, first column of the table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t> t> Thanks,
t> t> Tom
t>

 

[tshad]

That doesn't appear to be the case, because it is getting cleared.

Only the column names are not getting cleared.

I found out how to make it work, I just don't know why I would have to do
this:

If I add ds.tables.clear() after ds.clear(), this will clear the column
names as well.

This doesn't make sense to me as I would have thought that ds.clear (which
gets rid of the tables) would also get rid of the columns names for the
tables which now don't exist.

Thanks,

Tom

If I trace it through, before my da.Fill() I get:
ds.Tables[0].Rows[0][0] 'ds.Tables[0]' threw an exception of type
'System.IndexOutOfRangeException' object {System.IndexOutOfRangeException}

This is the same on both passes (which is expected).

For column names it is the same:
ds.Tables[0].Columns[0].ColumnName 'ds.Tables[0]' threw an exception
of type 'System.IndexOutOfRangeException' string
{System.IndexOutOfRangeException}

But it is only the same on the first pass. On the second pass, the rows
show as out of range, but the column names are the same as I set them on
the first pass.

The problem is that on the first pass, the column names are what is in the
1st column of row 0 of the csv file (which is what I want). But on the
2nd pass, it stays what I changed it to and never picks up the 1st column
of the 2nd file. The problem is that this column tells me what type of
file I am dealing with.

Why doesn't it read the first column here?

My connection string says to use the headers from the 1st row, which it
does on the first file:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");

"HDR=Yes;"

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high and
we miss it, but that it is too low and we reach it" (c) Michelangelo

t> I am trying to clear my dataset each time I read a new csv file but
t> it seems to keep the data from the previous time.
t> t> If I do:
t> *********************************************************************
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t> *********************************************************************
t> ************
t> The second time I read a file (after the ds.Clear() I find that my
t> dt.Columns.ColumnName will be equal to "FieldName", which is what
t> I change the column to the first time around!!!
t> t> Why is that?
t> t> It should be what is in the first row, first column of the table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t> t> Thanks,
t> t> Tom
t>

 

[tshad]

Also, after the ds.Clear(), even though there is no table data (rows[0][0]
will give a System.IndexOutOfRangeException), ds.Tables.Count still shows as
1. So certain things are cleared from the ds as well as certain table
information, but not all of it.

I found that I can do:

ds.Tables.Clear()

And that seems to clear everything. I say "SEEMS TO" because I am not sure
if there are other things that don't get cleared but haven't run into them
yet.

It may be that you need to issue both.

I would like to see what each does clear just to make sure, but I can't seem
to find anything about it.

Thanks,

Tom

I found out how to make it work, I just don't know why I would have to do
this:

If I add ds.tables.clear() after ds.clear(), this will clear the column
names as well.

This doesn't make sense to me as I would have thought that ds.clear (which
gets rid of the tables) would also get rid of the columns names for the
tables which now don't exist.

Thanks,

Tom

If I trace it through, before my da.Fill() I get:
ds.Tables[0].Rows[0][0] 'ds.Tables[0]' threw an exception of type
'System.IndexOutOfRangeException' object
{System.IndexOutOfRangeException}

This is the same on both passes (which is expected).

For column names it is the same:
ds.Tables[0].Columns[0].ColumnName 'ds.Tables[0]' threw an exception
of type 'System.IndexOutOfRangeException' string
{System.IndexOutOfRangeException}

But it is only the same on the first pass. On the second pass, the rows
show as out of range, but the column names are the same as I set them on
the first pass.

The problem is that on the first pass, the column names are what is in
the 1st column of row 0 of the csv file (which is what I want). But on
the 2nd pass, it stays what I changed it to and never picks up the 1st
column of the 2nd file. The problem is that this column tells me what
type of file I am dealing with.

Why doesn't it read the first column here?

My connection string says to use the headers from the 1st row, which it
does on the first file:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");

"HDR=Yes;"

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high and
we miss it, but that it is too low and we reach it" (c) Michelangelo

t> I am trying to clear my dataset each time I read a new csv file but
t> it seems to keep the data from the previous time.
t> t> If I do:
t> *********************************************************************
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t> *********************************************************************
t> ************
t> The second time I read a file (after the ds.Clear() I find that my
t> dt.Columns.ColumnName will be equal to "FieldName", which is what
t> I change the column to the first time around!!!
t> t> Why is that?
t> t> It should be what is in the first row, first column of the table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t> t> Thanks,
t> t> Tom
t>

 

[Michael Nemtsev [MVP]]

Hello tshad,

Actually in should call the .clear for all tables inside the data set.
if u take reflector you found the code, which iterate all table collection
and call clear for tables



---
WBR,
Michael Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo


t> t> t> I found out how to make it work, I just don't know why I would have
t> to do this:
t>
t> If I add ds.tables.clear() after ds.clear(), this will clear the
t> column names as well.
t>
t> This doesn't make sense to me as I would have thought that ds.clear
t> (which gets rid of the tables) would also get rid of the columns
t> names for the tables which now don't exist.
t>
t> Thanks,
t>
t> Tom
t>

If I trace it through, before my da.Fill() I get:
ds.Tables[0].Rows[0][0] 'ds.Tables[0]' threw an exception of
type 'System.IndexOutOfRangeException' object
{System.IndexOutOfRangeException}

This is the same on both passes (which is expected).

For column names it is the same:
ds.Tables[0].Columns[0].ColumnName 'ds.Tables[0]' threw an
exception
of type 'System.IndexOutOfRangeException' string
{System.IndexOutOfRangeException}
But it is only the same on the first pass. On the second pass, the
rows show as out of range, but the column names are the same as I set
them on the first pass.

The problem is that on the first pass, the column names are what is
in the 1st column of row 0 of the csv file (which is what I want).
But on the 2nd pass, it stays what I changed it to and never picks up
the 1st column of the 2nd file. The problem is that this column
tells me what type of file I am dealing with.

Why doesn't it read the first column here?

My connection string says to use the headers from the 1st row, which
it does on the first file:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");

"HDR=Yes;"

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high
and
we miss it, but that it is too low and we reach it" (c) Michelangelo
t> I am trying to clear my dataset each time I read a new csv file
but
t> it seems to keep the data from the previous time.
t> t> If I do:
t>
********************************************************************
*
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t>
********************************************************************
*
t> ************
t> The second time I read a file (after the ds.Clear() I find that
my
t> dt.Columns.ColumnName will be equal to "FieldName", which is
what
t> I change the column to the first time around!!!
t> t> Why is that?
t> t> It should be what is in the first row, first column of the
table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t> t> Thanks,
t> t> Tom
t>

 

[tshad]

Hello tshad,

Actually in should call the .clear for all tables inside the data set.
if u take reflector you found the code, which iterate all table collection
and call clear for tables

Not sure what you mean here.

ds.clear() does clear all the tables, just not the columnname and
ds.tables.count().

ds.Tables.Clear() appears to clear all the tables including the columnNames
and ds.tables.count().

I am just not sure if I should call both. There may be something that
ds.clear() handles that ds.tables.clear() doesn't (just like the problem
when I do the reverse).

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo

t> t>t> I found out how to make it work, I just don't know why I would have
t> to do this:
t> t> If I add ds.tables.clear() after ds.clear(), this will clear the
t> column names as well.
t> t> This doesn't make sense to me as I would have thought that ds.clear
t> (which gets rid of the tables) would also get rid of the columns
t> names for the tables which now don't exist.
t> t> Thanks,
t> t> Tom
t>

If I trace it through, before my da.Fill() I get:
ds.Tables[0].Rows[0][0] 'ds.Tables[0]' threw an exception of
type 'System.IndexOutOfRangeException' object
{System.IndexOutOfRangeException}

This is the same on both passes (which is expected).

For column names it is the same:
ds.Tables[0].Columns[0].ColumnName 'ds.Tables[0]' threw an
exception
of type 'System.IndexOutOfRangeException' string
{System.IndexOutOfRangeException}
But it is only the same on the first pass. On the second pass, the
rows show as out of range, but the column names are the same as I set
them on the first pass.

The problem is that on the first pass, the column names are what is
in the 1st column of row 0 of the csv file (which is what I want).
But on the 2nd pass, it stays what I changed it to and never picks up
the 1st column of the 2nd file. The problem is that this column
tells me what type of file I am dealing with.

Why doesn't it read the first column here?

My connection string says to use the headers from the 1st row, which
it does on the first file:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");

"HDR=Yes;"

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high
and
we miss it, but that it is too low and we reach it" (c) Michelangelo
t> I am trying to clear my dataset each time I read a new csv file
but
t> it seems to keep the data from the previous time.
t> t> If I do:
t>
********************************************************************
*
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t>
********************************************************************
*
t> ************
t> The second time I read a file (after the ds.Clear() I find that
my
t> dt.Columns.ColumnName will be equal to "FieldName", which is
what
t> I change the column to the first time around!!!
t> t> Why is that?
t> t> It should be what is in the first row, first column of the
table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t> t> Thanks,
t> t> Tom
t>

 

[tshad]

Hello tshad,

Actually in should call the .clear for all tables inside the data set.
if u take reflector you found the code, which iterate all table collection
and call clear for tables

Not sure what you mean by this?

Are you saying that you need to do a foreach loop and clear each table?

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo

t> t>t> I found out how to make it work, I just don't know why I would have
t> to do this:
t> t> If I add ds.tables.clear() after ds.clear(), this will clear the
t> column names as well.
t> t> This doesn't make sense to me as I would have thought that ds.clear
t> (which gets rid of the tables) would also get rid of the columns
t> names for the tables which now don't exist.
t> t> Thanks,
t> t> Tom
t>

If I trace it through, before my da.Fill() I get:
ds.Tables[0].Rows[0][0] 'ds.Tables[0]' threw an exception of
type 'System.IndexOutOfRangeException' object
{System.IndexOutOfRangeException}

This is the same on both passes (which is expected).

For column names it is the same:
ds.Tables[0].Columns[0].ColumnName 'ds.Tables[0]' threw an
exception
of type 'System.IndexOutOfRangeException' string
{System.IndexOutOfRangeException}
But it is only the same on the first pass. On the second pass, the
rows show as out of range, but the column names are the same as I set
them on the first pass.

The problem is that on the first pass, the column names are what is
in the 1st column of row 0 of the csv file (which is what I want).
But on the 2nd pass, it stays what I changed it to and never picks up
the 1st column of the 2nd file. The problem is that this column
tells me what type of file I am dealing with.

Why doesn't it read the first column here?

My connection string says to use the headers from the 1st row, which
it does on the first file:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
csvPath + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"");

"HDR=Yes;"

Thanks,

Tom

---
WBR, Michael Nemtsev [.NET/C# MVP] :: blog:
http://spaces.live.com/laflour
"The greatest danger for most of us is not that our aim is too high
and
we miss it, but that it is too low and we reach it" (c) Michelangelo
t> I am trying to clear my dataset each time I read a new csv file
but
t> it seems to keep the data from the previous time.
t> t> If I do:
t>
********************************************************************
*
t> ***********
t> foreach (string strFile in strFiles)
t> {
t> ds.Clear();
t> da = new OleDbDataAdapter("SELECT * FROM " +
t> Path.GetFileName(strFile),csvConnection);
t> da.Fill(ds);
t> foreach (DataTable dt in ds.Tables)
t> {
t> for (int i=0;i<dt.Columns.Count-1;i++)
t> {
t> switch(i)
t> {
t> case 0:
t> formName = dt.Columns.ColumnName.Replace("Form ","");
t> dt.Columns.ColumnName = "FieldName";
t> break;
t>
********************************************************************
*
t> ************
t> The second time I read a file (after the ds.Clear() I find that
my
t> dt.Columns.ColumnName will be equal to "FieldName", which is
what
t> I change the column to the first time around!!!
t> t> Why is that?
t> t> It should be what is in the first row, first column of the
table,
t> since I wiped out the table. If not, how do I get it to work
t> correctly?
t> t> Thanks,
t> t> Tom
t>