Typed Dataset: Add expression-based column?

[Guest]

Hi, I'm creating a typed dataset based off of a SQL Server 2005 DB. There are
2 fields from one table that I'd like to bind to on an ASP.NET dropdownlist.
Since I can't specify two fields in the "Datatextfield" proprety of the
control, I'm trying to create an expression-based column. One thing that
compilicates things a bit is that one of the fields is actually a number
which points to a friendly display value in a different table. There's a
foreign-key relationship that the dataset designer (and SQL Server 2005) are
both aware of. So I can modify the query that my method uses to include the
proper text value from the other table. However, when I try to, in the query,
create a single field that combines that text field and the other field (a
datetime field), I get an error at runtime complaining of a problem
converting between a datetime and a character string:

"Conversion failed when converting datetime from character string"

Line 1329: this.Adapter.SelectCommand.Parameters[0].Value =
((int)(OwnerOfShoes));
Line 1330: DataSet1.ShoeInstancesDataTable dataTable = new
DataSet1.ShoeInstancesDataTable();
Line 1331: this.Adapter.Fill(dataTable);
Line 1332: return dataTable;
Line 1333: }

Should I be trying to architect a solution to this in the SQL query? I'd
really like to try to modify the datatable after the SQL query is complete,
but it gets a bit more complicated because one of the values needs to be
looked up in a related table. I'm not sure where I would add the code. Would
I use a partial class to add a new method?

Any thoughts on this? Any suggestions for the best way to do this?

Thanks.

-Ben

 

[Sheng Jiang[MVP]]

You can create a view in the database that join the two tables and use it in
a new \table adapter.

 

[Guest]

Hi Sheng,

Why this over the SQL select query that merges the two fields? Also, do you
have any info on how I would go the other route and add an expression based
column to the datatable?

Thanks...

-Ben

You can create a view in the database that join the two tables and use it in
a new \table adapter.

--
Sheng Jiang
Microsoft MVP in VC++

Hi, I'm creating a typed dataset based off of a SQL Server 2005 DB. There are
2 fields from one table that I'd like to bind to on an ASP.NET dropdownlist.
Since I can't specify two fields in the "Datatextfield" proprety of the
control, I'm trying to create an expression-based column. One thing that
compilicates things a bit is that one of the fields is actually a number
which points to a friendly display value in a different table. There's a
foreign-key relationship that the dataset designer (and SQL Server 2005) are
both aware of. So I can modify the query that my method uses to include the
proper text value from the other table. However, when I try to, in the query,
create a single field that combines that text field and the other field (a
datetime field), I get an error at runtime complaining of a problem
converting between a datetime and a character string:

"Conversion failed when converting datetime from character string"

Line 1329: this.Adapter.SelectCommand.Parameters[0].Value =
((int)(OwnerOfShoes));
Line 1330: DataSet1.ShoeInstancesDataTable dataTable = new
DataSet1.ShoeInstancesDataTable();
Line 1331: this.Adapter.Fill(dataTable);
Line 1332: return dataTable;
Line 1333: }

Should I be trying to architect a solution to this in the SQL query? I'd
really like to try to modify the datatable after the SQL query is complete,
but it gets a bit more complicated because one of the values needs to be
looked up in a related table. I'm not sure where I would add the code. Would
I use a partial class to add a new method?

Any thoughts on this? Any suggestions for the best way to do this?

Thanks.

-Ben

 

[Sheng Jiang[MVP]]

because views get compiled in the database and will run faster than
hardcoded sql.
If you are building a multi-tier application, you can also create a business
object and bind your list column to a calculated property.

Sheng Jiang
Microsoft MVP in VC++


osoft MVP in VC++

Hi Sheng,

Why this over the SQL select query that merges the two fields? Also, do you
have any info on how I would go the other route and add an expression based
column to the datatable?

Thanks...

-Ben

You can create a view in the database that join the two tables and use it in
a new \table adapter.

--
Sheng Jiang
Microsoft MVP in VC++

Hi, I'm creating a typed dataset based off of a SQL Server 2005 DB.

There
are

2 fields from one table that I'd like to bind to on an ASP.NET dropdownlist.
Since I can't specify two fields in the "Datatextfield" proprety of the
control, I'm trying to create an expression-based column. One thing that
compilicates things a bit is that one of the fields is actually a number
which points to a friendly display value in a different table. There's a
foreign-key relationship that the dataset designer (and SQL Server

2005)
are

both aware of. So I can modify the query that my method uses to

include
the

proper text value from the other table. However, when I try to, in the query,
create a single field that combines that text field and the other field (a
datetime field), I get an error at runtime complaining of a problem
converting between a datetime and a character string:

"Conversion failed when converting datetime from character string"

Line 1329: this.Adapter.SelectCommand.Parameters[0].Value =
((int)(OwnerOfShoes));
Line 1330: DataSet1.ShoeInstancesDataTable dataTable = new
DataSet1.ShoeInstancesDataTable();
Line 1331: this.Adapter.Fill(dataTable);
Line 1332: return dataTable;
Line 1333: }

Should I be trying to architect a solution to this in the SQL query? I'd
really like to try to modify the datatable after the SQL query is complete,
but it gets a bit more complicated because one of the values needs to be
looked up in a related table. I'm not sure where I would add the code. Would
I use a partial class to add a new method?

Any thoughts on this? Any suggestions for the best way to do this?

Thanks.

-Ben

 

[Guest]

Do you have a sense regarding my inquiry of adding an expression-based row?

Could you provide more clarity regarding how I would combine the datetime
field and the text field into one without incurring the error I pasted?

because views get compiled in the database and will run faster than
hardcoded sql.
If you are building a multi-tier application, you can also create a business
object and bind your list column to a calculated property.

Sheng Jiang
Microsoft MVP in VC++


osoft MVP in VC++

Hi Sheng,

Why this over the SQL select query that merges the two fields? Also, do you
have any info on how I would go the other route and add an expression based
column to the datatable?

Thanks...

-Ben

You can create a view in the database that join the two tables and use it in
a new \table adapter.

--
Sheng Jiang
Microsoft MVP in VC++
Hi, I'm creating a typed dataset based off of a SQL Server 2005 DB. There
are
2 fields from one table that I'd like to bind to on an ASP.NET
dropdownlist.
Since I can't specify two fields in the "Datatextfield" proprety of the
control, I'm trying to create an expression-based column. One thing that
compilicates things a bit is that one of the fields is actually a number
which points to a friendly display value in a different table. There's a
foreign-key relationship that the dataset designer (and SQL Server 2005)
are
both aware of. So I can modify the query that my method uses to include
the
proper text value from the other table. However, when I try to, in the
query,
create a single field that combines that text field and the other field (a
datetime field), I get an error at runtime complaining of a problem
converting between a datetime and a character string:

"Conversion failed when converting datetime from character string"

Line 1329: this.Adapter.SelectCommand.Parameters[0].Value =
((int)(OwnerOfShoes));
Line 1330: DataSet1.ShoeInstancesDataTable dataTable = new
DataSet1.ShoeInstancesDataTable();
Line 1331: this.Adapter.Fill(dataTable);
Line 1332: return dataTable;
Line 1333: }

Should I be trying to architect a solution to this in the SQL query? I'd
really like to try to modify the datatable after the SQL query is
complete,
but it gets a bit more complicated because one of the values needs to be
looked up in a related table. I'm not sure where I would add the code.
Would
I use a partial class to add a new method?

Any thoughts on this? Any suggestions for the best way to do this?

Thanks.

-Ben

 

[Sheng Jiang[MVP]]

You may need the Convert function
see
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(vs.71).aspx
--
Sheng Jiang
Microsoft MVP in VC++

Do you have a sense regarding my inquiry of adding an expression-based row?

Could you provide more clarity regarding how I would combine the datetime
field and the text field into one without incurring the error I pasted?

because views get compiled in the database and will run faster than
hardcoded sql.
If you are building a multi-tier application, you can also create a business
object and bind your list column to a calculated property.

Sheng Jiang
Microsoft MVP in VC++


osoft MVP in VC++

Hi Sheng,

Why this over the SQL select query that merges the two fields? Also,

do
you

have any info on how I would go the other route and add an expression based
column to the datatable?

Thanks...

-Ben

:

You can create a view in the database that join the two tables and

use
it in

a new \table adapter.

DB.
There

are
2 fields from one table that I'd like to bind to on an ASP.NET
dropdownlist.
Since I can't specify two fields in the "Datatextfield" proprety

of
the

control, I'm trying to create an expression-based column. One

thing
that

compilicates things a bit is that one of the fields is actually a number
which points to a friendly display value in a different table.

There's
a

foreign-key relationship that the dataset designer (and SQL Server 2005)
are
both aware of. So I can modify the query that my method uses to include
the
proper text value from the other table. However, when I try to, in the
query,
create a single field that combines that text field and the other field (a
datetime field), I get an error at runtime complaining of a problem
converting between a datetime and a character string:

"Conversion failed when converting datetime from character string"

Line 1329: this.Adapter.SelectCommand.Parameters[0].Value =
((int)(OwnerOfShoes));
Line 1330: DataSet1.ShoeInstancesDataTable dataTable = new
DataSet1.ShoeInstancesDataTable();
Line 1331: this.Adapter.Fill(dataTable);
Line 1332: return dataTable;
Line 1333: }

Should I be trying to architect a solution to this in the SQL

query?
I'd

really like to try to modify the datatable after the SQL query is
complete,
but it gets a bit more complicated because one of the values needs

to
be

looked up in a related table. I'm not sure where I would add the code.
Would
I use a partial class to add a new method?

Any thoughts on this? Any suggestions for the best way to do this?

Thanks.

-Ben