Re: Typed Dataset: Add expression-based column?

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4ABDF822-9CF6-4886-8BE5-6C3C7FDEEB20@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: conversion question
    ... Above will return the rows that cannot be converted to datetime. ... or is out-of-range for the datetime datatype. ... > converting datetime from character string." ...
    (microsoft.public.sqlserver.programming)
  • Re: HELP with DateAdd statement and SQL
    ... saying SQL that it is having a problem converting the charater set. ... > do datetime comparisons with character data without converting the character ... > the performance of any query that you run agains the table, ... > an example of one of the UTC entries and I'll show you how to do it. ...
    (microsoft.public.scripting.vbscript)
  • Re: SQL Between
    ... The expressions in the script below will evaluate to the Monday and ... declare @m datetime ... >Basically I want my SQL query to be able to count records that have come in ... >I know this code wont work, but hopefully give you guys an idea of what I ...
    (microsoft.public.sqlserver.programming)
  • Re: converting from varchar to datetime
    ... I Have just ran into the same problem converting a nvarchar to ... Example of incorrect information in your datebase: ... Arithmetic overflow error converting expression to data type datetime. ...
    (microsoft.public.sqlserver.programming)
  • Re: date variable in a DTS package
    ... The expressions you're converting to ... datetime are integers, and format 112 is for converting strings in the ... format code for datetime strings is only documented for conversions between ...
    (microsoft.public.sqlserver.programming)
Quantcast