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

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++
"benji" <benji (AT) discussions (DOT) microsoft.com> wrote

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

"Sheng Jiang[MVP]" wrote:

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++
"Benji" <Benji (AT) discussions (DOT) microsoft.com> wrote

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?

"Sheng Jiang[MVP]" wrote:

You may need the Convert function
see
http://msdn2.microsoft.com/en-us/lib...on(vs.71).aspx
--
Sheng Jiang
Microsoft MVP in VC++
"benji" <benji (AT) discussions (DOT) microsoft.com> wrote