Using FillSchema and then dt.NewRow(), but I don't get any column default values...

10 Nov 2006 9:02 PM

0to60

Almost all of the tables in my SQL 2005 database have a dteCreated DateTime
field to track when the row was first added. Its a non-nullable field, and
the default value is set to T-SQL's GetDate() function.

Ok, so I'm gonna add a row to a table with ADO.net 2.0. Stop me if anyone
knows of a better way here...

    System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("SELECT *
FROM tablename", connection);
    System.Data.DataTable dt = new DataTable();
    System.Data.SqlClient.SqlCommandBuilder builder = new
SqlCommandBuilder(da);

    da.FillSchema(dt, SchemaType.Mapped);

    System.Data.DataRow row = dt.NewRow();

    row["field1"] = someValue;
    row["field2"] = someOtherValue;

    dt.Rows.Add(row);

    da.Update(dt);

Now, I'd like to not have to specify any non-nullable fields that have a
default value, but I get an exception if I don't right at the
dt.Rows.Add(row) line. Why is this? Is there a way to benefit from those
default values at this point?

Again, if there's a better way to add a row via ADO.net 2.0 (short of
calling a sproc) please lemme know. I just threw this together without much
research.

Oh yeah, I've decided to not use typed DataSets here also. That would
prolly solve my problem, but I don't wanna deal with the pain in the ass of
all that extra code.

11 Nov 2006 7:34 AM

Cor Ligthert [MVP]

Oto60,

Your message is fulfiled from showing your knowledge, why are you asking
things here.

By instance from where do you have that information that a DataSet needs all
that extra code.
For an expert who tells this seems in my idea your problem easy.

Just my thought,

Cor

Show quote

"0to60" <holeshot60_nospam_@yahoo.com> schreef in bericht
news:ONXQMuQBHHA.4680@TK2MSFTNGP04.phx.gbl...
> Almost all of the tables in my SQL 2005 database have a dteCreated
> DateTime field to track when the row was first added. Its a non-nullable
> field, and the default value is set to T-SQL's GetDate() function.
>
> Ok, so I'm gonna add a row to a table with ADO.net 2.0. Stop me if anyone
> knows of a better way here...
>
>    System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("SELECT *
> FROM tablename", connection);
>    System.Data.DataTable dt = new DataTable();
>    System.Data.SqlClient.SqlCommandBuilder builder = new
> SqlCommandBuilder(da);
>
>    da.FillSchema(dt, SchemaType.Mapped);
>
>    System.Data.DataRow row = dt.NewRow();
>
>    row["field1"] = someValue;
>    row["field2"] = someOtherValue;
>
>    dt.Rows.Add(row);
>
>    da.Update(dt);
>
>
> Now, I'd like to not have to specify any non-nullable fields that have a
> default value, but I get an exception if I don't right at the
> dt.Rows.Add(row) line. Why is this? Is there a way to benefit from those
> default values at this point?
>
> Again, if there's a better way to add a row via ADO.net 2.0 (short of
> calling a sproc) please lemme know. I just threw this together without
> much research.
>
> Oh yeah, I've decided to not use typed DataSets here also. That would
> prolly solve my problem, but I don't wanna deal with the pain in the ass
> of all that extra code.
>