ADO.NET and SQL

[Guest]

I have object that connects to a database and fetches data for 10 tables in
the database.

Because the database schema can change I have opted for a collection of SQL
Databadpaters that fill a dataset client side. The procedure cycles through
each table and executes "select * from " then a fill missing schema action
with key ("MissingSchemaAction.AddWithKey") and then finally the SQL Command
builder.

This has been working fine now for a while with other changes however the
schema changed recently with a new column added one of the tables. The
column in question is a bit field and does not allow nulls and has a default
value of 0. Back on the client I am not touching the column but ADO.NET for
some reason tries to enter null instead of zero into this column and
therefore errors.

Am I missing something. I do not want to split the code up and start
referencing each table as the automated cycle though the tables works nicely.

please help?


Thanks for any time spent on this

Sharat Koya

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,


Have you checked if you have any null in the db table?

Are you append the new rows to an existing dataset? if so the previous rows
may have that column as null, if not then the above cause is the most
probable


cheers,

 

[Guest]

There are no null values in the rows. They are all either 0 or 1 as a bit
field. I am appending the new rows to the existing dataset after which I
poupulate changes back to the database. The point at which I recieve the
error is when I try and add the row to the dataset.

Hi,


Have you checked if you have any null in the db table?

Are you append the new rows to an existing dataset? if so the previous rows
may have that column as null, if not then the above cause is the most
probable


cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

I have object that connects to a database and fetches data for 10 tables in
the database.

Because the database schema can change I have opted for a collection of
SQL
Databadpaters that fill a dataset client side. The procedure cycles
through
each table and executes "select * from " then a fill missing schema
action
with key ("MissingSchemaAction.AddWithKey") and then finally the SQL
Command
builder.

This has been working fine now for a while with other changes however the
schema changed recently with a new column added one of the tables. The
column in question is a bit field and does not allow nulls and has a
default
value of 0. Back on the client I am not touching the column but ADO.NET
for
some reason tries to enter null instead of zero into this column and
therefore errors.

Am I missing something. I do not want to split the code up and start
referencing each table as the automated cycle though the tables works
nicely.

please help?


Thanks for any time spent on this

Sharat Koya

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Then check if the previous dataset, or better said, the table in question
has the column defined and all the rows have a value:

if ( dataset1.Tables[0].Columns["BitColumn"] != null )
foreach( DataRow row in dataset1.Tables[0] )
Console.WriteLine( row["BitColumn"].ToString() );


cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

There are no null values in the rows. They are all either 0 or 1 as a bit
field. I am appending the new rows to the existing dataset after which I
poupulate changes back to the database. The point at which I recieve the
error is when I try and add the row to the dataset.

Hi,


Have you checked if you have any null in the db table?

Are you append the new rows to an existing dataset? if so the previous
rows
may have that column as null, if not then the above cause is the most
probable


cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

I have object that connects to a database and fetches data for 10 tables
in
the database.

Because the database schema can change I have opted for a collection of
SQL
Databadpaters that fill a dataset client side. The procedure cycles
through
each table and executes "select * from " then a fill missing schema
action
with key ("MissingSchemaAction.AddWithKey") and then finally the SQL
Command
builder.

This has been working fine now for a while with other changes however
the
schema changed recently with a new column added one of the tables. The
column in question is a bit field and does not allow nulls and has a
default
value of 0. Back on the client I am not touching the column but ADO.NET
for
some reason tries to enter null instead of zero into this column and
therefore errors.

Am I missing something. I do not want to split the code up and start
referencing each table as the automated cycle though the tables works
nicely.

please help?


Thanks for any time spent on this

Sharat Koya

 

[Christof Nordiek]

The problem is not, that the column is not defined. The problem is the
missing default value in the schema.
MissingSchemaAction.AddWithKey doesn't add default values to the schema.

A possible solution would be, only select the columns you now about.
This would work as long as all additional columns are either nullable or
have a default value.

Christof

Hi,

Then check if the previous dataset, or better said, the table in question
has the column defined and all the rows have a value:

if ( dataset1.Tables[0].Columns["BitColumn"] != null )
foreach( DataRow row in dataset1.Tables[0] )
Console.WriteLine( row["BitColumn"].ToString() );


cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

There are no null values in the rows. They are all either 0 or 1 as a bit
field. I am appending the new rows to the existing dataset after which I
poupulate changes back to the database. The point at which I recieve the
error is when I try and add the row to the dataset.

Hi,


Have you checked if you have any null in the db table?

Are you append the new rows to an existing dataset? if so the previous
rows
may have that column as null, if not then the above cause is the most
probable


cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



I have object that connects to a database and fetches data for 10
tables in
the database.

Because the database schema can change I have opted for a collection
of
SQL
Databadpaters that fill a dataset client side. The procedure cycles
through
each table and executes "select * from " then a fill missing schema
action
with key ("MissingSchemaAction.AddWithKey") and then finally the SQL
Command
builder.

This has been working fine now for a while with other changes however
the
schema changed recently with a new column added one of the tables.
The
column in question is a bit field and does not allow nulls and has a
default
value of 0. Back on the client I am not touching the column but
ADO.NET
for
some reason tries to enter null instead of zero into this column and
therefore errors.

Am I missing something. I do not want to split the code up and start
referencing each table as the automated cycle though the tables works
nicely.

please help?


Thanks for any time spent on this

Sharat Koya