Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Can't concatenate for data adapters

Reply
Thread Tools

Can't concatenate for data adapters

 
 
=?Utf-8?B?am9uZWZlcg==?=
Guest
Posts: n/a
 
      10-11-2005
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?





 
Reply With Quote
 
 
 
 
S. Justin Gengo
Guest
Posts: n/a
 
      10-11-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
>
>
>
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?am9uZWZlcg==?=
Guest
Posts: n/a
 
      10-11-2005
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >
> >
> >
> >
> >

>
>
>

 
Reply With Quote
 
S. Justin Gengo
Guest
Posts: n/a
 
      10-14-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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?
>> >
>> >
>> >
>> >
>> >

>>
>>
>>



 
Reply With Quote
 
=?Utf-8?B?am9uZWZlcg==?=
Guest
Posts: n/a
 
      10-14-2005
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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?
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
S. Justin Gengo
Guest
Posts: n/a
 
      10-16-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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?
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate/De-Concatenate Carlos VHDL 10 10-24-2012 01:04 PM
Supply Telephone Voice Modular Adapters,Telephone Modular Couplers,Modular Duplex Jack,Triplex Adapters,Telephone extension Cord samul888@vip.163.com Cisco 1 11-13-2005 09:23 AM
Supply Telephone Voice Modular Adapters,Telephone Modular Couplers,Modular Duplex Jack,Triplex Adapters,Telephone extension Cord samul888@vip.163.com Computer Support 0 11-12-2005 06:22 AM
Supply Telephone Voice Modular Adapters,Telephone Modular Couplers,Modular Duplex Jack,Triplex Adapters,Telephone extension Cord samul888@vip.163.com VOIP 0 11-12-2005 06:22 AM
Supply Telephone Accessories,Modular Adapters Couplers,Duplex Jack,Triplex Adapters,ADSL Adapters,Plug With RJ12 Jacks samul888@vip.163.com Cisco 0 11-10-2005 07:47 AM



Advertisments