Loop over DataTable to edit 'bad' columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike D - 09 Jul 2007 16:24 GMT

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();

Signature

Regards,

Mike D
Coding in C# since Feb 2007

Reply to this Message

Eliyahu Goldin - 09 Jul 2007 16:31 GMT

What do you expect in "Create Date" as a bad value?

Signature

Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net

>I have a DataTable (over 32,000 rows) with 19 columns of which I need to
>fix
[quoted text clipped - 10 lines]
> }
> dtTmpTable.AcceptChanges();

Reply to this Message

Mike D - 09 Jul 2007 16:44 GMT

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.

Signature

Regards,

Mike D
Coding in C# since Feb 2007

> What do you expect in "Create Date" as a bad value?
>
[quoted text clipped - 12 lines]
> > }
> > dtTmpTable.AcceptChanges();

Reply to this Message

Peter Bromberg [C# MVP] - 09 Jul 2007 16:52 GMT

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

> 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
[quoted text clipped - 9 lines]
> }
> dtTmpTable.AcceptChanges();

Reply to this Message

Mike D - 09 Jul 2007 17:18 GMT

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 DataRow
results back to the DataTable.

Signature

Regards,

Mike D
Coding in C# since Feb 2007

> You need to find out what the Datatype is of the "CreateDate" column. If it
> is datetime, then you need to cast it to
[quoted text clipped - 21 lines]
> > }
> > dtTmpTable.AcceptChanges();

Reply to this Message

Mike D - 09 Jul 2007 18:32 GMT

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.

Signature

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
[quoted text clipped - 30 lines]
> > > }
> > > dtTmpTable.AcceptChanges();

Reply to this Message

Rate this thread:

Loop over DataTable to edit 'bad' columns

Free Magazines