LoadDataRow to DataTable built by fill schema

13 Feb 2006 4:36 PM

Fiddelm3742

So what I'm trying to do is Create the table tblToLoad which i have done,
then use fill schema to create the proper columns based on strLoadTable
(which is a table name that is passed in via command line ) Then later in my
program insert a datarow into the table. However I receive an error on a
datetime column of the data. I can insert the same row of data by
dynamically creating an insert statement and it will insert without errors
however when I use the LoadDataRow procedure I get the error
An unhandled exception of type 'System.ArgumentException' occurred in
system.data.dll

Additional information: System.FormatException: String was not recognized as
a valid DateTime.
   at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi,
DateTimeStyles styles)
   at System.DateTime.Parse(String s, IFormatProvider provider,
DateTimeStyles styles)
   at System.DateTime.Parse(String s, IFormatProvider provider)
   at System.Convert.ToDateTime(String value, IFormatProvider provider)
   at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
   at System.Convert.ToDateTime(Object value)
   at System.Data.Common.DateTimeStorage.Set(Int32 record, Object value)
   at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't
store <20060209> in contactdate Column. Expected type is DateTime.

If i change the value 20060209 to something like 2006-02-09 12:35:29.998    it
inserts fine, however when i use an insert statement the data 20060209
inserts just fine. Why?

Here's a snippet of my program:
string strTableToLoad = "SELECT * FROM " + strLoadTable ;
SqlCommand cmdTableToLoad = new SqlCommand (strTableToLoad,DWConnection);
DataTable tblToLoad = new DataTable(strLoadTable);

SqlCommand cmdTableToLoad = new SqlCommand (strTableToLoad,DWConnection);
da.SelectCommand = cmdTableToLoad;
            da.FillSchema(dsInfo, SchemaType.Source, strLoadTable);
            da.Fill(dsInfo);
            Console.WriteLine("Columns: " + tblToLoad.Columns.Count);

                            dsInfo.Tables[strLoadTable].LoadDataRow(strTokens,false);

--
www.Fiddelke.org

13 Feb 2006 8:39 PM

Fiddelm3742

Well I just realized that Fill Schema will not really create what it Need. I
only seems to do contraints. Things like allow null, auto incriment, etc. I
just wanted to verify that the table had the same columns(and datatypes) as
the actual database table itself. Apparently doing a "fill" (Select * from
myTable) will create the columns with the contraints. The problem now being
I dont want to do a select * because the table I'm loading is HUGE. I just
need to create a skeleton table with the columns/data types and no real data.
When i do a fill the memory usage that my program uses severly spikes. And
this is not the largest of the tables I'm going to need to use so this will
not be acceptable. I guess I just want to be able to do a LoadDataRow but
thought that the table I was loading didn't have the correct
columns/datatypes so it was failing. Any input as to why this LoadDataRow
fails would be great, when I can insert the same data using an insert
statement.

--
www.Fiddelke.org

Show quote

"Fiddelm3742" wrote:

> So what I'm trying to do is Create the table tblToLoad which i have done,
> then use fill schema to create the proper columns based on strLoadTable
> (which is a table name that is passed in via command line ) Then later in my
> program insert a datarow into the table. However I receive an error on a
> datetime column of the data. I can insert the same row of data by
> dynamically creating an insert statement and it will insert without errors
> however when I use the LoadDataRow procedure I get the error
> An unhandled exception of type 'System.ArgumentException' occurred in
> system.data.dll
>
> Additional information: System.FormatException: String was not recognized as
> a valid DateTime.
>    at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi,
> DateTimeStyles styles)
>    at System.DateTime.Parse(String s, IFormatProvider provider,
> DateTimeStyles styles)
>    at System.DateTime.Parse(String s, IFormatProvider provider)
>    at System.Convert.ToDateTime(String value, IFormatProvider provider)
>    at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
>    at System.Convert.ToDateTime(Object value)
>    at System.Data.Common.DateTimeStorage.Set(Int32 record, Object value)
>    at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't
> store <20060209> in contactdate Column. Expected type is DateTime.
>
> If i change the value 20060209 to something like 2006-02-09 12:35:29.998    it
> inserts fine, however when i use an insert statement the data 20060209
> inserts just fine. Why?
>
> Here's a snippet of my program:
> string strTableToLoad = "SELECT * FROM " + strLoadTable ;
> SqlCommand cmdTableToLoad = new SqlCommand (strTableToLoad,DWConnection);
> DataTable tblToLoad = new DataTable(strLoadTable);
>
> SqlCommand cmdTableToLoad = new SqlCommand (strTableToLoad,DWConnection);
> da.SelectCommand = cmdTableToLoad;
>             da.FillSchema(dsInfo, SchemaType.Source, strLoadTable);
>             da.Fill(dsInfo);
>             Console.WriteLine("Columns: " + tblToLoad.Columns.Count);
>
>                             dsInfo.Tables[strLoadTable].LoadDataRow(strTokens,false);
>
>
>
>
> --
> www.Fiddelke.org