Can't concatenate for data adapters

[jonefer]

Just starting with ASP.NET and Pulling from data from an Access Database so
I'm using the OleDbDataAdapter

I'm trying to create two data adapters...

daActorNames

daActor

I've successfully created daActor

But for daActorNames the following (which works in T-SQL) just doesn't work

SELECT ActorID, LastName + N', ' + FirstName + N' ' + ISNULL(MiddleName,
N'') AS Actor
FROM tblActors
ORDER BY LastName

I'm trying to produce the following

ActorID Actor
123 LastName, FirstName MI (if there is one... nothing if there
isn't)
Why doesnt' this work in or out of the query builder?

[S. Justin Gengo]

Jonefer,

I'm not positive if access understands the "N", but I know you don't need
it. Also I'm unclear whether your ISNULL is allowed or not but I don't
understand why you'd need it. If MiddleName is NULL then it just won't be
included. Give this a try:

SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
Actor
FROM tblActors
ORDER BY LastName

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:FF**********************************@microsof t.com...

Just starting with ASP.NET and Pulling from data from an Access Database
so
I'm using the OleDbDataAdapter

I'm trying to create two data adapters...

daActorNames

daActor

I've successfully created daActor

But for daActorNames the following (which works in T-SQL) just doesn't
work

SELECT ActorID, LastName + N', ' + FirstName + N' ' +
ISNULL(MiddleName,
N'') AS Actor
FROM tblActors
ORDER BY LastName

I'm trying to produce the following

ActorID Actor
123 LastName, FirstName MI (if there is one... nothing if there
isn't)
Why doesnt' this work in or out of the query builder?

[jonefer]

Ok, I was hoping someone else would say that...
I tried what you said, and then this is how it interpreted that:

SELECT ActorID, LastName + ',
' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY LastName

My resultant set looks like this

ActorID Actor
===== =======
123 LastName,
124 LastName,
125 Null
126 Null

No matter what I try, I haven't been able to get the firstname to show...
unless I completely eliminate the comma.

But another KEY thing you said is that you're not sure "ACCESS" understands
'N'
so... should I do what I already know works in Access?

SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")

I tried that, and it doesn't like the & (ampersand)



"S. Justin Gengo" wrote:

Jonefer,

I'm not positive if access understands the "N", but I know you don't need
it. Also I'm unclear whether your ISNULL is allowed or not but I don't
understand why you'd need it. If MiddleName is NULL then it just won't be
included. Give this a try:

SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
Actor
FROM tblActors
ORDER BY LastName

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:FF**********************************@microsof t.com...

Just starting with ASP.NET and Pulling from data from an Access Database
so
I'm using the OleDbDataAdapter

I'm trying to create two data adapters...

daActorNames

daActor

I've successfully created daActor

But for daActorNames the following (which works in T-SQL) just doesn't
work

SELECT ActorID, LastName + N', ' + FirstName + N' ' +
ISNULL(MiddleName,
N'') AS Actor
FROM tblActors
ORDER BY LastName

I'm trying to produce the following

ActorID Actor
123 LastName, FirstName MI (if there is one... nothing if there
isn't)
Why doesnt' this work in or out of the query builder?

[S. Justin Gengo]

Jonefer,

Did you remove the parentheses I had in the select I showed you?

Those are key. I know that concatenation works in Access because I tried it.
And the only difference between the select I sent you and the one you show
here is that yours doesn't have the parentheses...

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.com...

Ok, I was hoping someone else would say that...
I tried what you said, and then this is how it interpreted that:

SELECT ActorID, LastName + ',
' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY
LastName

My resultant set looks like this

ActorID Actor
===== =======
123 LastName,
124 LastName,
125 Null
126 Null

No matter what I try, I haven't been able to get the firstname to show...
unless I completely eliminate the comma.

But another KEY thing you said is that you're not sure "ACCESS"
understands
'N'
so... should I do what I already know works in Access?

SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")

I tried that, and it doesn't like the & (ampersand)



"S. Justin Gengo" wrote:

Jonefer,

I'm not positive if access understands the "N", but I know you don't need
it. Also I'm unclear whether your ISNULL is allowed or not but I don't
understand why you'd need it. If MiddleName is NULL then it just won't be
included. Give this a try:

SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
Actor
FROM tblActors
ORDER BY LastName

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:FF**********************************@microsof t.com...

> Just starting with ASP.NET and Pulling from data from an Access
> Database
> so
> I'm using the OleDbDataAdapter
>
> I'm trying to create two data adapters...
>
> daActorNames
>
> daActor
>
> I've successfully created daActor
>
> But for daActorNames the following (which works in T-SQL) just doesn't
> work
>
> SELECT ActorID, LastName + N', ' + FirstName + N' ' +
> ISNULL(MiddleName,
> N'') AS Actor
> FROM tblActors
> ORDER BY LastName
>
> I'm trying to produce the following
>
> ActorID Actor
> 123 LastName, FirstName MI (if there is one... nothing if
> there
> isn't)
> Why doesnt' this work in or out of the query builder?
>
>
>
>
>

[jonefer]

Justin,
No, I didn't remove the parenthesis. VS.NET removes it.
That's what I meant by:

"and then this is how it interpreted that"

I ended up going to my Access Database and creating a field called
ActorName, updated the table and am now using that field.

....but there must be away to do this in Visual Studio.NET
Try it if you can... you won't be able to keep the parenthesis if you use
the Query Builder... maybe I need to set up the adapter through code.


"S. Justin Gengo" wrote:

Jonefer,

Did you remove the parentheses I had in the select I showed you?

Those are key. I know that concatenation works in Access because I tried it.
And the only difference between the select I sent you and the one you show
here is that yours doesn't have the parentheses...

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.com...

Ok, I was hoping someone else would say that...
I tried what you said, and then this is how it interpreted that:

SELECT ActorID, LastName + ',
' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY
LastName

My resultant set looks like this

ActorID Actor
===== =======
123 LastName,
124 LastName,
125 Null
126 Null

No matter what I try, I haven't been able to get the firstname to show...
unless I completely eliminate the comma.

But another KEY thing you said is that you're not sure "ACCESS"
understands
'N'
so... should I do what I already know works in Access?

SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")

I tried that, and it doesn't like the & (ampersand)



"S. Justin Gengo" wrote:

Jonefer,

I'm not positive if access understands the "N", but I know you don't need
it. Also I'm unclear whether your ISNULL is allowed or not but I don't
understand why you'd need it. If MiddleName is NULL then it just won't be
included. Give this a try:

SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
Actor
FROM tblActors
ORDER BY LastName

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:FF**********************************@microsof t.com...
> Just starting with ASP.NET and Pulling from data from an Access
> Database
> so
> I'm using the OleDbDataAdapter
>
> I'm trying to create two data adapters...
>
> daActorNames
>
> daActor
>
> I've successfully created daActor
>
> But for daActorNames the following (which works in T-SQL) just doesn't
> work
>
> SELECT ActorID, LastName + N', ' + FirstName + N' ' +
> ISNULL(MiddleName,
> N'') AS Actor
> FROM tblActors
> ORDER BY LastName
>
> I'm trying to produce the following
>
> ActorID Actor
> 123 LastName, FirstName MI (if there is one... nothing if
> there
> isn't)
> Why doesnt' this work in or out of the query builder?
>
>
>
>
>

[S. Justin Gengo]

Jonefer,

In that case how about specifying your select as a query in the access
database and then calling that query name from .NET.

That way you'll be calling it the same way you would a stored procedure...

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:01**********************************@microsof t.com...

Justin,
No, I didn't remove the parenthesis. VS.NET removes it.
That's what I meant by:

"and then this is how it interpreted that"

I ended up going to my Access Database and creating a field called
ActorName, updated the table and am now using that field.

...but there must be away to do this in Visual Studio.NET
Try it if you can... you won't be able to keep the parenthesis if you use
the Query Builder... maybe I need to set up the adapter through code.


"S. Justin Gengo" wrote:

Jonefer,

Did you remove the parentheses I had in the select I showed you?

Those are key. I know that concatenation works in Access because I tried
it.
And the only difference between the select I sent you and the one you
show
here is that yours doesn't have the parentheses...

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"jonefer" <jo*****@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.com...

> Ok, I was hoping someone else would say that...
> I tried what you said, and then this is how it interpreted that:
>
> SELECT ActorID, LastName + ',
> ' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY
> LastName
>
> My resultant set looks like this
>
> ActorID Actor
> ===== =======
> 123 LastName,
> 124 LastName,
> 125 Null
> 126 Null
>
> No matter what I try, I haven't been able to get the firstname to
> show...
> unless I completely eliminate the comma.
>
> But another KEY thing you said is that you're not sure "ACCESS"
> understands
> 'N'
> so... should I do what I already know works in Access?
>
> SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")
>
> I tried that, and it doesn't like the & (ampersand)
>
>
>
>
>
>
>
> "S. Justin Gengo" wrote:
>
>> Jonefer,
>>
>> I'm not positive if access understands the "N", but I know you don't
>> need
>> it. Also I'm unclear whether your ISNULL is allowed or not but I don't
>> understand why you'd need it. If MiddleName is NULL then it just won't
>> be
>> included. Give this a try:
>>
>> SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName)
>> AS
>> Actor
>> FROM tblActors
>> ORDER BY LastName
>>
>> --
>> Sincerely,
>>
>> S. Justin Gengo, MCP
>> Web Developer / Programmer
>>
>> www.aboutfortunate.com
>>
>> "Out of chaos comes order."
>> Nietzsche
>> "jonefer" <jo*****@discussions.microsoft.com> wrote in message
>> news:FF**********************************@microsof t.com...
>> > Just starting with ASP.NET and Pulling from data from an Access
>> > Database
>> > so
>> > I'm using the OleDbDataAdapter
>> >
>> > I'm trying to create two data adapters...
>> >
>> > daActorNames
>> >
>> > daActor
>> >
>> > I've successfully created daActor
>> >
>> > But for daActorNames the following (which works in T-SQL) just
>> > doesn't
>> > work
>> >
>> > SELECT ActorID, LastName + N', ' + FirstName + N' ' +
>> > ISNULL(MiddleName,
>> > N'') AS Actor
>> > FROM tblActors
>> > ORDER BY LastName
>> >
>> > I'm trying to produce the following
>> >
>> > ActorID Actor
>> > 123 LastName, FirstName MI (if there is one... nothing if
>> > there
>> > isn't)
>> > Why doesnt' this work in or out of the query builder?
>> >
>> >
>> >
>> >
>> >
>>
>>
>>