Loop over DataTable to edit 'bad' columns

I have a DataTable (over 32,000 rows) with 19 columns of which I need to fix
some data in two of them in order to import the data into a SQL table. I
have a for loop but I can't figure out the if statement to find the 'bad'
columns so I edit them. This is what I have but it isn't working.
for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
if (dtTmpTable.Rows[i]["Create Date"] == "00/00/0000") <-- not working
{
dtTmpTable.Rows[i]["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();
Regards,
Mike D
Coding in C# since Feb 2007What do you expect in "Create Date" as a bad value?
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net
"Mike D" <MikeD@.discussions.microsoft.com> wrote in message
news:A7543F02-D8D7-4A9E-A9B5-CB44AF9E2C1E@.microsoft.com...
>I have a DataTable (over 32,000 rows) with 19 columns of which I need to
>fix
> some data in two of them in order to import the data into a SQL table. I
> have a for loop but I can't figure out the if statement to find the 'bad'
> columns so I edit them. This is what I have but it isn't working.
> for (int i = 0; i < dtTmpTable.Rows.Count; i++)
> {
> if (dtTmpTable.Rows[i]["Create Date"] == "00/00/0000") <-- not working
> {
> dtTmpTable.Rows[i]["Create Date"] = DBNull.Value;
> }
> }
> dtTmpTable.AcceptChanges();
> --
> Regards,
> Mike D
> Coding in C# since Feb 2007
The data comes from a SAP output file (which I have no control) and I
imported into a DataTable to make the changes then insert the data into the
SQL table. The two columns should have valid dates but I have '00/00/0000'
in the two columns interspersed with the valid dates. I tried the Select()
method but I can't get that to update the right rows.
Regards,
Mike D
Coding in C# since Feb 2007
"Eliyahu Goldin" wrote:

> What do you expect in "Create Date" as a bad value?
> --
> Eliyahu Goldin,
> Software Developer
> Microsoft MVP [ASP.NET]
> http://msmvps.com/blogs/egoldin
> http://usableasp.net
>
> "Mike D" <MikeD@.discussions.microsoft.com> wrote in message
> news:A7543F02-D8D7-4A9E-A9B5-CB44AF9E2C1E@.microsoft.com...
>
>
You need to find out what the Datatype is of the "CreateDate" column. If it
is datetime, then you need to cast it to
(Datetime) dtTmpTable.Rows[i]["Create Date"]
otherwise, you are comparing a string to an object, which will fail.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com
"Mike D" wrote:

> I have a DataTable (over 32,000 rows) with 19 columns of which I need to f
ix
> some data in two of them in order to import the data into a SQL table. I
> have a for loop but I can't figure out the if statement to find the 'bad'
> columns so I edit them. This is what I have but it isn't working.
> for (int i = 0; i < dtTmpTable.Rows.Count; i++)
> {
> if (dtTmpTable.Rows[i]["Create Date"] == "00/00/0000") <-- not working
> {
> dtTmpTable.Rows[i]["Create Date"] = DBNull.Value;
> }
> }
> dtTmpTable.AcceptChanges();
> --
> Regards,
> Mike D
> Coding in C# since Feb 2007
I believe all the columns are of datatype string as I did a Select From
Openrowset from a csv file. The code I am using completes without error but
it doesn't find any rows with the string "00/00/0000" either. If I do a
Select() from a DataRow then I get around 12,000 rows for one column and
around 6,000 rows for the other column. But when I try to update the
Datatable I update the first 12,000/6,000 rows of the DataTable and not the
rows that need to be updated. I don't know how to convert the edited DataRo
w
results back to the DataTable.
--
Regards,
Mike D
Coding in C# since Feb 2007
"Peter Bromberg [C# MVP]" wrote:
> You need to find out what the Datatype is of the "CreateDate" column. If i
t
> is datetime, then you need to cast it to
> (Datetime) dtTmpTable.Rows[i]["Create Date"]
> otherwise, you are comparing a string to an object, which will fail.
> -- Peter
> Site: http://www.eggheadcafe.com
> UnBlog: http://petesbloggerama.blogspot.com
> BlogMetaFinder(BETA): http://www.blogmetafinder.com
>
> "Mike D" wrote:
>
I figured out how to do it.
DateTime dtTemp;
for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
try
{
dtTemp = DateTime.Parse(dtTmpTable.Rows[i]["Create
Date"].ToString());
if (dtTemp < DateTime.Parse("1990-01-01") || dtTemp >
DateTime.Parse("2099-12-31"))
{
dtTmpTable.Rows[i]["Create Date"] = DBNull.Value;
}
}
catch
{
dtTmpTable.Rows[i]["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();
Now I have to do is figure out how to take the DataTable and insert it into
the SQL table.
--
Regards,
Mike D
Coding in C# since Feb 2007
"Mike D" wrote:
> I believe all the columns are of datatype string as I did a Select From
> Openrowset from a csv file. The code I am using completes without error b
ut
> it doesn't find any rows with the string "00/00/0000" either. If I do a
> Select() from a DataRow then I get around 12,000 rows for one column and
> around 6,000 rows for the other column. But when I try to update the
> Datatable I update the first 12,000/6,000 rows of the DataTable and not th
e
> rows that need to be updated. I don't know how to convert the edited Data
Row
> results back to the DataTable.
> --
> Regards,
> Mike D
> Coding in C# since Feb 2007
> "Peter Bromberg [C# MVP]" wrote:
>